Question

我有两个数据库表:一个有用户识别器,另一个有用户元数据(与同一用户识别仪有多个链接)。

<代码>用户表结构

id	USERNAME
1	user 1
2	user 2
3	user 3

<代码>用户_meta 数据结构

meta_id	user_id	meta_key	meta_val
1	1	key1	a
2	1	key2	b
3	2	key1	d
4	3	key1	e
5	3	key2	f

我要问,用户是否拥有元数据数据库。

 SELECT * FROM users 
 LEFT OUTER JOIN users_meta ON users.id = users_meta.user_id
 WHERE users_meta.meta_key != "key2"
 GROUP BY id 
 ORDER BY id ASC 
 LIMIT 0, 100

如何获得用户<代码>2与<代码>key2有关的元值?

Answer 1

引证。我使用次问询、工会和问询。

SELECT * FROM
(
    SELECT
        (SELECT users_meta.id FROM users_meta WHERE users_meta.user_id = users.id AND users_meta.meta_key =  key1 ) AS meta_id,
        users.id AS user_id,
        users.username,
         key1  AS meta_key,
        (SELECT users_meta.meta_val FROM users_meta WHERE users_meta.user_id = users.id AND users_meta.meta_key =  key1 ) AS meta_val
    FROM
        users
    UNION ALL
    SELECT
        (SELECT users_meta.id FROM users_meta WHERE users_meta.user_id = users.id AND users_meta.meta_key =  key2 ) AS meta_id,
        users.id AS user_id,
        users.username,
         key2  AS meta_key,
        (SELECT users_meta.meta_val FROM users_meta WHERE users_meta.user_id = users.id AND users_meta.meta_key =  key2 ) AS meta_val
    FROM
        users
) AS SRC
WHERE
    meta_val IS NULL
ORDER BY
    user_id

友情链接