求：统计一些项目的组合的程序 (可累计加分到500！)

表达起来确实是很费劲。我干脆把最后的结果形式写出来吧最后得到三个记录集：第一个记录集是1项组合的统计结果(item是项的名称，count是记录数)item  count
P1    3
P2    2
P3    1
T1    3
T2    1
T3    2
L1    3
L2    1
L3    2

第二个记录集是2项组合的统计结果(item是项的名称，count是记录数)item   count
P1_T1   2
P1_T2   0
P1_T3   1
P1_L1   3
P1_L2   0
P1_L3   0
P2_T1   0
P2_T2   1
P2_T3   1
...........

第三个记录集是3项组合的统计结果(item是项的名称，count是记录数)item   count
P1_T1_L1   2
P1_T1_L2   0
P1_T1_L3   0
P1_T2_L1   0
P1_T2_L2   0
P1_T2_L3   0
P1_T3_L1   1
P1_T3_L2   0
P1_T3_L3   0
P2_T1_L1   0
P2_T1_L2   0
P2_T1_L3   0
P2_T2_L1   0
P2_T2_L2   0
P2_T2_L3   1
P2_T3_L1   0
P2_T3_L2   0
P2_T3_L3   1
...........不知道这样表述清楚没有？

--1项的组合
select item=Place,[count]=count(*) from t1 group by Place
union all
select item=Time,[count]=count(*) from t1 group by Time
union all
select item=Level,[count]=count(*) from t1 group by Level

--2项的组合
select a.item,[count]=count(b.ID)
from(
select item=a.Place+'_'+b.Place,a=a.Place,b=b.Place
from(
select Place from t1
union
select Time from t1
union
select Level from t1
)a,(
select Place from t1
union
select Time from t1
union
select Level from t1
)b where a.Place<b.Place
)a left join t1 b
on a.a=b.Place and a.b=b.Time
or a.a=b.Place and a.b=b.Level
or a.a=b.Time and a.b=b.Level
group by a.item

--3项的组合
select a.item,[count]=count(b.ID)
from(
select item=a.Place+'_'+b.Place+'_'+c.Place
,a=a.Place,b=b.Place,c=c.Place
from(
select Place from t1
union
select Time from t1
union
select Level from t1
)a,(
select Place from t1
union
select Time from t1
union
select Level from t1
)b,(
select Place from t1
union
select Time from t1
union
select Level from t1
)c where a.Place<b.Place and b.Place<c.Place
)a left join t1 b
on a.a=b.Place and a.b=b.Time and a.c=b.Level
or a.a=b.Place and a.c=b.Time and a.b=b.Level
or a.b=b.Place and a.a=b.Time and a.c=b.Level
or a.b=b.Place and a.c=b.Time and a.a=b.Level
or a.c=b.Place and a.a=b.Time and a.b=b.Level
or a.c=b.Place and a.b=b.Time and a.a=b.Level
group by a.item

楼上的2、3项有问题，2项会产生【P1_P2】、【L1_L2】这样的项目，3项也是，产生【P1_P2_P3】……等项目，理解楼主的意思应该是在P、T、L三类之间互相组合，类似P1、P2之间不用组合。--构造测试数据
create table t1(id int,Place char(2),Time char(2),Level char(2))
insert into t1 select 1 ,'P1','T3','L1'
union select 2 ,'P1','T1','L1'
union select 3 ,'P3','T1','L2'
union select 4 ,'P2','T2','L3'
union select 5 ,'P1','T1','L1'
union select 6 ,'P2','T3','L3'--2项
select c.item,count=isnull(d.count,0)
from (
select distinct item=a.item+'_'+b.item
from (
select item=Place,f=1 from t1
union all select Time,f=2 from t1
union all select Level,f=3 from t1) a
cross join (
select item=Place,f=1 from t1
union all select Time,f=2 from t1
union all select Level,f=3 from t1) b
where a.f<b.f) c
left join (
select item=a.item+'_'+b.item
,count=count(a.item+'_'+b.item)
from (
select id,item=Place,f=1 from t1
union all select id,item=Time,f=2 from t1
union all select id,item=Level,f=3 from t1) a
cross join (
select id,item=Place,f=1 from t1
union all select id,item=Time,f=2 from t1
union all select id,item=Level,f=3 from t1) b
where a.id=b.id and a.f<b.f
group by a.item+'_'+b.item) d
on c.item=d.item--3项
select d.item,count=isnull(e.count,0)
from (
select distinct item=a.Place+'_'+b.Time+'_'+c.Level from t1 a
cross join (select Time from t1) b
cross join (select Level from t1) c) d
left join (
select item=Place+'_'+Time+'_'+Level
,count=count(Place+'_'+Time+'_'+Level)
from t1
group by Place+'_'+Time+'_'+Level) e
on d.item=e.itemdrop table t1

--2项返回结果item count
----- -----------
P1_L1 3
P1_L2 0
P1_L3 0
P1_T1 2
P1_T2 0
P1_T3 1
P2_L1 0
P2_L2 0
P2_L3 2
P2_T1 0
P2_T2 1
P2_T3 1
P3_L1 0
P3_L2 1
P3_L3 0
P3_T1 1
P3_T2 0
P3_T3 0
T1_L1 2
T1_L2 1
T1_L3 0
T2_L1 0
T2_L2 0
T2_L3 1
T3_L1 1
T3_L2 0
T3_L3 1（所影响的行数为 27 行）--3项返回结果item count
-------- -----------
P1_T1_L1 2
P1_T1_L2 0
P1_T1_L3 0
P1_T2_L1 0
P1_T2_L2 0
P1_T2_L3 0
P1_T3_L1 1
P1_T3_L2 0
P1_T3_L3 0
P2_T1_L1 0
P2_T1_L2 0
P2_T1_L3 0
P2_T2_L1 0
P2_T2_L2 0
P2_T2_L3 1
P2_T3_L1 0
P2_T3_L2 0
P2_T3_L3 1
P3_T1_L1 0
P3_T1_L2 1
P3_T1_L3 0
P3_T2_L1 0
P3_T2_L2 0
P3_T2_L3 0
P3_T3_L1 0
P3_T3_L2 0
P3_T3_L3 0（所影响的行数为 27 行）

--2项另解
select d.item,count=isnull(e.count,0)
from (
select distinct item=a.Place+'_'+b.Time from t1 a,t1 b
union all select distinct a.Place+'_'+b.Level from t1 a,t1 b
union all select distinct a.Time+'_'+b.Level from t1 a,t1 b) d
left join (
select item,count=count(item)
from (
select item=Place+'_'+Time from t1
union all select Place+'_'+Level from t1
union all select Time+'_'+Level from t1
) a group by a.item) e
on d.item=e.item--3项简化
select d.item,count=isnull(e.count,0)
from (
select distinct item=a.Place+'_'+b.Time+'_'+c.Level from t1 a,t1 b,t1 c) d
left join (
select item=Place+'_'+Time+'_'+Level
,count=count(Place+'_'+Time+'_'+Level)
from t1
group by Place+'_'+Time+'_'+Level) e
on d.item=e.item

感谢 mulintaomulintao、邹建，感谢huwgao！我的问题比较麻烦，所以表达起来不是很清楚。 huwgao的结果就是我所要的。我发现如果表t1数据量比较大的话，比如记录数远不止6条，或者再增加几个字段的话，就会产生大量的冗余数据，比如"P1_T2 0"这样的数据，在实际的表里是不存在同时含有"P1"和"T2"这样的纪录的。大量的冗余数据也导致运行速度比较慢（因为我要用一些外部程序调用这段SQL）。所以能否对没有出现过的项的组合不进行统计？也就是结果里面把所有 count=0的结果消除掉（应该是在生成各项的组合的时候，根本不对没有出现过的组合进行统计，这样才能减少运行速度）。不知道好不好实现阿？对于恢复的几位，我一定会另开贴加分满500，决不食言哦！

--示例--示例数据
create table t1(ID int,Place varchar(10),Time varchar(10),Level varchar(10))
insert t1 select 1,'P1','T3','L1'
union all select 2,'P1','T1','L1'
union all select 3,'P3','T1','L2'
union all select 4,'P2','T2','L3'
union all select 5,'P1','T1','L1'
union all select 6,'P2','T3','L3'
go--2项的组合,只查有数据的
select Item=Place+'_'+Time,[Count]=count(*)
from t1
group by Place,Time
union all
select Item=Place+'_'+Level,[Count]=count(*)
from t1
group by Place,Level
union all
select Item=Time+'_'+Level,[Count]=count(*)
from t1
group by Time,Level
go--删除测试
drop table t1/*--测试结果Item                  Count
--------------------- -----------
P1_T1                 2
P3_T1                 1
P2_T2                 1
P1_T3                 1
P2_T3                 1
P1_L1                 3
P3_L2                 1
P2_L3                 2
T1_L1                 2
T3_L1                 1
T1_L2                 1
T2_L3                 1
T3_L3                 1（所影响的行数为 13 行）
--*/

--示例--示例数据
create table t1(ID int,Place varchar(10),Time varchar(10),Level varchar(10))
insert t1 select 1,'P1','T3','L1'
union all select 2,'P1','T1','L1'
union all select 3,'P3','T1','L2'
union all select 4,'P2','T2','L3'
union all select 5,'P1','T1','L1'
union all select 6,'P2','T3','L3'
go--3项的组合,只查有数据的
select Item=Place+'_'+Time+'_'+Level,[Count]=count(*)
from t1
group by Place,Time,Level
go--删除测试
drop table t1/*--测试结果Item                             Count
-------------------------------- -----------
P1_T1_L1                         2
P1_T3_L1                         1
P2_T2_L3                         1
P2_T3_L3                         1
P3_T1_L2                         1（所影响的行数为 5 行）
--*/

不知道有没有理解错楼主的意思.--这几种组合应该只算是一种吧?
P1_T1_L1
P1_L1_T1
T1_P1_P1
T1_L1_P1
L1_P1_T1
L1_T1_P1

如果字段比较多，用下面的代码会清晰些，不过效率没有测试过
--以2项为例
select item=a.item+'_'+b.item
,count=count(a.item+'_'+b.item)
from (
select id,item=Place,f=1 from t1
union all select id,item=Time,f=2 from t1
union all select id,item=Level,f=3 from t1) a
cross join (
select id,item=Place,f=1 from t1
union all select id,item=Time,f=2 from t1
union all select id,item=Level,f=3 from t1) b
where a.id=b.id and a.f<b.f
group by a.item+'_'+b.item另：同楼上，P、T、L之间不考虑排序（P1，T1，L1）=（T1，P1，L1），……
考虑排序时只需要修改“a.f<b.f”条件为“a.f<>b.f”

--示例--示例数据
create table t1(ID int,Place varchar(10),Time varchar(10),Level varchar(10),aa varchar(10))
insert t1 select 1,'P1','T3','L1','aa'
union all select 2,'P1','T1','L1','bb'
union all select 3,'P3','T1','L2','aa'
union all select 4,'P2','T2','L3','aa'
union all select 5,'P1','T1','L1','aa'
union all select 6,'P2','T3','L3','bb'
go--通用的统计存储过程
create proc p_qry
@count int=1 --组合的项数
as
declare @sa Nvarchar(4000),@sb Nvarchar(4000)
declare @s2 Nvarchar(4000),@s3 Nvarchar(4000)
declare @s varchar(8000)if isnull(@count,0)<0 set @count=1
select a=name,b=colid
into #t from syscolumns
where id=object_id(N't1') and name<>'ID'
set @count=case when @count>@@rowcount then @@rowcount else @count endif @count=1
set @sa='select @s=@s+'' union all select item=[''+a+''],[count]=count(*) from t1 group by [''+a+'']'' from #t'
else
begin
select @sa='select @s=@s+'' union all select item=[''+a.a+'']'''
,@sb='''[''+a.a+'']'''
,@s2='from #t a'
,@s3='where a.b'
while @count>1
select @count=@count-1
,@sa=@sa+'+''+''''_''''+[''+'+char(@count/26+97)+char(@count%26+97)+'.a+'']'''
,@sb=@sb+'+'',[''+'+char(@count/26+97)+char(@count%26+97)+'.a+'']'''
,@s2=@s2+',#t '+char(@count/26+97)+char(@count%26+97)
,@s3=@s3+'<'++char(@count/26+97)+char(@count%26+97)+'.b'
+' and '++char(@count/26+97)+char(@count%26+97)+'.b'
select @sa=@sa+'+'',[count]=count(*) from t1 group by ''+'+@sb+' '+@s2+' '+left(@s3,len(@s3)-9)
endset @s=''
exec sp_executesql @sa,N'@s varchar(8000) out',@s out
set @s=stuff(@s,1,11,'')
exec(@s)
go--调用
exec p_qry 3
go--删除测试
drop table t1
drop proc p_qry/*--测试结果--*/

也就是结果里面把所有 count=0的结果消除掉（应该是在生成各项的组合的时候，根本不对没有出现过的组合进行统计，这样才能减少运行速度）。
------------------------------------------------
由于要判断，count是否等于，即使去掉这样的记录，也不会省掉判断的时候
不过返回的记录集就要少多了

谢谢各位，我的问题基本解决。--还是这里的牛人多阿！我另开贴加分，贴子的标题是“统计一些项目的组合2”，希望邹建、小楼听雨、 mulintaomulintao赏光哦

调试易

求：统计一些项目的组合的程序 (可累计加分到500！)

解决方案 »