SQL难题。。。〔3〕

Question

SELECT
    Name,
    SUM(CASE WHEN flag = 'QYFR' THEN 1 ELSE 0 END) AS QYFR_COUNT,
    SUM(CASE WHEN flag = 'CWFZR' THEN 1 ELSE 0 END) AS CWFZR_COUNT,
    SUM(CASE WHEN flag = 'BSY' THEN 1 ELSE 0 END) AS BSY_COUNT,
    SUM(CASE WHEN flag = 'BSY2' THEN 1 ELSE 0 END) AS BSY2_COUNT,
FROM (
    SELECT QYFR AS Name,flag = 'QYFR' FROM tb WHERE QYFR IS NOT NULL
    UNION ALL
    SELECT CWFZR,'CWFZR' FROM tb WHERE CWFZR IS NOT NULL
    UNION ALL
    SELECT BSY,'BSY' FROM tb WHERE BSY IS NOT NULL
    UNION ALL
    SELECT BSY2,'BSY2' FROM tb WHERE BSY2 IS NOT NULL
) AS A
GROUP BY Name
HAVING SUM(CASE WHEN flag = 'QYFR' THEN 1 ELSE 0 END) > 1
    AND SUM(CASE WHEN flag = 'CWFZR' THEN 1 ELSE 0 END) > 1
    AND SUM(CASE WHEN flag = 'BSY' THEN 1 ELSE 0 END) > 1
    AND SUM(CASE WHEN flag = 'BSY2' THEN 1 ELSE 0 END) > 1
ORDER BY 
    SUM(CASE WHEN flag = 'QYFR' THEN 1 ELSE 0 END) 
   +SUM(CASE WHEN flag = 'CWFZR' THEN 1 ELSE 0 END)
   +SUM(CASE WHEN flag = 'BSY' THEN 1 ELSE 0 END)
   + SUM(CASE WHEN flag = 'BSY2' THEN 1 ELSE 0 END) DESC;

Answer 1

SELECT
    Name,
    SUM(CASE WHEN flag = 'QYFR' THEN 1 ELSE 0 END) AS QYFR_COUNT,
    SUM(CASE WHEN flag = 'CWFZR' THEN 1 ELSE 0 END) AS CWFZR_COUNT,
    SUM(CASE WHEN flag = 'BSY' THEN 1 ELSE 0 END) AS BSY_COUNT,
    SUM(CASE WHEN flag = 'BSY2' THEN 1 ELSE 0 END) AS BSY2_COUNT,
FROM (
    SELECT QYFR AS Name,flag = 'QYFR' FROM tb WHERE QYFR IS NOT NULL
    UNION ALL
    SELECT CWFZR,'CWFZR' FROM tb WHERE CWFZR IS NOT NULL
    UNION ALL
    SELECT BSY,'BSY' FROM tb WHERE BSY IS NOT NULL
    UNION ALL
    SELECT BSY2,'BSY2' FROM tb WHERE BSY2 IS NOT NULL
) AS A
GROUP BY Name
HAVING 
    SUM(CASE WHEN flag = 'QYFR' THEN 1 ELSE 0 END) 
   +SUM(CASE WHEN flag = 'CWFZR' THEN 1 ELSE 0 END)
   +SUM(CASE WHEN flag = 'BSY' THEN 1 ELSE 0 END)
   + SUM(CASE WHEN flag = 'BSY2' THEN 1 ELSE 0 END) > 1
ORDER BY 
    SUM(CASE WHEN flag = 'QYFR' THEN 1 ELSE 0 END) 
   +SUM(CASE WHEN flag = 'CWFZR' THEN 1 ELSE 0 END)
   +SUM(CASE WHEN flag = 'BSY' THEN 1 ELSE 0 END)
   + SUM(CASE WHEN flag = 'BSY2' THEN 1 ELSE 0 END) DESC;

Answer 2

select a as 人物名字
,sum(case b when 'QYFR' then 1 else 0 end) as 在QYFR出现次数
,sum(case b when 'CWFZR' then 1 else 0 end) as 在CWFZR出现次数
,sum(case b when 'BSY' then 1 else 0 end) as 在BSY出现次数
,sum(case b when 'BSY2' then 1 else 0 end) as 在BSY2出现次数
from (
select QYFR as a,'QYFR' as b
from tb
where QYFR<>''
union all
select CWFZR as a,'CWFZR' as b
from tb
where CWFZR<>''
union all
select BSY as a,'BSY' as b
from tb
where BSY<>''
union all
select BSY2 as a,'BSY2' as b
from tb
where BSY2<>''
) as t
group by a

Answer 3

select a as 人物名字
,sum(case b when 'QYFR' then 1 else 0 end) as 在QYFR出现次数
,sum(case b when 'CWFZR' then 1 else 0 end) as 在CWFZR出现次数
,sum(case b when 'BSY' then 1 else 0 end) as 在BSY出现次数
,sum(case b when 'BSY2' then 1 else 0 end) as 在BSY2出现次数
from (
select QYFR as a,'QYFR' as b
from tb
where QYFR<>''
union all
select CWFZR as a,'CWFZR' as b
from tb
where CWFZR<>''
union all
select BSY as a,'BSY' as b
from tb
where BSY<>''
union all
select BSY2 as a,'BSY2' as b
from tb
where BSY2<>''
) as t
group by a
having count(1)>1
order by count(1) desc

Answer 4

select  [人物名字]=tname,
[在QYFR出现次数]=sum(case when flag='QYFR' then 1 else 0 end),
[在CWFZR出现次数]=sum(case when flag='CWFZR' then 1 else 0 end),
[在BSY出现次数]=sum(case when flag='BSY' then 1 else 0 end),
[在BSY2出现次数]=sum(case when flag='BSY2' then 1 else 0 end)
from (
select tname=BSY,flag='BSY' from tab union all 
select tname=BSY2,flag='BSY2' from tab union all 
select tname=CWFZR,flag='CWFZR' from tab union all 
select tname=QYFR,flag='QYFR' from tab
) t
group by tname having count(tname)>1

Answer 5

drop table #temp1
drop table #temp2
create table #temp1(flditemcode varchar(20),flditemcode2 varchar(20),flditemcode3 varchar(20),flditemcode4 varchar(20))insert into #temp1
select 'A','B','C','D' union all
select 'd','a','b','b' union all
select 'b','B','C','c' union all
select 'b','a','a','D' union all
select 'c','B','C','a' union all
select 'A','B','d','b'select distinct flditemcode into #temp2  from #temp1
union
select flditemcode2 from #temp1
union 
select flditemcode3 from #temp1
union 
select flditemcode4 from #temp1
select
flditemcode,
'1'=(select count(*) from #temp1 b where a.flditemcode=b.flditemcode),
'2'=(select count(*) from #temp1 b where a.flditemcode=b.flditemcode2),
'3'=(select count(*) from #temp1 b where a.flditemcode=b.flditemcode3),
'4'=(select count(*) from #temp1 b where a.flditemcode=b.flditemcode4)
from #temp2 a

Answer 6

declare @s varchar(4000),@t varchar(4000)
set @s='select [人物名字]=tname '
select @s=@s+',[在'+b.name+'出现次数]=sum(case when flag='''+b.name+''' then 1 else 0 end)'
from sysobjects a,syscolumns b where a.id=b.id and a.name='tab' and b.name<>'CODE'select @t=isnull(@t+' union all ','')+'select tname='+b.name+',flag='''+b.name+''' from tab' 
from sysobjects a,syscolumns b where a.id=b.id and a.name='tab' and b.name<>'CODE'
exec(@s+'from ('+@t+') t group by tname having count(tname)>1')

Answer 7

use tempdb
create table People
(code varchar(12),
QYFR nvarchar(20),
CWFZR nvarchar(20),
BSY nvarchar(20),
BSY2 nvarchar(20))delete People
insert into People(code,QYFR,CWFZR,BSY)
select 
4407039901,    N'李育民',                         null,                                 N'黄惠孺'              
union all select
4407859901,    N'李远光',                          N'何廷前',                            N'何廷前'              
union all select                                        
4407910001,    N'林永发',                          N'林春明',                            N'林春明'              
union all select                                        
4407910002,    N'赵英材',                          N'杨宝琴',                            N'杨宝琴'              
union all select                                        
4407910003,    N'黄俊辉',                          N'李小洛',                             null                   
union all select                                        
4407910004,    N'谭有弟',                          N'林卫东',                            N'谭东彤'              
union all select                                        
4407910005,    N'岑崇欢',                          N'谭小娟',                            N'谭小娟'              
union all select                                        
4407910006,    N'施子衡',                          N'林浩',                            N'温瑞贤'              
union all select                                        
4407910007,    N'李宇星',                          N'刘红',                            N'王丽晖'              
union all select                                        
4407910008,    N'张大毛',                          N'郑幕芬',                            N'刘素薇'     SELECT A.Name,isnull(QYFR,0),isnull(CWFZR,0),isnull(BSY,0),isnull(BSY2,0)
FROM
(
select QYFR Name from People
union
select CWFZR Name from People
union
select BSY Name from People
union
select BSY2 Name from People
) A left join (select QYFR Name, count(*) QYFR from People group by QYFR) B on A.Name=B.Name
left join (select CWFZR Name, count(*) CWFZR from People group by CWFZR) C on A.Name=C.Name
left join (select BSY Name, count(*) BSY from People group by BSY) D on A.Name=D.Name
left join (select BSY2 Name, count(*) BSY2 from People group by BSY2) E on A.Name=E.Name
where isnull(QYFR,0)+isnull(CWFZR,0)+isnull(BSY,0)+isnull(BSY2,0)>=2