字段1 字段2
A 00
A 01
B 00
B 01
C 00
C 02
D 00
D 02
E 01
E 02
上面是表的模拟数据,分别统计出字段2为(00,01)、(00,02)、(01,02)三种情况的数据,比如上表对应三种情况的正确结果应该是2、2、1,这个sql语句应该如何写,各位大虾帮帮忙
A 00
A 01
B 00
B 01
C 00
C 02
D 00
D 02
E 01
E 02
上面是表的模拟数据,分别统计出字段2为(00,01)、(00,02)、(01,02)三种情况的数据,比如上表对应三种情况的正确结果应该是2、2、1,这个sql语句应该如何写,各位大虾帮帮忙
create function f_str(@字段1 varchar(10))
returns varchar(100)
as
begin
declare @str varchar(100)
set @str=''
select @str=@str+','+字段2 from tb where 字段1=@字段1
set @str=stuff(@str,1,1,'')
return (@str)
end
select
[00,01]=sum(case when dbo.f_str(字段1)='00,01' then 1 else 0 end )
[00,02]=sum(case when dbo.f_str(字段1)='00,02' then 1 else 0 end )
[01,02]=sum(case when dbo.f_str(字段1)='01,02' then 1 else 0 end )
from 表
group by 字段1
select sum(case when t2=1 then 1 else 0 end) n1,
sum(case when t2=2 then 1 else 0 end) n2,
sum(case when t2=3 then 1 else 0 end) n3
from
(
select 字段1,sum(字段2) as t2 from table
group by 字段1
)temptable不考虑特出情况
if object_id('tempdb.dbo.#T') is not null drop table #T
create table #T (C1 varchar(1),C2 varchar(2))
insert into #T
select 'A','00' union all
select 'A','01' union all
select 'B','00' union all
select 'B','01' union all
select 'C','00' union all
select 'C','02' union all
select 'D','00' union all
select 'D','02' union all
select 'E','01' union all
select 'E','02';
with T as
(
select C1,C2=(stuff((select ','+C2 from #T where C1=a.C1 for xml path('')),1,1,'')) from #T a group by C1
)
select C2, [Count]=count(*) from T group by C2
/*
C2 Count
-------- -----------
00,01 2
00,02 2
01,02 1
*/
--接分
if object_id('tb') is not null
drop table tb
if object_id('f_getcount') is not null
drop function f_getcount
go
create table tb (C1 varchar(1),C2 varchar(2))
insert into tb
select 'A','00' union all
select 'A','01' union all
select 'B','00' union all
select 'B','01' union all
select 'C','00' union all
select 'C','02' union all
select 'D','00' union all
select 'D','02' union all
select 'E','01' union all
select 'E','02'
--select * from tb
go
create function f_getcount(@C1 varchar(1))
returns varchar(100)
as
begin
declare @merge varchar(100)
select @merge=isnull(@merge,'')+C2+',' from tb where C1=@C1
set @merge='('+substring(@merge,1,len(@merge)-1)+')'
RETURN @merge
end
GO
select type_ch,count(1) as [count] from
(
select a.C1,dbo.f_getcount(a.C1) as type_ch from (select distinct C1 from tb) a
) b
group by type_ch
if object_id('tempdb.dbo.#T') is not null drop table #T
create table #T (C1 varchar(1),C2 varchar(2))
insert into #T
select 'A','00' union all
select 'A','01' union all
select 'B','00' union all
select 'B','01' union all
select 'C','00' union all
select 'C','02' union all
select 'D','00' union all
select 'D','02' union all
select 'E','01' union all
select 'E','02'select count(a.c1)
from(
select DISTINCT
a.c1,
case when a.c2>b.c2 then a.c2+b.c2 else b.c2+a.c2 end c2
from #t a,#t b
where a.c1=b.c1
and a.c2!=b.c2
) as a
group by a.c2
insert into ta
select 'A','00' union all
select 'A','01' union all
select 'B','00' union all
select 'B','01' union all
select 'C','00' union all
select 'C','02' union all
select 'D','00' union all
select 'D','02' union all
select 'E','01' union all
select 'E','02'goselect c3,count(1) as cnt
from
(
select a.*,a.c2+'-'+b.c2 as c3
from ta a,ta b
where a.c1 = b.c1 and a.c2 < b.c2) a
group by a.c3
drop table ta/*
c3 cnt
----- -----------
00-01 2
00-02 2
01-02 1(所影响的行数为 3 行)
*/
from 表 a,表 b
where a.字段1 = b.字段1 and a.字段2<b.字段2
and rtrim(a.字段2)+','+rtrim(b.字段2) in ('00,01','00,02','01,02')
group by rtrim(a.字段2)+','+rtrim(b.字段2)
create table #T (C1 varchar(1),C2 varchar(2))
insert into #T
select 'A','00' union all
select 'A','01' union all
select 'B','00' union all
select 'B','01' union all
select 'C','00' union all
select 'C','02' union all
select 'D','00' union all
select 'D','02' union all
select 'E','01' union all
select 'E','02';
with T as
(
select C1,C2=(stuff((select ','+C2 from #T where C1=a.C1 for xml path('')),1,1,'')) from #T a group by C1
)
select C2, [Count]=count(*) from T group by C2