请教一个SQL语句

Question

表格如下：orders_id       steps_bc       un_barcode      unit_status_id
--------------- -------------- --------------- -------------------
2         1Label01 SN001         13
2         1Label01 SN002         13
2         1Label01 SN003         13
2         1Label01 SN004         3
2         1Label01 SN005         13
2         1Label01 SN004         3
2         1Label01 SN005         133         1Label01 SN006         3
3         1Label01 SN006         3
3         1Label01 SN006         3
3         1Label01 SN007         3
3         1Label01 SN008         13
3         1Label01 SN009         13
3         1Label01 SN0010         13----------------------------------------------------
2         2Printer01 SN001         3
2         2Printer01 SN001         4
2         2Printer01 SN001         3
2         2Printer01 SN002         9
2         2Printer01 SN002         3
2         2Printer01 SN003         3
2         2Printer01 SN004         3
2         2Printer01 SN005         33         2Printer01 SN006         3
3         2Printer01 SN007         3
3         2Printer01 SN008         3
3         2Printer01 SN009         3
3         2Printer01 SN0010         3
3         2Printer01 SN0010         4
3         2Printer01 SN0010         9
3         2Printer01 SN0010         10
以上表格说明: 
第一列：出现过的orders_id
第二列：每个orders_id下，不同的stepc_bc
第三列：每个order_id下会有多个un_barcode, 该un_barcode会经过不同的steps_bc 
第四列：每个un_barcode经过steps_bc时的状态统计结果如下:
orders_id       steps_bc       un_ok      un_nok      un_total 
--------------- -------------- ---------- ----------  ----------
2             1Label01       5          0           5
2             2Printer01     3          2           53             1Label01       5          0           5
3             2Printer01     4          1           5以上统计结果表格说明: 
第一列：出现过的orders_id
第二列：每个orders_id下，不同的stepc_bc
第三列：凡是状态只为3或者13的，都统计在un_ok里面，相同orders_id和相同steps_bc 下重复的un_barcode只统计一次
第四列：只要状态曾经不为3或13，都统计在un_nok里面，相同orders_id和相同steps_bc 下重复的un_barcode只统计一次，如果重复的un_barcode的状态同时为多个非3或13的数字，也只统计一次求教高手！！

Answer 1

declare @t table(orders_id int,steps_bc varchar(18),un_barcode varchar(18),unit_status_id int)
insert into @t select 2,'1Label01  ','SN001 ',13 
insert into @t select 2,'1Label01  ','SN002 ',13 
insert into @t select 2,'1Label01  ','SN003 ',13 
insert into @t select 2,'1Label01  ','SN004 ',3 
insert into @t select 2,'1Label01  ','SN005 ',13 
insert into @t select 2,'1Label01  ','SN004 ',3 
insert into @t select 2,'1Label01  ','SN005 ',13 
insert into @t select 3,'1Label01  ','SN006 ',3 
insert into @t select 3,'1Label01  ','SN006 ',3 
insert into @t select 3,'1Label01  ','SN006 ',3 
insert into @t select 3,'1Label01  ','SN007 ',3 
insert into @t select 3,'1Label01  ','SN008 ',13 
insert into @t select 3,'1Label01  ','SN009 ',13 
insert into @t select 3,'1Label01  ','SN0010', 13 
insert into @t select 2,'2Printer01','SN001 ',3 
insert into @t select 2,'2Printer01','SN001 ',4 
insert into @t select 2,'2Printer01','SN001 ',3 
insert into @t select 2,'2Printer01','SN002 ',9 
insert into @t select 2,'2Printer01','SN002 ',3 
insert into @t select 2,'2Printer01','SN003 ',3 
insert into @t select 2,'2Printer01','SN004 ',3 
insert into @t select 2,'2Printer01','SN005 ',3 
insert into @t select 3,'2Printer01','SN006 ',3 
insert into @t select 3,'2Printer01','SN007 ',3 
insert into @t select 3,'2Printer01','SN008 ',3 
insert into @t select 3,'2Printer01','SN009 ',3 
insert into @t select 3,'2Printer01','SN0010', 3 
insert into @t select 3,'2Printer01','SN0010', 4 
insert into @t select 3,'2Printer01','SN0010', 9 
insert into @t select 3,'2Printer01','SN0010', 10 select
    orders_id,steps_bc,
    un_ok   =sum(case when unit_status_id     in(3,13) then 1 else 0 end),
    un_nok  =sum(case when unit_status_id not in(3,13) then 1 else 0 end),
    un_total=count(1)
from
    @t
group by
    orders_id,steps_bc/*
orders_id   steps_bc           un_ok       un_nok      un_total    
----------- ------------------ ----------- ----------- ----------- 
2           1Label01           7           0           7
3           1Label01           7           0           7
2           2Printer01         6           2           8
3           2Printer01         5           3           8
*/

Answer 2

declare @t table(orders_id int,steps_bc varchar(18),un_barcode varchar(18),unit_status_id int)
insert into @t select 2,'1Label01  ','SN001 ',13 
insert into @t select 2,'1Label01  ','SN002 ',13 
insert into @t select 2,'1Label01  ','SN003 ',13 
insert into @t select 2,'1Label01  ','SN004 ',3 
insert into @t select 2,'1Label01  ','SN005 ',13 
insert into @t select 2,'1Label01  ','SN004 ',3 
insert into @t select 2,'1Label01  ','SN005 ',13 
insert into @t select 3,'1Label01  ','SN006 ',3 
insert into @t select 3,'1Label01  ','SN006 ',3 
insert into @t select 3,'1Label01  ','SN006 ',3 
insert into @t select 3,'1Label01  ','SN007 ',3 
insert into @t select 3,'1Label01  ','SN008 ',13 
insert into @t select 3,'1Label01  ','SN009 ',13 
insert into @t select 3,'1Label01  ','SN0010', 13 
insert into @t select 2,'2Printer01','SN001 ',3 
insert into @t select 2,'2Printer01','SN001 ',4 
insert into @t select 2,'2Printer01','SN001 ',3 
insert into @t select 2,'2Printer01','SN002 ',9 
insert into @t select 2,'2Printer01','SN002 ',3 
insert into @t select 2,'2Printer01','SN003 ',3 
insert into @t select 2,'2Printer01','SN004 ',3 
insert into @t select 2,'2Printer01','SN005 ',3 
insert into @t select 3,'2Printer01','SN006 ',3 
insert into @t select 3,'2Printer01','SN007 ',3 
insert into @t select 3,'2Printer01','SN008 ',3 
insert into @t select 3,'2Printer01','SN009 ',3 
insert into @t select 3,'2Printer01','SN0010', 3 
insert into @t select 3,'2Printer01','SN0010', 4 
insert into @t select 3,'2Printer01','SN0010', 9 
insert into @t select 3,'2Printer01','SN0010', 10 select
    orders_id,steps_bc,
    un_ok   =sum(case flag when 0 then 1 else 0 end),
    un_nok  =sum(case flag when 1 then 1 else 0 end),
    un_total=count(1)
from
    (select 
        orders_id,steps_bc,un_barcode,min(unit_status_id) as unit_status_id,
        case when exists(select 
                             1 
                         from 
                             @t 
                         where 
                             orders_id=a.orders_id and steps_bc=a.steps_bc 
                             and 
                             un_barcode=a.un_barcode and unit_status_id not in(3,13)) 
          then 1 else 0 
        end as flag
     from @t a
     group by orders_id,steps_bc,un_barcode) t
group by
    orders_id,steps_bc
order by
    orders_id,steps_bc/*
orders_id   steps_bc           un_ok       un_nok      un_total    
----------- ------------------ ----------- ----------- ----------- 
2           1Label01           5           0           5
2           2Printer01         3           2           5
3           1Label01           5           0           5
3           2Printer01         4           1           5
*/

Answer 3

还需要更新一下问题：
orders_id      steps_bc      un_barcode      unit_status_id     Arrivetime      DepartureTime
------------- -------------  ------------  -----------------  ----------------- -----------------  
2             1Label01       SN001         13 
2             1Label01       SN002         13 
2             1Label01       SN003         13 
2             1Label01       SN004         3 
2             1Label01       SN005         13 
2             1Label01       SN004         3 
2             1Label01       SN005         13 3             1Label01       SN006         3 
3             1Label01       SN006         3 
3             1Label01       SN006         3 
3             1Label01       SN007         3 
3             1Label01       SN008         13 
3             1Label01       SN009         13 
3             1Label01       SN0010         13 ---------------------------------------------------- 
2             2Printer01       SN001         3 
2             2Printer01       SN001         4 
2             2Printer01       SN001         3 
2             2Printer01       SN002         9 
2             2Printer01       SN002         3 
2             2Printer01       SN003         3 
2             2Printer01       SN004         3 
2             2Printer01       SN005         3 3             2Printer01       SN006         3 
3             2Printer01       SN007         3 
3             2Printer01       SN008         3 
3             2Printer01       SN009         3 
3             2Printer01       SN0010         3 
3             2Printer01       SN0010         4 
3             2Printer01       SN0010         9 
3             2Printer01       SN0010         10 再加上两列arrivetime和departuretime ，
orders_id      steps_bc      un_ok      un_nok      un_total  Arrivetime    departuretime 
--------------- -------------- ---------- ----------  ---------- 
2             1Label01      5          0          5 
2             2Printer01    3          2          5 3             1Label01      5          0          5 
3             2Printer01    4          1          5 如果un_barcode重复，取最小的Arrivetime，而取最大的departuretime，另外unit_status_id 还有可能取值为1或者2，均为un_nok，所以不能取unit_status_id的最小值为基准
多谢,多谢！

Answer 4

;with t as
( select row_number() over(partition by orders_id ,steps_bc order by  orders_id ,steps_bc) id,*
  from table1)
select
    orders_id,steps_bc,
    un_ok   =sum(case when unit_status_id     in(3,13) then 1 else 0 end),
    un_nok  =sum(case when unit_status_id not in(3,13) then 1 else 0 end),
 
from
    table1
where id=1
乱写

Answer 5

-->生成测试数据
declare @tab table
(orders_id int, steps_bc  varchar(32) ,un_barcode varchar(32) ,unit_status_id int )insert into @Tab select 2,         '1Label01'  ,'SN001',         13
insert into @Tab select 2,         '1Label01'  ,'SN002',         13
insert into @Tab select 2,         '1Label01'  ,'SN003',         13
insert into @Tab select 2,         '1Label01'  ,'SN004',         3
insert into @Tab select 2,         '1Label01'  ,'SN005',         13
insert into @Tab select 2,         '1Label01'  ,'SN004',         3
insert into @Tab select 2,         '1Label01'  ,'SN005',         13
insert into @Tab select 3,         '1Label01'  ,'SN006',         3
insert into @Tab select 3,         '1Label01'  ,'SN006',         3
insert into @Tab select 3,         '1Label01'  ,'SN006',         3
insert into @Tab select 3,         '1Label01'  ,'SN007',         3
insert into @Tab select 3,         '1Label01'  ,'SN008',         13
insert into @Tab select 3,         '1Label01'  ,'SN009',         13
insert into @Tab select 3,         '1Label01'  ,'SN0010',        13
insert into @Tab select 2,         '2Printer01'   ,'SN001',         3
insert into @Tab select 2,         '2Printer01'   ,'SN001',         4
insert into @Tab select 2,         '2Printer01'   ,'SN001',         3
insert into @Tab select 2,         '2Printer01'   ,'SN002',         9
insert into @Tab select 2,         '2Printer01'   ,'SN002',         3
insert into @Tab select 2,         '2Printer01'   ,'SN003',         3
insert into @Tab select 2,         '2Printer01'   ,'SN004',         3
insert into @Tab select 2,         '2Printer01'   ,'SN005',         3
insert into @Tab select 3,         '2Printer01'   ,'SN006',         3
insert into @Tab select 3,         '2Printer01'   ,'SN007',         3
insert into @Tab select 3,         '2Printer01'   ,'SN008',         3
insert into @Tab select 3,         '2Printer01'   ,'SN009',         3
insert into @Tab select 3,         '2Printer01'   ,'SN0010',        3
insert into @Tab select 3,         '2Printer01'   ,'SN0010',        4
insert into @Tab select 3,         '2Printer01'   ,'SN0010',        9
insert into @Tab select 3,         '2Printer01'   ,'SN0010',        10 
insert into @Tab select 3,         '2Printer01'   ,'SN0010',        10 -->生成测试环境
;WITH Args1 AS
(
SELECT DISTINCT *,ROW_NUMBER() OVER (ORDER BY orders_id) OrderID FROM @tab A 
 WHERE unit_status_id IN (13,3)
 UNION ALL 
 SELECT * FROM (
 SELECT *,ROW_NUMBER() OVER 
 (partition BY orders_id,steps_bc,un_barcode  ORDER BY orders_id ,steps_bc,un_barcode ) 
 OrderID 
 FROM @tab A 
 WHERE A.unit_status_id NOT IN (13,3) ) T
 WHERE T.OrderID =1
)
SELECT orders_id, 
steps_bc ,
    un_ok   =sum(case when unit_status_id     in(3,13) then 1 else 0 end),
    un_nok  =sum(case when unit_status_id not in(3,13) then 1 else 0 end),
    un_total=count(1) FROM Args1
 GROUP BY orders_id,steps_bc 
-->测试结果
/*
orders_id   steps_bc                         un_ok       un_nok      un_total
----------- -------------------------------- ----------- ----------- -----------
2           1Label01                         7           0           7
3           1Label01                         7           0           7
2           2Printer01                       6           2           8
3           2Printer01                       5           1           6
*/

Answer 6

-->生成测试数据
declare @tab table
(orders_id int, steps_bc  varchar(32) ,un_barcode varchar(32) ,unit_status_id INT,
Arrivetime DATETIME,
DepartureTime DATETIME )insert into @Tab select 2            ,'1Label01',       'SN001'      ,  13    ,'2009-04-11 13:09:12.543','2009-04-5 13:09:12.543'
insert into @Tab select 2            ,'1Label01',       'SN002'      ,  13    ,'2009-04-12 13:09:12.543','2009-04-1 13:09:12.543'
insert into @Tab select 2            ,'1Label01',       'SN003'      ,  13    ,'2009-04-10 13:09:12.543','2009-04-2 13:09:12.543'
insert into @Tab select 2            ,'1Label01',       'SN004'      ,  3     ,'2009-04-1 13:09:12.543','2009-04-3 13:09:12.543'
insert into @Tab select 2            ,'1Label01',       'SN005'      ,  13    ,'2009-04-2 13:09:12.543','2009-04-4 13:09:12.543'
insert into @Tab select 2            ,'1Label01',       'SN004'      ,  3     ,'2009-04-3 13:09:12.543','2009-04-8 13:09:12.543'
insert into @Tab select 2            ,'1Label01',       'SN005'      ,  13    ,null,null
insert into @Tab select 3            ,'1Label01',       'SN006'      ,  3     ,null,null
insert into @Tab select 3            ,'1Label01',       'SN006'      ,  3     ,null,null
insert into @Tab select 3            ,'1Label01',       'SN006'      ,  3     ,null,null
insert into @Tab select 3            ,'1Label01',       'SN007'      ,  3     ,null,null
insert into @Tab select 3            ,'1Label01',       'SN008'      ,  13    ,null,null
insert into @Tab select 3            ,'1Label01',       'SN009'      ,  13    ,null,null
insert into @Tab select 3            ,'1Label01',       'SN0010'     ,   13   ,null,null
insert into @Tab select 2            ,'2Printer01',      ' SN001'      ,  3     ,null,null
insert into @Tab select 2            ,'2Printer01',      ' SN001'      ,  4     ,null,null
insert into @Tab select 2            ,'2Printer01',      ' SN001'      ,  3     ,null,null
insert into @Tab select 2            ,'2Printer01',      ' SN002'      ,  9     ,null,null
insert into @Tab select 2            ,'2Printer01',      ' SN002'      ,  3     ,null,null
insert into @Tab select 2            ,'2Printer01',      ' SN003'      ,  3     ,null,null
insert into @Tab select 2            ,'2Printer01',      ' SN004'      ,  3     ,null,null
insert into @Tab select 2            ,'2Printer01',      ' SN005'      ,  3     ,null,null
insert into @Tab select 3            ,'2Printer01',      ' SN006'      ,  3     ,null,null
insert into @Tab select 3            ,'2Printer01',      ' SN007'      ,  3     ,null,null
insert into @Tab select 3            ,'2Printer01',      ' SN008'      ,  3     ,null,null
insert into @Tab select 3            ,'2Printer01',      ' SN009'      ,  3     ,null,null
insert into @Tab select 3            ,'2Printer01',      ' SN0010'     ,   3    ,null,null
insert into @Tab select 3            ,'2Printer01',      ' SN0010'     ,   4    ,null,null-->生成测试环境
;WITH Args1 AS
(
SELECT DISTINCT *,ROW_NUMBER() OVER (ORDER BY orders_id) OrderID FROM @tab A 
 WHERE unit_status_id IN (13,3)
 UNION ALL 
 SELECT * FROM (
 SELECT *,ROW_NUMBER() OVER 
 (partition BY orders_id,steps_bc,un_barcode  ORDER BY orders_id ,steps_bc,un_barcode ) 
 OrderID 
 FROM @tab A 
 WHERE A.unit_status_id NOT IN (13,3) ) T
 WHERE T.OrderID =1
)
SELECT orders_id, 
steps_bc ,
    un_ok   =sum(case when unit_status_id     in(3,13) then 1 else 0 end),
    un_nok  =sum(case when unit_status_id not in(3,13) then 1 else 0 end),
    un_total=count(1),
    MIN(Arrivetime) Arrivetime,
    max(DepartureTime)  DepartureTime FROM Args1
 GROUP BY orders_id,steps_bc -->测试结果
/*
orders_id   steps_bc      un_ok    un_nok un_total    Arrivetime       DepartureTime
----------- -------------------------------- ----------- ----------- ----------- ----------------------- 
2           1Label01         7      0           7           2009-04-01 13:09:12.543 2009-04-08 13:09:12.543
3           1Label01         7      0           7           NULL                    NULL
2           2Printer01       6      2           8           NULL                    NULL
3           2Printer01       5      1           6           NULL                    NULL
警告: 聚合或其他 SET 操作消除了空值。(4 行受影响)
*/

Answer 7

select  orders_id,steps_bc
,sum(flg) un_ok,count(1)-sum(flg) un_nok,count(1) un_total
,min(Arrivetime) Arrivetime,max(departuretime) departuretime
from
(
select  orders_id,steps_bc,un_barcode
,case when exists(select 1 from tb 
where orders_id=t.orders_id and steps_bc=t.steps_bc and un_barcode=t.un_barcode 
and unit_status_id not in(3,13))
  then 0 else 1 
 end flg
,min(Arrivetime) Arrivetime
,max(departuretime) departuretime
from tb t
group by orders_id,steps_bc,un_barcode
) a
group by orders_id,steps_bc
order by orders_id,steps_bc
(应LZ请求)模仿钻钻的写法，狗尾续貂一下。

Answer 8

,min(Arrivetime) Arrivetime
,max(departuretime) departuretime？ 假如是 7和8呢 ，而数据是1 到15之间，也就是说比7，8 有小的也有更大的，怎么办 ？

Answer 9

Sql = " select orders_id,steps_bc,sum(iif(un_barcode>1,0,1)),count(un_barcode)-sum(iif(un_barcode>1,0,1)), " _
    & " count(un_barcode) from (select orders_id,steps_bc,count(un_barcode) as un_barcode from " _
    & " (select distinct orders_id,steps_bc,un_barcode,unit_status_id from tb1)  group by " _
    & " orders_id,steps_bc,un_barcode) group by orders_id,steps_bc "测试结果：
orders_id steps_bc un_ok un_nok un_total
2 1Label01 5 0 5
2 2Printer01 3 2 5
3 1Label01 5 0 5
3 2Printer01 4 1 5应该算通过了吧

Answer 10

好象上面没有用到 count(iif(unit_status_id in(3,13), 1,0))    中的 in 和 not in 怎么答案也对了呢说明还是错误的。