数据查询－－－在线等

有一张表table1,基本结构是这样的，（第2列MQCW开头表示warning，MQCE表示error）  
  2907839,MQCW2BS0,1011006,2005-06-30   15:59:00  
  2521801,MQCW2IS0,1011006,2005-06-30   15:59:00  
  4178958,MQCE2IS0,1011006,2005-06-30   15:59:00  
  4188081,MQCE2IS0,1011006,2005-06-30   15:59:00  
  4201404,MQCE1CF1,66740,     2005-06-30   16:01:00  
  1791971,MQCW4BS2,66740,     2005-06-30   16:01:00  
  171862,   MQCW4BS2,66740,     2005-06-30   16:01:00  
  4068991,MQCE4BS2,66740,     2005-06-30   16:01:00  
  4201404,MQCE4BS2,66740,     2005-06-30   16:01:00  
  4201401,MQCE1CF1,51143,     2005-06-30   16:13:00  
  4086388,MQCW1CF1,51143,     2005-06-30   16:13:00  
  2230209,MQCW1CF1,51143,     2005-06-30   16:13:00  
  期望结果：  
  CIK                   errorCount         warningCount  
  1011006           2                           2  
  66740               3                           2  
  51143               1                           2  
   
  各位高手有什么办法？？？？急，在线等  
  问题点数：20、回复次数：5Top

1 楼zzb23（zzb）回复于 2005-07-01 10:25:11 得分 0

select   CIK,count(MQ)   from   table1   where   MQ   like   '%MQCW%'   group   by   CIK,MQ  
  select   CIK,count(MQ)   from   table1   where   MQ   like   '%MQCE%'   group   by   CIK,MQ  
  你试一下，我也记不大清楚了Top

2 楼paoluo（一天到晚游泳的鱼）回复于 2005-07-01 10:25:49 得分 10

Select    
  CIK,  
  SUM(Case   Left(ColName,4)   When   'MQCE'   Then   1   Else   0   End)   As   errorCount,  
  SUM(Case   Left(ColName,4)   When   'MQCW'   Then   1   Else   0   End)   As   warningCount  
  from   table1  
  Group   By   CIKTop

3 楼phantomMan（）回复于 2005-07-01 10:27:44 得分 4

select   field3,sum(warning)   as   warningCount,   sum(error)   as   errorCount   from   (  
  select field3,  
  case   when   (left(field2,4)='MQCW')   then   1   else   0   end   as   warning,  
  case   when   (left(field2,4)='MQCE')   then   1   else   0   end   as   error  
  )a  
  group   by   field3  
  Top

4 楼hsj20041004（光芒）回复于 2005-07-01 10:29:07 得分 3

a               b                   c                   d  
  2907839,MQCW2BS0,1011006,2005-06-30   15:59:00  
  2521801,MQCW2IS0,1011006,2005-06-30   15:59:00  
  4178958,MQCE2IS0,1011006,2005-06-30   15:59:00  
  4188081,MQCE2IS0,1011006,2005-06-30   15:59:00  
  4201404,MQCE1CF1,66740,     2005-06-30   16:01:00  
  1791971,MQCW4BS2,66740,     2005-06-30   16:01:00  
  171862,   MQCW4BS2,66740,     2005-06-30   16:01:00  
  4068991,MQCE4BS2,66740,     2005-06-30   16:01:00  
   
  select   c,sum(case   left(b,4)='MQCW'   then   1   else   0   then)   as   warningCount,  
                  sum(case   left(b,4)='MQCE'   then   1   else   0   then)   as   errorCount  
  from   table1   group   by   c  
  Top

5 楼hsj20041004（光芒）回复于 2005-07-01 10:30:01 得分 3

a               b                   c                   d  
  2907839,MQCW2BS0,1011006,2005-06-30   15:59:00  
  2521801,MQCW2IS0,1011006,2005-06-30   15:59:00  
  4178958,MQCE2IS0,1011006,2005-06-30   15:59:00  
  4188081,MQCE2IS0,1011006,2005-06-30   15:59:00  
  4201404,MQCE1CF1,66740,     2005-06-30   16:01:00  
  1791971,MQCW4BS2,66740,     2005-06-30   16:01:00  
  171862,   MQCW4BS2,66740,     2005-06-30   16:01:00  
  4068991,MQCE4BS2,66740,     2005-06-30   16:01:00  
   
  select   c,sum(case   when   left(b,4)='MQCW'   then   1   else   0   end)   as   warningCount,  
                  sum(case   when   left(b,4)='MQCE'   then   1   else   0   end)   as   errorCount  
  from   table1   group   by   cTop

相关问题

• mqcw
• mqce
• cik
• bs
• mq
• errorcount
• cf
• then1else
• sum
• warning

• 帖主：andywang110001
• paoluo
• phantomMan
• hsj20041004
• hsj20041004

• SQL Server类图书