首页 新闻 搜索 专区 学院

sqlserver join

0
[待解决问题]

有两张表a和b,通过a_id关联,我想通过join获得下面记录,以便统计。
请问在sql server环境该怎么写sql比较好?

table a:
id name address
1 wo beijing
2 he shanghai
3 she beijing

table b:
id a_id lift
1 1 L1
2 1 L2
3 2 L1
4 2 L2
5 2 L3

final join:
id name num lift address
1 wo 2 L1,L2 beijing
2 he 3 L1,L2,L3 shanghai
3 she 0 beijing

问题补充:

SELECT e.WLGuid, e.num
, lifts = STUFF((
SELECT ',' + liftNo
FROM (
SELECT c., d.LiftNo
FROM (
SELECT a.WLGuid, COUNT(b.WLGuid) AS num
FROM Worklist a
LEFT JOIN ProjectLift b
ON a.WLGuid = b.WLGuid
AND b.Status = '0'
GROUP BY a.WLGuid
) c
LEFT JOIN ProjectLift d
ON c.WLGuid = d.WLGuid
AND d.Status = '0'
) f
WHERE f.WLGuid = e.wlguid
FOR XML path('')
), 1, 1, '')
FROM (
SELECT c.
, d.LiftNo
FROM (
SELECT a.WLGuid, COUNT(b.WLGuid) AS num
FROM Worklist a
LEFT JOIN ProjectLift b
ON a.WLGuid = b.WLGuid
AND b.Status = '0'
GROUP BY a.WLGuid
) c
LEFT JOIN ProjectLift d
ON c.WLGuid = d.WLGuid
AND d.Status = '0'
) e
GROUP BY e.WLGuid, e.num

站在那看风景的主页 站在那看风景 | 菜鸟二级 | 园豆:371
提问于:2020-07-23 16:55
< >
分享
所有回答(2)
0

不知道风往哪儿吹 | 园豆:1528 (小虾三级) | 2020-07-24 10:45

我还在想需要一个函数,你那个函数,只在mysql?

支持(0) 反对(0) 顾晓北 | 园豆:10283 (专家六级) | 2020-07-24 14:41
0

SELECT a.id,
a.name,
ISNULL(bb.num, 0) AS num,
bb.lift,
a.address
FROM #a a
LEFT JOIN
(
SELECT COUNT(1) AS num,
a_id,
lift = STUFF(
(
SELECT ',' + b1.lift FROM #b b1 WHERE b1.a_id = b.a_id FOR XML PATH('')
),
1,
1,
''
)
FROM #b b
GROUP BY a_id
) bb
ON a.id = bb.a_id;

Script丶 | 园豆:170 (初学一级) | 2020-07-28 15:04
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册