User id fee monthly user name line degree of water
this month this month1001 202009 Huang Zhongbao 22 5
1001 202008 Huang Zhongbao 19
1001 202007 Huang Zhongbao 14 6
1001 202006 Huang Zhongbao 10 7
3 1002 202009 xiao-yun wu 421
3 1002 202008 xiao-yun wu 321
3 1002 202007 xiao-yun wu 221
1003 202009 zhi-qiang ma 151 23
141 21 1003 202008 zhi-qiang ma
1003 202007 zhi-qiang ma 121 23
By the SQL statement check out the following results in t,
Conditions will query the meaning of 3 consecutive months "water consumption" of the same user, the latest month is September, so just display data in September,
User id fee monthly user name line degree of water
this month this month3 1002 202009 xiao-yun wu 421
CodePudding user response:
The CREATE TABLE # T
(
User id VARCHAR (10),
Fee monthly VARCHAR (10),
User name VARCHAR (10),
Line c INT this month,
This month water INT
)
INSERT INTO # T VALUES (' 1001 ', '202009', 'Huang Zhongbao, 22, 5)
INSERT INTO # T VALUES (' 1001 ', '202008', 'Huang Zhongbao, 19, 5)
INSERT INTO # T VALUES (' 1001 ', '202007', 'Huang Zhongbao, 14, 6)
INSERT INTO # T VALUES (' 1001 ', '202006', 'Huang Zhongbao, 10, 7)
INSERT INTO # T VALUES (' 1002 ', '202009', 'xiao-yun wu, 421, 3)
INSERT INTO # T VALUES (' 1002 ', '202008', 'xiao-yun wu, 321, 3)
INSERT INTO # T VALUES (' 1002 ', '202007', 'xiao-yun wu, 221, 3)
INSERT INTO # T VALUES (' 1003 ', '202009', 'zhi-qiang ma, 151, 23)
INSERT INTO # T VALUES (' 1003 ', '202008', 'zhi-qiang ma, 141, 21)
INSERT INTO # T VALUES (' 1003 ', '202007', 'zhi-qiang ma, 121, 23)
;
WITH ct
AS
(
SELECT *, ROW_NUMBER () OVER (the ORDER BY the user number, charge for years) AS id FROM # T
)
SELECT the user number, b. b. charges, user name, b. b. line this month, b. this month water
The FROM ct b INNER JOIN
(
The SELECT MAX (id) AS id FROM
(
SELECT *, id - ROW_NUMBER () OVER (PARTITION BY water ORDER this month BY the user number) AS y FROM ct
) a
GROUP BY HAVING y COUNT (1) & gt;=3
C) ON c.i d=b.i d
DROP TABLE # T
CodePudding user response:
The SELECT c. *
The FROM c # T
INNER JOIN
(
SELECT a. user number, MAX (a. charge years) AS charge monthly
The FROM # t a WHERE clause (SELECT COUNT (1) the FROM # t WHERE user id=a. user number AND charge monthly & lt; Charge for years AND this month water==a. a. this month water) & gt;=3
GROUP BY a. user id
) b ON user id=c. b. user number AND b. charge monthly=c. years
CodePudding user response:
The SELECT c. *
The FROM c # T
INNER JOIN
(
SELECT a. user number, MAX (a. charge years) AS charge monthly
The FROM
(
SELECT *,
LEAD (water, this month 1) OVER (PARTITION BY user id ORDER BY charging monthly desc) AS b,
LEAD (water this month, 2) OVER (PARTITION BY user id ORDER BY charging monthly desc) AS c
The FROM # T
) a
WHERE a. Water=a. this month AND a.=a.c
GROUP BY a. user id
) b ON user id=c. b. user number AND b. charge monthly=c. years
CodePudding user response:
The create table table t
(user id int, collect fees monthly varchar (10), the user name varchar (10), line degree int this month, this month water int)
Insert into table t
Select 1001, '202009', 'Huang Zhongbao, 22, 5 union all
Select 1001, '202008', 'Huang Zhongbao, 19, 5 union all
Select 1001, '202007', 'Huang Zhongbao, 14, 6 union all
Select 1001, '202006', 'Huang Zhongbao, 10, 7 union all
Select 1002, '202009', 'xiao-yun wu, 421, 3 union all
Select 1002, '202008', 'xiao-yun wu, 321, 3 union all
Select 1002, '202007', 'xiao-yun wu, 221, 3 union all
Select 1003, '202009', 'zhi-qiang ma' 151, union all
Select 1003, '202008', 'zhi-qiang ma' 141, union all
Select 1003, '202007', 'zhi-qiang ma, 121, 23.
With t as
(select *, rn=row_number () over (partition by user id order by charging monthly desc)
From table t)
Select the user number, charge for years, user name, line of this month, this month water
The from t
Where an rn=1
And the user number in (select the user number
The from t
Where rn<=3
Group by user id
Having a count (1)=3 and count (distinct water this month)=1)
/*
User id fee monthly user name line degree of water
this month this month-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
3 1002 202009 xiao-yun wu 421
Line (1) affected
*/