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
Statistics of the same order, and the maximum number of consecutive results are as follows:
Unqualified number last time start time
A 4,3,4,6 (note: 1) the 2014-11-6 2014-11-1
3 B (note: 8,9,10) 2014-11-10 2014-11-8
CodePudding user response:
This problem can't solve with a simple query,The train of thought to solve the problem of statistics: first qualifying records as boundary conditions, and then statistical unqualified records,
set nocount on;
Declare @ order table (AutoId integer, order number char (1), unqualified bit, time 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 integer (id);
Insert into @ ids select AutoId from @ order where unqualified=0;
Insert into @ ids values (@ minid), (@ maxid);
Declare @ group table (startid integer, endid integer);
Insert into @ group
The select startid=id, endid=(select min (id) from @ ids where id & gt; Anderson, d)
The from @ ids a where id & lt; @ maxid;
With t_cnt as
(select a.s tartid, a.e ndid, b. the order number, unqualified number=the count (*)
The from @ group a, @ order b
Where b. unqualified=1 and b.A utoId between a.s tartid and a.e ndid
Group by a.s tartid, a.e ndid, b. order number),
T_max_cnt as
(select * from t_cnt a where unqualified number=(select Max (unqualified number) from t_cnt where startid=a.s tartid))
Order number, select a. b. unqualified number, the last time=Max (time), time=min (time)
The from @ the order a, t_max_cnt b
The where (a.A utoId between b.s tartid and b.e ndid) and a. order number=b. order number and a. unqualified=1
Order no., group by a. b. unqualified number
The order by the start time;
CodePudding user response:
If you must write a query, you can write:set nocount on;
Declare @ order table (AutoId integer, order number char (1), unqualified bit, time 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 unqualified=0
The union
Select the from @ order min (AutoId) - 1
The union
The select Max (AutoId) + 1 from @ order),
T_group as
(select startid=id, endid=(select min (id) from t_ids where id & gt; Anderson, d)
The from t_ids a where id & lt; (select Max (AutoId) + 1 from @ order)),
T_cnt as
(select a.s tartid, a.e ndid, b. the order number, unqualified number=the count (*)
The from t_group a, @ order b
Where b. unqualified=1 and b.A utoId between a.s tartid and a.e ndid
Group by a.s tartid, a.e ndid, b. order number),
T_max_cnt as
(select * from t_cnt a where unqualified number=(select Max (unqualified number) from t_cnt where startid=a.s tartid))
Order number, select a. b. unqualified number, the last time=Max (time), time=min (time)
The from @ the order a, t_max_cnt b
The where (a.A utoId between b.s tartid and b.e ndid) and a. order number=b. order number and a. unqualified=1
Order no., group by a. b. unqualified number
The order by the start time;