34,590
社区成员
发帖
与我相关
我的任务
分享
--测试数据
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;
--测试数据
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;
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
*/
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