AutoId 訂單編號 不合格 時間
1 A 1 2014-11-1
2 B 1 2014-11-2
3 A 1 2014-11-3
4 A 1 2014-11-4
5 B 1 2014-11-5
6 A 1 2014-11-6
7 A 0 2014-11-7
8 B 1 2014-11-8
9 B 1 2014-11-9
10 B 1 2014-11-10
統計 同一訂單的連續不合格的最大個數，結果如下：
編號 不合格 最后時間 開始時間
A 4 (說明：1,3,4,6) 2014-11-6 2014-11-1
B 3 (說明：8,9,10) 2014-11-10 2014-11-8
uj5u.com熱心網友回復：
這個問題無法用一個簡單查詢陳述句解決。解決問題的思路：先統計合格的記錄作為邊界條件，再統計不合格的記錄。
set nocount on;
declare @order table (AutoId integer, 訂單編號 char(1), 不合格 bit, 時間 date);
insert into @order values
(1, 'A', 1, '2014-11-1'),
(2, 'B', 1, '2014-11-2'),
(3, 'A', 1, '2014-11-3'),
(4, 'A', 1, '2014-11-4'),
(5, 'B', 1, '2014-11-5'),
(6, 'A', 1, '2014-11-6'),
(7, 'A', 0, '2014-11-7'),
(8, 'B', 1, '2014-11-8'),
(9, 'B', 1, '2014-11-9'),
(10,'B', 1, '2014-11-10');
declare @minid integer = (select min(AutoId) from @order) - 1;
declare @maxid integer = (select max(AutoId) from @order) + 1;
declare @ids table (id integer);
insert into @ids select AutoId from @order where 不合格=0;
insert into @ids values (@minid), (@maxid);
declare @group table (startid integer, endid integer);
insert into @group
select startid=id, endid = (select min(id) from @ids where id > a.id)
from @ids a where id < @maxid;
with t_cnt as
(select a.startid, a.endid, b.訂單編號, 不合格數量 = count(*)
from @group a, @order b
where b.不合格=1 and b.AutoId between a.startid and a.endid
group by a.startid, a.endid, b.訂單編號),
t_max_cnt as
(select * from t_cnt a where 不合格數量 = (select max(不合格數量) from t_cnt where startid=a.startid))
select a.訂單編號, b.不合格數量, 最后時間=max(時間), 開始時間=min(時間)
from @order a, t_max_cnt b
where (a.AutoId between b.startid and b.endid) and a.訂單編號=b.訂單編號 and a.不合格 = 1
group by a.訂單編號, b.不合格數量
order by 開始時間;
uj5u.com熱心網友回復：
如果一定要寫成一個查詢陳述句，可以這樣寫：set nocount on;
declare @order table (AutoId integer, 訂單編號 char(1), 不合格 bit, 時間 date);
insert into @order values
(1, 'A', 1, '2014-11-1'),
(2, 'B', 1, '2014-11-2'),
(3, 'A', 1, '2014-11-3'),
(4, 'A', 1, '2014-11-4'),
(5, 'B', 1, '2014-11-5'),
(6, 'A', 1, '2014-11-6'),
(7, 'A', 0, '2014-11-7'),
(8, 'B', 1, '2014-11-8'),
(9, 'B', 1, '2014-11-9'),
(10,'B', 1, '2014-11-10');
------------------------------------------------------------------
with
t_ids as
(select id=AutoId from @order where 不合格=0
union
select min(AutoId)-1 from @order
union
select max(AutoId)+1 from @order),
t_group as
(select startid=id, endid = (select min(id) from t_ids where id > a.id)
from t_ids a where id < (select max(AutoId)+1 from @order)),
t_cnt as
(select a.startid, a.endid, b.訂單編號, 不合格數量 = count(*)
from t_group a, @order b
where b.不合格=1 and b.AutoId between a.startid and a.endid
group by a.startid, a.endid, b.訂單編號),
t_max_cnt as
(select * from t_cnt a where 不合格數量 = (select max(不合格數量) from t_cnt where startid=a.startid))
select a.訂單編號, b.不合格數量, 最后時間=max(時間), 開始時間=min(時間)
from @order a, t_max_cnt b
where (a.AutoId between b.startid and b.endid) and a.訂單編號=b.訂單編號 and a.不合格 = 1
group by a.訂單編號, b.不合格數量
order by 開始時間;
轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/9601.html
標籤：數據庫相關
上一篇：程式開發 新人請教
下一篇：錯誤使用 network/train (line 340)Inputs and targets have different numbers of samp