我有一个MySQL表,如:
ID, USER, PARAM, VAL -------------------- 1 | 1 | NAME | ADAM 2 | 1 | AGE | 15 3 | 2 | NAME | EVA 4 | 2 | AGE | 16 5 | 3 | NAME | CRISS 6 | 3 | AGE | 14
而且我很好奇是否有一个查询会给我类似的东西:
1 | ADAM | 15 2 | EVE | 16 3 | CRISS| 14
到目前为止,我只是使用下面的查询并在循环中对记录进行分组.
SELECT * FROM table WHERE PARAM ='NAME' OR PARAM = 'AGE'
我尝试使用GROUP但没有成功.
在同一个表上使用join:
SELECT a.USER user_id, a.VAL user_name, b.VAL user_age
FROM `table` a
INNER JOIN `table` b ON a.USER = b.USER
WHERE a.PARAM = 'NAME'
AND b.PARAM = 'AGE'
结果:
user_id user_name user_age 1 ADAM 15 2 EVA 16 3 CRISS 14
没有必要使用JOIN.试试这个:
SELECT USER,
MAX(CASE PARAM WHEN 'NAME' THEN VAL ELSE NULL END) AS NAME,
MAX(CASE PARAM WHEN 'AGE' THEN VAL ELSE 0 END) AS AGE
FROM test
GROUP BY USER;
您可以使用最常用的技术生成数据透视视图
select user, max(case when param = 'NAME' then val end) as name, max(case when param = 'AGE' then val end) as age from mytable group by user
你可以这样做一个查询:
SELECT t1.user,
(
SELECT val FROM tab1 t2
WHERE t2.user = t1.user
AND t2.param = 'Name'
) name,
(
SELECT val FROM tab1 t2
WHERE t2.user = t1.user
AND t2.param = 'Age'
) age
FROM tab1 t1
GROUP BY user
另一种可能性是group_concat,但是你可以在一列中监听所有值.
SELECT user, group_concat(param, ':', val)
FROM tab1
GROUP BY user
京公网安备 11010802040832号 | 京ICP备19059560号-6 