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

 
  SQL   Server的一张表t1，有四个字段，六条纪录。其中字段ID是每条纪录的序号。如下表所示:  
   
  ID   |   Place   Time     Level  
  ---|-------------------    
    1   |     P1         T3         L1          
    2   |     P1         T1         L1          
    3   |     P3         T1         L2          
    4   |     P2         T2         L3          
    5   |     P1         T1         L1          
    6   |     P2         T3         L3          
   
  也就是说字段"Place"的取值都以"P"开头，字段"Time"的取值都以"T"开头，字段"Level"的取值都以"L"开头。  
   
   
  想实现的是：  
   
  生成所有项的组合,并统计表t1中包含该组合的纪录的条数。  
   
  比如：  
   
  1项的组合：P1,P2,P3,T1,T2,T3,L1,L2,L3         然后统计含有P1的记录条数，含有P2的纪录的条数...  
   
  2项的组合：P1_T1,   P1_T2,   P1_T3,   P2_T1,   P2_T2,   P2_T3...           然后统计同时含有P1和T1的记录条数，同时含有P1和T2的纪录的条数.....                
   
  3项的组合：P1_T1_L1,   P1_T1_L2,   P1_T1_L3...                 然后统计同时含有P1、T1和L1的记录条数，同时含有P1、T1和L2的纪录的条数...                
   
  （也不知道我的表述请不清楚哦）  
   
  如果能够解决，可以一直加分到500分！   绝不食言哦！  
   
  我的E-MAIL:   rye99@126.com  
   
   
  问题点数：100、回复次数：20Top

1 楼zjcxc（邹建）回复于 2004-09-03 21:49:50 得分 0

好像不是太明白楼主的意思.Top

2 楼mulintaomulintao（）回复于 2004-09-03 21:56:18 得分 10

 
  1项的组合   select   count(ID)   FORM   t1   group   by   Place   order   by   Place    
  2项的组合   select   count(ID)   FORM   t1   group   by   (Place   +   '-'   +   Time   )   order   by   (Place   +   '-'   +   Time   )  
  3项的组合   select   count(ID)   FORM   t1   group   by   (Place   +   '-'   +   Time   +   '-'   +Level     )   order   by   (Place   +   '-'   +   Time   +   '-'   +Level     )  
  不知对不对?????//  
  Top

3 楼suwenli（）回复于 2004-09-03 22:14:31 得分 0

表达起来确实是很费劲。我干脆把最后的结果形式写出来吧  
   
  最后得到三个记录集：  
   
  第一个记录集是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  
    ...........  
   
  不知道这样表述清楚没有？Top

4 楼zjcxc（邹建）回复于 2004-09-03 22:23:00 得分 0

--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  
  Top

5 楼zjcxc（邹建）回复于 2004-09-03 22:23:12 得分 0

--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  
  Top

6 楼zjcxc（邹建）回复于 2004-09-03 22:30:22 得分 0

--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.itemTop

7 楼huwgao（小楼听雨）回复于 2004-09-04 01:29:00 得分 30

楼上的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.item  
   
  drop   table   t1Top

8 楼huwgao（小楼听雨）回复于 2004-09-04 01:30:51 得分 0

--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   行）Top

9 楼huwgao（小楼听雨）回复于 2004-09-04 03:38:46 得分 0

--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.itemTop

10 楼laker_tmj（laker）回复于 2004-09-04 09:15:44 得分 0

up   learnTop

11 楼suwenli（）回复于 2004-09-04 10:01:08 得分 0

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

12 楼zjcxc（邹建）回复于 2004-09-04 10:22:39 得分 0

--示例  
   
  --示例数据  
  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   行）  
  --*/Top

13 楼zjcxc（邹建）回复于 2004-09-04 10:23:29 得分 0

--示例  
   
  --示例数据  
  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   行）  
  --*/Top

14 楼zjcxc（邹建）回复于 2004-09-04 10:25:26 得分 0

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

15 楼huwgao（小楼听雨）回复于 2004-09-04 11:54:25 得分 0

如果字段比较多，用下面的代码会清晰些，不过效率没有测试过  
  --以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”Top

16 楼zjcxc（邹建）回复于 2004-09-04 14:51:09 得分 60

--示例  
   
  --示例数据  
  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   end  
   
  if   @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)  
  end  
   
  set   @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  
   
  /*--测试结果--*/  
  Top

17 楼zjcxc（邹建）回复于 2004-09-04 14:52:29 得分 0

如果我的理解没有错,就可以写一个类似上面的通用存储过程来处理.  
   
  实现了自动提取表中的字段,并根据组合项的多少来组合的功能.Top

18 楼netcoder（朱二）回复于 2004-09-04 16:01:33 得分 0

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

19 楼netcoder（朱二）回复于 2004-09-04 16:02:30 得分 0

我的打字能力怎么这么差  
   
  不是白字就是漏字：（  
  Top

20 楼suwenli（）回复于 2004-09-04 19:46:27 得分 0

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

相关问题

• 组合
• 字段
• sa
• 条数
• place
• count
• unionselect
• 统计
• 取值
• 含有

• 帖主：suwenli
• mulintaomulintao
• huwgao
• zjcxc

• SQL Server类图书