表a结构如下:
aID,aName
1, a
2, b
3, c
4, d表b结构如下:
bID,aID,bName
1, 1, aaaa
2, 1, bbbb
3, 2, cccc
4, 2, dddd
5, 1, eeee
6, 3, ffff表b中的aID关联表a中的aID
现想求如下结果:
select aid, aName, aID在表中的数量 as total from a
不知道,sql语句如何写,先谢谢大家了。
aID,aName
1, a
2, b
3, c
4, d表b结构如下:
bID,aID,bName
1, 1, aaaa
2, 1, bbbb
3, 2, cccc
4, 2, dddd
5, 1, eeee
6, 3, ffff表b中的aID关联表a中的aID
现想求如下结果:
select aid, aName, aID在表中的数量 as total from a
不知道,sql语句如何写,先谢谢大家了。
SELECT a.aid,a.aNAME,COUNT(1) AS total
FROM a a LEFT JOIN b b ON a.aid=b.aid
GROUP BY a.aid,a.NAME
我不清楚你的aid是要取a表还是b表的
(
aid INT,
aname VARCHAR(50)
)
DECLARE @b TABLE
(
bid INT,
aid INT,
bname VARCHAR(50)
) INSERT @a
SELECT 1,
'a'
UNION ALL
SELECT 2,
'b'
UNION ALL
SELECT 3,
'c'
UNION ALL
SELECT 4,
'd' INSERT @b
SELECT 1,
1,
'aaaa'
UNION ALL
SELECT 2,
1,
'bbbb'
UNION ALL
SELECT 3,
2,
'cccc'
UNION ALL
SELECT 4,
2,
'dddd'
UNION ALL
SELECT 5,
1,
'eeee'
UNION ALL
SELECT 6,
3,
'ffff' SELECT a.aid,
a.aname,
Count(b.aid) AS total
FROM @a a
LEFT JOIN @b b
ON a.aid = b.aid
GROUP BY b.aid,
a.aid,
a.aname SELECT aid,
aname,
(SELECT Count(*)
FROM @b b
WHERE aid = a.aid) AS total
FROM @a a
1楼的这个经典我的繁琐了
select aid, aName, (select count(*) from b where aID=a.aID) as total from aselect a.aid,a.aname,count(b.aid) as total from a a
left join b b on a.aid=b.aid
group by a.aid,a.aname /*
aid aname total
----------- ---------- -----------
1 a 3
2 b 2
3 c 1
4 d 0
*/