SQL语句关于两表连接查询使用两次COUNT（）函数的问题

[GAMER丶]

anum表示各个人参加的项目数，bnum表示各个人在各自项目中胜利的次数
这个查询语句该怎么写，萌新求罩

[RINK_1]

select A.bid,anum,isnull(bnum,0) as bnum
from 
(select bid,COUNT(distinct aid) as anum from 表A group by bid) as A
left join
(select bid,SUM(case when cid='胜' then 1 else 0 end) as bnum from 表B group by bid) as B
on A.bid=B.bid

[吉普赛的歌]

USE tempdb
GO
IF OBJECT_ID('A') IS NOT NULL DROP TABLE A
IF OBJECT_ID('B') IS NOT NULL DROP TABLE B
GO
CREATE TABLE A(
	aid NVARCHAR(10),
	bid NVARCHAR(10)	
)
GO
CREATE TABLE B(
	aid NVARCHAR(10),
	bid NVARCHAR(10),
	cid NVARCHAR(10)	
)
GO
INSERT INTO A VALUES ('赛跑','王大')
INSERT INTO A VALUES ('跳远','王大')
INSERT INTO A VALUES ('游泳','张三')
INSERT INTO A VALUES ('实心球','李四')

INSERT INTO B VALUES ('a','王大','胜')
INSERT INTO B VALUES ('b','王大','胜')
INSERT INTO B VALUES ('d','李四','胜')

SELECT bid
	,COUNT(1) AS anum 
	,ISNULL((SELECT SUM(CASE WHEN cid='胜' THEN 1 ELSE 0 END) FROM B WHERE a.bid=b.bid ),0) AS bnum
FROM A GROUP BY bid
/*
bid        anum        bnum
---------- ----------- -----------
李四         1           1
王大         2           2
张三         1           0
*/

[二月十六]

--测试数据
if not object_id(N'Tempdb..#A') is null
	drop table #A
Go
Create table #A([aid] nvarchar(23),[bid] nvarchar(22))
Insert #A
select N'赛跑',N'王大' union all
select N'跳远',N'王大' union all
select N'游泳',N'张三' union all
select N'实心球',N'李四'
GO
if not object_id(N'Tempdb..#B') is null
	drop table #B
Go
Create table #B([aid] nvarchar(21),[bid] nvarchar(22),[cid] nvarchar(22))
Insert #B
select N'a',N'王大',N'胜' union all
select N'b',N'王大',N'胜' union all
select N'd',N'李四',N'胜'
Go
--测试数据结束
SELECT
    ISNULL(t1.bid, t2.bid) AS bid,
    ISNULL(t1.anum, 0)     anum,
    ISNULL(t2.bnum, 0)     bnum
FROM
    (
        SELECT
            bid,
            COUNT(1) AS anum
        FROM
            #A
        GROUP BY
            bid
    )     t1
    FULL JOIN
        (
            SELECT
                bid,
                SUM(   CASE
                           WHEN cid = '胜'
                               THEN 1
                           ELSE
                               0
                       END
                   ) bnum
            FROM
                #B
            GROUP BY
                bid
        ) t2
            ON t2.bid = t1.bid;

[GAMER丶]

如果B表中的cid的值是不确定的该怎么办，比如可能是“胜利、第一名、赢·······”

[二月十六]

引用 4 楼 qq_36011222 的回复:

如果B表中的cid的值是不确定的该怎么办，比如可能是“胜利、第一名、赢·······”

那要统计那些？比如说如果是“挺好”、“不错”、“有点意思”、“棒极了”、“还行吧”、“凑活”怎么统计，统计的规则是什么？

[GAMER丶]

引用 5 楼 sinat_28984567 的回复:

[quote=引用 4 楼 qq_36011222 的回复:]
如果B表中的cid的值是不确定的该怎么办，比如可能是“胜利、第一名、赢·······”

那要统计那些？比如说如果是“挺好”、“不错”、“有点意思”、“棒极了”、“还行吧”、“凑活”怎么统计，统计的规则是什么？[/quote]
额。。就是去掉cid这一列，统计B表与A表中bid相对应的数据数量，这么说不知道您能不能明白，本来把cid放上去是方便描述的，没想到弄巧成拙了

[二月十六]

--测试数据
if not object_id(N'Tempdb..#A') is null
    drop table #A
Go
Create table #A([aid] nvarchar(23),[bid] nvarchar(22))
Insert #A
select N'赛跑',N'王大' union all
select N'跳远',N'王大' union all
select N'游泳',N'张三' union all
select N'实心球',N'李四'
GO
if not object_id(N'Tempdb..#B') is null
    drop table #B
Go
Create table #B([aid] nvarchar(21),[bid] nvarchar(22),[cid] nvarchar(22))
Insert #B
select N'a',N'王大',N'胜' union all
select N'b',N'王大',N'胜' union all
select N'd',N'李四',N'胜'
Go
--测试数据结束
SELECT
    ISNULL(t1.bid, t2.bid) AS bid,
    ISNULL(t1.anum, 0)     anum,
    ISNULL(t2.bnum, 0)     bnum
FROM
    (
        SELECT
            bid,
            COUNT(1) AS anum
        FROM
            #A
        GROUP BY
            bid
    )     t1
    FULL JOIN
        (
            SELECT
                bid,
                COUNT(1) bnum
            FROM
                #B
            GROUP BY
                bid
        ) t2
            ON t2.bid = t1.bid;