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
所有回答(2)
0
我还在想需要一个函数，你那个函数，只在mysql？
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;
