SELECT一列,返回多列,其他列相同,mysql查询 [英] SELECT one column, with multiple columns returned where other columns are same, mysql query
本文介绍了SELECT一列,返回多列,其他列相同,mysql查询的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
选择一列(val3),返回多列,其他列(id,val1)相同,mysql查询
数据看起来像那样
id val1 val2 val3
R m 1 a
N c 1 c
N c 2 d
Y b 1 e
Y h 1 u
Y h 2 i
结果应该是是
id val1 val3s
R ma
N cc,d
Y
Y hu,i
我的尝试:
有完成,
SELECT id,val1,
CASE
WHEN COUNT(val1)= 1 THEN CONCAT('(',val3,') ')
WHEN COUNT(val1)> 1那么CONCAT('(','??',')')
END AS val3s
FROM table1
GROUP BY val1
解决方案
请试试这个。希望这会有所帮助,这里你的table1是TestConC
选择a.id,a.val1,CustomData
= STUFF(( SELECT','+ b.val3
FROM TestConC b
其中b.id = a.id和b.val1 = a.val1
FOR XML PATH('')),1, 1,'')
从TestConC a a
组a.id,a.val1
命令由a.id
< blockquote>听起来你正在寻找 GROUP_CONCAT
功能 [ ^ ]:
SELECT
id,
val1,
GROUP_CONCAT(val3) As val3s
FROM
table1
GROUP BY
id,
val1
;
SELECT one column (val3), with multiple columns returned where other columns (id, val1) are same, mysql query
data looks something like that
id val1 val2 val3
R m 1 a
N c 1 c
N c 2 d
Y b 1 e
Y h 1 u
Y h 2 i
the result should be
id val1 val3s
R m a
N c c,d
Y b e
Y h u,i
What I have tried:
Have done,
SELECT id, val1,
CASE
WHEN COUNT(val1) = 1 THEN CONCAT('(', val3, ')')
WHEN COUNT(val1) > 1 THEN CONCAT('(', '??' , ')')
END AS val3s
FROM table1
GROUP BY val1
解决方案
Please try this. Hope this will help, here your table1 is TestConC
select a.id, a.val1, CustomData = STUFF( (SELECT ',' + b.val3 FROM TestConC b where b.id=a.id and b.val1=a.val1 FOR XML PATH('')),1, 1, '') From TestConC a group by a.id, a.val1 order by a.id
Sounds like you're looking for theGROUP_CONCAT
function[^]:
SELECT id, val1, GROUP_CONCAT(val3) As val3s FROM table1 GROUP BY id, val1 ;
这篇关于SELECT一列,返回多列,其他列相同,mysql查询的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文