sql 如何查询连续相同记录

qgcsoft 2009-07-24 03:49:33

如下表:

用户号 用户名称 污水费计费度 计费月份 水费金额
-----------------------------------------------------
000001 AAAAAAAAAAA 1.00 2009-01-01 120.00
000002 BBBBBBBBBBB 1.11 2009-01-01 122.00
000003 CCCCCCCCCCC 4.00 2009-01-01 812.00
000004 DDDDDDDDDDD 6.45 2009-01-01 822.00
000001 AAAAAAAAAAA 1.10 2009-02-01 20.00
000002 BBBBBBBBBBB 1.31 2009-02-01 22.00
000003 CCCCCCCCCCC 4.40 2009-02-01 82.00
000004 DDDDDDDDDDD 2.30 2009-02-01 23.00
000001 AAAAAAAAAAA 1.00 2009-03-01 120.00
000002 BBBBBBBBBBB 1.11 2009-03-01 122.00
000003 CCCCCCCCCCC 4.00 2009-03-01 812.00
000004 DDDDDDDDDDD 6.45 2009-03-01 212.00
000001 AAAAAAAAAAA 1.00 2009-04-01 120.00
000002 BBBBBBBBBBB 1.11 2009-04-01 122.00
000003 CCCCCCCCCCC 4.00 2009-04-01 812.00
000004 DDDDDDDDDDD 6.45 2009-04-01 822.00
000001 AAAAAAAAAAA 1.00 2009-05-01 120.00
000002 BBBBBBBBBBB 1.11 2009-05-01 122.00
000003 CCCCCCCCCCC 4.00 2009-05-01 812.00
000004 DDDDDDDDDDD 6.45 2009-05-01 820.00

问题:如何查询相同“用户号”和“污水费计费度”并且连续“计费月份”的记录。

就是如何得到下面记录:

用户号 用户名称 污水费计费度 计费月份 水费金额
-----------------------------------------------------
000001 AAAAAAAAAAA 1.00 2009-03-01 120.00
000001 AAAAAAAAAAA 1.00 2009-04-01 120.00
000001 AAAAAAAAAAA 1.00 2009-05-01 120.00
000002 BBBBBBBBBBB 1.11 2009-03-01 122.00
000002 BBBBBBBBBBB 1.11 2009-04-01 122.00
000002 BBBBBBBBBBB 1.11 2009-05-01 122.00
000003 CCCCCCCCCCC 4.00 2009-03-01 812.00
000003 CCCCCCCCCCC 4.00 2009-04-01 812.00
000003 CCCCCCCCCCC 4.00 2009-05-01 812.00
000004 DDDDDDDDDDD 6.45 2009-03-01 212.00
000004 DDDDDDDDDDD 6.45 2009-04-01 822.00
000004 DDDDDDDDDDD 6.45 2009-05-01 820.00
...全文
1389 19 打赏 收藏 转发到动态 举报
写回复
用AI写文章
19 条回复
切换为时间正序
请发表友善的回复…
发表回复
devilidea 2009-07-27
  • 打赏
  • 举报
 回复 
select * from tb a where 

not exists(

select * from tb 

where

abs(datepart(mm,a.[计费月份]))=2 

or

abs(datepart(mm,a.[计费月份]))=1) order by 用户名称,计费月份
devilidea 2009-07-27
  • 打赏
  • 举报
 回复 
if object_id(N'[tb]',N'U') is not null drop table [tb]

create table [tb]([用户号] varchar(6),[用户名称] varchar(11),[污水费计费度] numeric(3,2),[计费月份] datetime,[水费金额] numeric(5,2))

insert [tb]

select '000001','AAAAAAAAAAA',1.00,'2009-01-01',120.00 union all

select '000002','BBBBBBBBBBB',1.11,'2009-01-01',122.00 union all

select '000003','CCCCCCCCCCC',4.00,'2009-01-01',812.00 union all

select '000004','DDDDDDDDDDD',6.45,'2009-01-01',822.00 union all

select '000001','AAAAAAAAAAA',1.10,'2009-02-01',20.00 union all

select '000002','BBBBBBBBBBB',1.31,'2009-02-01',22.00 union all

select '000003','CCCCCCCCCCC',4.40,'2009-02-01',82.00 union all

select '000004','DDDDDDDDDDD',2.30,'2009-02-01',23.00 union all

select '000001','AAAAAAAAAAA',1.00,'2009-03-01',120.00 union all

select '000002','BBBBBBBBBBB',1.11,'2009-03-01',122.00 union all

select '000003','CCCCCCCCCCC',4.00,'2009-03-01',812.00 union all

select '000004','DDDDDDDDDDD',6.45,'2009-03-01',212.00 union all

select '000001','AAAAAAAAAAA',1.00,'2009-04-01',120.00 union all

select '000002','BBBBBBBBBBB',1.11,'2009-04-01',122.00 union all

select '000003','CCCCCCCCCCC',4.00,'2009-04-01',812.00 union all

select '000004','DDDDDDDDDDD',6.45,'2009-04-01',822.00 union all

select '000001','AAAAAAAAAAA',1.00,'2009-05-01',120.00 union all

select '000002','BBBBBBBBBBB',1.11,'2009-05-01',122.00 union all

select '000003','CCCCCCCCCCC',4.00,'2009-05-01',812.00 union all

select '000004','DDDDDDDDDDD',6.45,'2009-05-01',820.00



select * from tb where [计费月份]<>'2009-02-01' and [计费月份]<>'2009-01-01' order by [污水费计费度],[计费月份]

/*

用户号      用户名称  污水费计费度    计费月份    水费金额 

----------------------------------------------------- 

    000001    AAAAAAAAAAA    1.00      2009-03-01  120.00 

    000001    AAAAAAAAAAA    1.00      2009-04-01  120.00 

    000001    AAAAAAAAAAA    1.00      2009-05-01  120.00 

    000002    BBBBBBBBBBB    1.11      2009-03-01  122.00 

    000002    BBBBBBBBBBB    1.11      2009-04-01  122.00 

    000002    BBBBBBBBBBB    1.11      2009-05-01  122.00 

    000003    CCCCCCCCCCC    4.00      2009-03-01  812.00 

    000003    CCCCCCCCCCC    4.00      2009-04-01  812.00 

    000003    CCCCCCCCCCC    4.00      2009-05-01  812.00 

    000004    DDDDDDDDDDD    6.45      2009-03-01  212.00 

    000004    DDDDDDDDDDD    6.45      2009-04-01  822.00 

    000004    DDDDDDDDDDD    6.45      2009-05-01  820.00 



*/
csdyyr 2009-07-24
  • 打赏
  • 举报
 回复 
DECLARE @TB TABLE([用户号] VARCHAR(6), [用户名称] VARCHAR(11), [污水费计费度] DECIMAL(18,2), [计费月份] DATETIME, [水费金额] DECIMAL(18,2))

INSERT @TB 

SELECT '000001', 'AAAAAAAAAAA', 1.00, '2009-01-01', 120.00 UNION ALL 

SELECT '000002', 'BBBBBBBBBBB', 1.11, '2009-01-01', 122.00 UNION ALL 

SELECT '000003', 'CCCCCCCCCCC', 4.00, '2009-01-01', 812.00 UNION ALL 

SELECT '000004', 'DDDDDDDDDDD', 6.45, '2009-01-01', 822.00 UNION ALL 

SELECT '000001', 'AAAAAAAAAAA', 1.10, '2009-02-01', 20.00 UNION ALL 

SELECT '000002', 'BBBBBBBBBBB', 1.31, '2009-02-01', 22.00 UNION ALL 

SELECT '000003', 'CCCCCCCCCCC', 4.40, '2009-02-01', 82.00 UNION ALL 

SELECT '000004', 'DDDDDDDDDDD', 2.30, '2009-02-01', 23.00 UNION ALL 

SELECT '000001', 'AAAAAAAAAAA', 1.00, '2009-03-01', 120.00 UNION ALL 

SELECT '000002', 'BBBBBBBBBBB', 1.11, '2009-03-01', 122.00 UNION ALL 

SELECT '000003', 'CCCCCCCCCCC', 4.00, '2009-03-01', 812.00 UNION ALL 

SELECT '000004', 'DDDDDDDDDDD', 6.45, '2009-03-01', 212.00 UNION ALL 

SELECT '000001', 'AAAAAAAAAAA', 1.00, '2009-04-01', 120.00 UNION ALL 

SELECT '000002', 'BBBBBBBBBBB', 1.11, '2009-04-01', 122.00 UNION ALL 

SELECT '000003', 'CCCCCCCCCCC', 4.00, '2009-04-01', 812.00 UNION ALL 

SELECT '000004', 'DDDDDDDDDDD', 6.45, '2009-04-01', 822.00 UNION ALL 

SELECT '000004', 'DDDDDDDDDDD', 6.45, '2009-04-01', 822.00 UNION ALL --ADD

SELECT '000001', 'AAAAAAAAAAA', 1.00, '2009-05-01', 120.00 UNION ALL 

SELECT '000002', 'BBBBBBBBBBB', 1.11, '2009-05-01', 122.00 UNION ALL 

SELECT '000003', 'CCCCCCCCCCC', 4.00, '2009-05-01', 812.00 UNION ALL 

SELECT '000004', 'DDDDDDDDDDD', 6.45, '2009-05-01', 820.00





SELECT *

FROM @TB AS T

WHERE EXISTS(

	          SELECT 1 

	          FROM (SELECT T1.用户号,T1.污水费计费度, T1.计费月份

	                        FROM @TB  AS T1

	                        WHERE  EXISTS(SELECT 1 FROM @TB WHERE 用户号=T1.用户号 AND 污水费计费度=T1.污水费计费度 AND 计费月份 BETWEEN T1.计费月份 AND DATEADD(MONTH,2,T1.计费月份)  HAVING COUNT(*)>=3)

	                       ) AS T2

	          WHERE T.用户号=T2.用户号 AND T.污水费计费度=T2.污水费计费度 AND T.计费月份 BETWEEN T2.计费月份 AND DATEADD(MONTH,2,T2.计费月份)

	          )

ORDER BY 用户号,污水费计费度,计费月份

/*

用户号    用户名称        污水费计费度               计费月份                                                   水费金额                 

------ ----------- -------------------- ------------------------------------------------------ -------------------- 

000001 AAAAAAAAAAA 1.00                 2009-03-01 00:00:00.000                                120.00

000001 AAAAAAAAAAA 1.00                 2009-04-01 00:00:00.000                                120.00

000001 AAAAAAAAAAA 1.00                 2009-05-01 00:00:00.000                                120.00

000002 BBBBBBBBBBB 1.11                 2009-03-01 00:00:00.000                                122.00

000002 BBBBBBBBBBB 1.11                 2009-04-01 00:00:00.000                                122.00

000002 BBBBBBBBBBB 1.11                 2009-05-01 00:00:00.000                                122.00

000003 CCCCCCCCCCC 4.00                 2009-03-01 00:00:00.000                                812.00

000003 CCCCCCCCCCC 4.00                 2009-04-01 00:00:00.000                                812.00

000003 CCCCCCCCCCC 4.00                 2009-05-01 00:00:00.000                                812.00

000004 DDDDDDDDDDD 6.45                 2009-03-01 00:00:00.000                                212.00

000004 DDDDDDDDDDD 6.45                 2009-04-01 00:00:00.000                                822.00

000004 DDDDDDDDDDD 6.45                 2009-04-01 00:00:00.000                                822.00

000004 DDDDDDDDDDD 6.45                 2009-05-01 00:00:00.000                                820.00



(13 row(s) affected)

*/
csdyyr 2009-07-24
  • 打赏
  • 举报
 回复 
DECLARE @TB TABLE([用户号] VARCHAR(6), [用户名称] VARCHAR(11), [污水费计费度] DECIMAL(18,2), [计费月份] DATETIME, [水费金额] DECIMAL(18,2))

INSERT @TB 

SELECT '000001', 'AAAAAAAAAAA', 1.00, '2009-01-01', 120.00 UNION ALL 

SELECT '000002', 'BBBBBBBBBBB', 1.11, '2009-01-01', 122.00 UNION ALL 

SELECT '000003', 'CCCCCCCCCCC', 4.00, '2009-01-01', 812.00 UNION ALL 

SELECT '000004', 'DDDDDDDDDDD', 6.45, '2009-01-01', 822.00 UNION ALL 

SELECT '000001', 'AAAAAAAAAAA', 1.10, '2009-02-01', 20.00 UNION ALL 

SELECT '000002', 'BBBBBBBBBBB', 1.31, '2009-02-01', 22.00 UNION ALL 

SELECT '000003', 'CCCCCCCCCCC', 4.40, '2009-02-01', 82.00 UNION ALL 

SELECT '000004', 'DDDDDDDDDDD', 2.30, '2009-02-01', 23.00 UNION ALL 

SELECT '000001', 'AAAAAAAAAAA', 1.00, '2009-03-01', 120.00 UNION ALL 

SELECT '000002', 'BBBBBBBBBBB', 1.11, '2009-03-01', 122.00 UNION ALL 

SELECT '000003', 'CCCCCCCCCCC', 4.00, '2009-03-01', 812.00 UNION ALL 

SELECT '000004', 'DDDDDDDDDDD', 6.45, '2009-03-01', 212.00 UNION ALL 

SELECT '000001', 'AAAAAAAAAAA', 1.00, '2009-04-01', 120.00 UNION ALL 

SELECT '000002', 'BBBBBBBBBBB', 1.11, '2009-04-01', 122.00 UNION ALL 

SELECT '000003', 'CCCCCCCCCCC', 4.00, '2009-04-01', 812.00 UNION ALL 

SELECT '000004', 'DDDDDDDDDDD', 6.45, '2009-04-01', 822.00 UNION ALL 

SELECT '000004', 'DDDDDDDDDDD', 6.45, '2009-04-01', 822.00 UNION ALL --ADD

SELECT '000001', 'AAAAAAAAAAA', 1.00, '2009-05-01', 120.00 UNION ALL 

SELECT '000002', 'BBBBBBBBBBB', 1.11, '2009-05-01', 122.00 UNION ALL 

SELECT '000003', 'CCCCCCCCCCC', 4.00, '2009-05-01', 812.00 UNION ALL 

SELECT '000004', 'DDDDDDDDDDD', 6.45, '2009-05-01', 820.00





SELECT *

FROM @TB AS T

WHERE EXISTS(

	          SELECT 1 

	          FROM (SELECT T1.用户号,T1.污水费计费度, T1.计费月份

	                        FROM @TB  AS T1

	                        WHERE  EXISTS(SELECT 1 FROM @TB WHERE 用户号=T1.用户号 AND 污水费计费度=T1.污水费计费度 AND 计费月份 BETWEEN T1.计费月份 AND DATEADD(MONTH,2,T1.计费月份)  HAVING COUNT(*)>=3)

	                       ) AS T2

	          WHERE T.用户号=T2.用户号 AND T.污水费计费度=T2.污水费计费度 AND T.计费月份 BETWEEN T2.计费月份 AND DATEADD(MONTH,2,T2.计费月份)

	          )

ORDER BY 用户号,污水费计费度,计费月份

/*

用户号    用户名称        污水费计费度               计费月份                                                   水费金额                 

------ ----------- -------------------- ------------------------------------------------------ -------------------- 

000001 AAAAAAAAAAA 1.00                 2009-03-01 00:00:00.000                                120.00

000001 AAAAAAAAAAA 1.00                 2009-04-01 00:00:00.000                                120.00

000001 AAAAAAAAAAA 1.00                 2009-05-01 00:00:00.000                                120.00

000002 BBBBBBBBBBB 1.11                 2009-03-01 00:00:00.000                                122.00

000002 BBBBBBBBBBB 1.11                 2009-04-01 00:00:00.000                                122.00

000002 BBBBBBBBBBB 1.11                 2009-05-01 00:00:00.000                                122.00

000003 CCCCCCCCCCC 4.00                 2009-03-01 00:00:00.000                                812.00

000003 CCCCCCCCCCC 4.00                 2009-04-01 00:00:00.000                                812.00

000003 CCCCCCCCCCC 4.00                 2009-05-01 00:00:00.000                                812.00

000004 DDDDDDDDDDD 6.45                 2009-03-01 00:00:00.000                                212.00

000004 DDDDDDDDDDD 6.45                 2009-04-01 00:00:00.000                                822.00

000004 DDDDDDDDDDD 6.45                 2009-04-01 00:00:00.000                                822.00

000004 DDDDDDDDDDD 6.45                 2009-05-01 00:00:00.000                                820.00



(13 row(s) affected)

*/
csdyyr 2009-07-24
  • 打赏
  • 举报
 回复 
DECLARE @TB TABLE([用户号] VARCHAR(6), [用户名称] VARCHAR(11), [污水费计费度] DECIMAL(18,2), [计费月份] DATETIME, [水费金额] DECIMAL(18,2))

INSERT @TB 

SELECT '000001', 'AAAAAAAAAAA', 1.00, '2009-01-01', 120.00 UNION ALL 

SELECT '000002', 'BBBBBBBBBBB', 1.11, '2009-01-01', 122.00 UNION ALL 

SELECT '000003', 'CCCCCCCCCCC', 4.00, '2009-01-01', 812.00 UNION ALL 

SELECT '000004', 'DDDDDDDDDDD', 6.45, '2009-01-01', 822.00 UNION ALL 

SELECT '000001', 'AAAAAAAAAAA', 1.10, '2009-02-01', 20.00 UNION ALL 

SELECT '000002', 'BBBBBBBBBBB', 1.31, '2009-02-01', 22.00 UNION ALL 

SELECT '000003', 'CCCCCCCCCCC', 4.40, '2009-02-01', 82.00 UNION ALL 

SELECT '000004', 'DDDDDDDDDDD', 2.30, '2009-02-01', 23.00 UNION ALL 

SELECT '000001', 'AAAAAAAAAAA', 1.00, '2009-03-01', 120.00 UNION ALL 

SELECT '000002', 'BBBBBBBBBBB', 1.11, '2009-03-01', 122.00 UNION ALL 

SELECT '000003', 'CCCCCCCCCCC', 4.00, '2009-03-01', 812.00 UNION ALL 

SELECT '000004', 'DDDDDDDDDDD', 6.45, '2009-03-01', 212.00 UNION ALL 

SELECT '000001', 'AAAAAAAAAAA', 1.00, '2009-04-01', 120.00 UNION ALL 

SELECT '000002', 'BBBBBBBBBBB', 1.11, '2009-04-01', 122.00 UNION ALL 

SELECT '000003', 'CCCCCCCCCCC', 4.00, '2009-04-01', 812.00 UNION ALL 

SELECT '000004', 'DDDDDDDDDDD', 6.45, '2009-04-01', 822.00 UNION ALL 

SELECT '000004', 'DDDDDDDDDDD', 6.45, '2009-04-01', 822.00 UNION ALL --ADD

SELECT '000001', 'AAAAAAAAAAA', 1.00, '2009-05-01', 120.00 UNION ALL 

SELECT '000002', 'BBBBBBBBBBB', 1.11, '2009-05-01', 122.00 UNION ALL 

SELECT '000003', 'CCCCCCCCCCC', 4.00, '2009-05-01', 812.00 UNION ALL 

SELECT '000004', 'DDDDDDDDDDD', 6.45, '2009-05-01', 820.00





SELECT *

FROM @TB AS T

WHERE EXISTS(

	          SELECT 1 

	          FROM (SELECT T1.用户号,T1.污水费计费度, T1.计费月份

	                        FROM @TB  AS T1

	                        WHERE  EXISTS(SELECT 1 FROM @TB WHERE 用户号=T1.用户号 AND 污水费计费度=T1.污水费计费度 AND 计费月份 BETWEEN T1.计费月份 AND DATEADD(MONTH,2,T1.计费月份)  HAVING COUNT(*)>=3)

	                       ) AS T2

	          WHERE T.用户号=T2.用户号 AND T.污水费计费度=T2.污水费计费度 AND T.计费月份 BETWEEN T2.计费月份 AND DATEADD(MONTH,2,T2.计费月份)

	          )

ORDER BY 用户号,污水费计费度,计费月份

/*

用户号    用户名称        污水费计费度               计费月份                                                   水费金额                 

------ ----------- -------------------- ------------------------------------------------------ -------------------- 

000001 AAAAAAAAAAA 1.00                 2009-03-01 00:00:00.000                                120.00

000001 AAAAAAAAAAA 1.00                 2009-04-01 00:00:00.000                                120.00

000001 AAAAAAAAAAA 1.00                 2009-05-01 00:00:00.000                                120.00

000002 BBBBBBBBBBB 1.11                 2009-03-01 00:00:00.000                                122.00

000002 BBBBBBBBBBB 1.11                 2009-04-01 00:00:00.000                                122.00

000002 BBBBBBBBBBB 1.11                 2009-05-01 00:00:00.000                                122.00

000003 CCCCCCCCCCC 4.00                 2009-03-01 00:00:00.000                                812.00

000003 CCCCCCCCCCC 4.00                 2009-04-01 00:00:00.000                                812.00

000003 CCCCCCCCCCC 4.00                 2009-05-01 00:00:00.000                                812.00

000004 DDDDDDDDDDD 6.45                 2009-03-01 00:00:00.000                                212.00

000004 DDDDDDDDDDD 6.45                 2009-04-01 00:00:00.000                                822.00

000004 DDDDDDDDDDD 6.45                 2009-04-01 00:00:00.000                                822.00

000004 DDDDDDDDDDD 6.45                 2009-05-01 00:00:00.000                                820.00



(13 row(s) affected)

*/
htl258_Tony 2009-07-24
  • 打赏
  • 举报
 回复 




---------------------------------

--  Author: htl258(Tony)

--  Date  : 2009-07-24 18:24:21

---------------------------------

--> 生成测试数据表:tb



If not object_id('[tb]') is null

	Drop table [tb]

Go

Create table [tb]([用户号] int,[用户名称] nvarchar(11),[污水费计费度] decimal(18,2),[计费月份] Datetime,[水费金额] decimal(18,2))

Insert tb

Select 000001,'AAAAAAAAAAA',1.00,'2009-01-01',120.00 union all

Select 000002,'BBBBBBBBBBB',1.11,'2009-01-01',122.00 union all

Select 000003,'CCCCCCCCCCC',4.00,'2009-01-01',812.00 union all

Select 000004,'DDDDDDDDDDD',6.45,'2009-01-01',822.00 union all

Select 000001,'AAAAAAAAAAA',1.10,'2009-02-01',20.00 union all

Select 000002,'BBBBBBBBBBB',1.31,'2009-02-01',22.00 union all

Select 000003,'CCCCCCCCCCC',4.40,'2009-02-01',82.00 union all

Select 000004,'DDDDDDDDDDD',2.30,'2009-02-01',23.00 union all

Select 000001,'AAAAAAAAAAA',1.00,'2009-03-01',120.00 union all

Select 000002,'BBBBBBBBBBB',1.11,'2009-03-01',122.00 union all

Select 000003,'CCCCCCCCCCC',4.00,'2009-03-01',812.00 union all

Select 000004,'DDDDDDDDDDD',6.45,'2009-03-01',212.00 union all

Select 000001,'AAAAAAAAAAA',1.00,'2009-04-01',120.00 union all

Select 000002,'BBBBBBBBBBB',1.11,'2009-04-01',122.00 union all

Select 000003,'CCCCCCCCCCC',4.00,'2009-04-01',812.00 union all

Select 000004,'DDDDDDDDDDD',6.45,'2009-04-01',822.00 union all

Select 000001,'AAAAAAAAAAA',1.00,'2009-05-01',120.00 union all

Select 000002,'BBBBBBBBBBB',1.11,'2009-05-01',122.00 union all

Select 000003,'CCCCCCCCCCC',4.00,'2009-05-01',812.00 union all

Select 000004,'DDDDDDDDDDD',6.45,'2009-05-01',820.00 union all

Select 000004,'DDDDDDDDDDD',6.45,'2009-06-01',820.00 union all

Select 000001,'AAAAAAAAAAA',1.00,'2009-06-01',120.00

Go

--Select * from tb



-->SQL查询如下:

;with t as

(

	select * from tb t

	where exists(

		select 1

		from tb 

		where [用户号]=t.[用户号]

			and 污水费计费度=t.污水费计费度

			and abs(datediff(mm,[计费月份],t.[计费月份]))=1)

)

select * 

from t a 

where exists(

	select 1 

	from t 

	where 用户号=a.用户号 

	group by 用户号 

	having count(1)>3)

order by 1

/*

用户号         用户名称        污水费计费度                                  计费月份                    水费金额

----------- ----------- --------------------------------------- ----------------------- ---------------------------------------

1           AAAAAAAAAAA 1.00                                    2009-03-01 00:00:00.000 120.00

1           AAAAAAAAAAA 1.00                                    2009-04-01 00:00:00.000 120.00

1           AAAAAAAAAAA 1.00                                    2009-05-01 00:00:00.000 120.00

1           AAAAAAAAAAA 1.00                                    2009-06-01 00:00:00.000 120.00

4           DDDDDDDDDDD 6.45                                    2009-05-01 00:00:00.000 820.00

4           DDDDDDDDDDD 6.45                                    2009-06-01 00:00:00.000 820.00

4           DDDDDDDDDDD 6.45                                    2009-04-01 00:00:00.000 822.00

4           DDDDDDDDDDD 6.45                                    2009-03-01 00:00:00.000 212.00



(8 行受影响)



*/
csdyyr 2009-07-24
  • 打赏
  • 举报
 回复 


SELECT *

FROM @TB AS T

WHERE EXISTS(SELECT 1 FROM @TB WHERE 用户号=T.用户号 AND 污水费计费度=T.污水费计费度 AND 计费月份 BETWEEN 计费月份 AND DATEADD(MONTH,2,T.计费月份) HAVING COUNT(*)>=3)

ORDER BY 用户号,污水费计费度,计费月份
linbiao523 2009-07-24
  • 打赏
  • 举报
 回复 


select * from TableName where 用户名称+convert(varchar,污水费计费度)=(select 用户名称+convert(varchar,污水费计费度) from C_car

						group by 用户名称+convert(varchar,污水费计费度) having count(*)>1)

and exists (select 1 from TableName a where (a.用户名称+a.污水费计费度)=(C_car.用户名称+C_car.污水费计费度) and abs(datediff(d,a.计费月份,C_Car.计费月份))=1)

and exists (select 1 from TableName a where (a.用户名称+a.污水费计费度)=(C_car.用户名称+C_car.污水费计费度) and abs(datediff(d,a.计费月份,C_Car.计费月份))=2)

order by 计费月份
qgcsoft 2009-07-24
  • 打赏
  • 举报
 回复
就是条件记录以上
htl258_Tony 2009-07-24
  • 打赏
  • 举报
 回复
[Quote=引用 9 楼 qgcsoft 的回复:]
上面的问题已经解决。
问题：接着上面的问题，上面的结果是数据相同的连续月份就查询来，我现在想3以上的记录查出来，该怎么做
[/Quote]
什么叫3以上?
qgcsoft 2009-07-24
  • 打赏
  • 举报
 回复
上面的问题已经解决。
问题：接着上面的问题，上面的结果是数据相同的连续月份就查询来，我现在想3以上的记录查出来，该怎么做
linbiao523 2009-07-24
  • 打赏
  • 举报
 回复 


select * from TableName where 用户名称+convert(varchar,污水费计费度)=(select 用户名称+convert(varchar,污水费计费度) from C_car

						group by 用户名称+convert(varchar,污水费计费度) having count(*)>1)

and exists (select 1 from TableName a where (a.用户名称+a.污水费计费度)=(C_car.用户名称+C_car.污水费计费度) and (dateadd(d,-1,a.计费月份)=C_Car.计费月份 or dateadd(d,1,a.计费月份)=C_Car.计费月份))



order by 计费月份
--小F-- 2009-07-24
  • 打赏
  • 举报
 回复 
--6楼的括号位置有点错误

----------------------------------------------------------------

-- Author :fredrickhu(小F 向高手学习)

-- Date   :2009-07-24 16:08:18

----------------------------------------------------------------

--> 测试数据：[tb]

if object_id('[tb]') is not null drop table [tb]

create table [tb]([用户号] varchar(6),[用户名称] varchar(11),[污水费计费度] numeric(3,2),[计费月份] datetime,[水费金额] numeric(5,2))

insert [tb]

select '000001','AAAAAAAAAAA',1.00,'2009-01-01',120.00 union all

select '000002','BBBBBBBBBBB',1.11,'2009-01-01',122.00 union all

select '000003','CCCCCCCCCCC',4.00,'2009-01-01',812.00 union all

select '000004','DDDDDDDDDDD',6.45,'2009-01-01',822.00 union all

select '000001','AAAAAAAAAAA',1.10,'2009-02-01',20.00 union all

select '000002','BBBBBBBBBBB',1.31,'2009-02-01',22.00 union all

select '000003','CCCCCCCCCCC',4.40,'2009-02-01',82.00 union all

select '000004','DDDDDDDDDDD',2.30,'2009-02-01',23.00 union all

select '000001','AAAAAAAAAAA',1.00,'2009-03-01',120.00 union all

select '000002','BBBBBBBBBBB',1.11,'2009-03-01',122.00 union all

select '000003','CCCCCCCCCCC',4.00,'2009-03-01',812.00 union all

select '000004','DDDDDDDDDDD',6.45,'2009-03-01',212.00 union all

select '000001','AAAAAAAAAAA',1.00,'2009-04-01',120.00 union all

select '000002','BBBBBBBBBBB',1.11,'2009-04-01',122.00 union all

select '000003','CCCCCCCCCCC',4.00,'2009-04-01',812.00 union all

select '000004','DDDDDDDDDDD',6.45,'2009-04-01',822.00 union all

select '000001','AAAAAAAAAAA',1.00,'2009-05-01',120.00 union all

select '000002','BBBBBBBBBBB',1.11,'2009-05-01',122.00 union all

select '000003','CCCCCCCCCCC',4.00,'2009-05-01',812.00 union all

select '000004','DDDDDDDDDDD',6.45,'2009-05-01',820.00

--------------开始查询--------------------------

select

    *

from

    tb t

where 

    exists

(select * from tb where 用户号=t.用户号 and 污水费计费度=t.污水费计费度

    and 

abs(datediff(mm,计费月份,t.计费月份))=1)

order by

     用户号,污水费计费度,计费月份

----------------结果----------------------------

/*用户号    用户名称        污水费计费度 计费月份                                                   水费金额    

------ ----------- ------ ------------------------------------------------------ ------- 

000001 AAAAAAAAAAA 1.00   2009-03-01 00:00:00.000                                120.00

000001 AAAAAAAAAAA 1.00   2009-04-01 00:00:00.000                                120.00

000001 AAAAAAAAAAA 1.00   2009-05-01 00:00:00.000                                120.00

000002 BBBBBBBBBBB 1.11   2009-03-01 00:00:00.000                                122.00

000002 BBBBBBBBBBB 1.11   2009-04-01 00:00:00.000                                122.00

000002 BBBBBBBBBBB 1.11   2009-05-01 00:00:00.000                                122.00

000003 CCCCCCCCCCC 4.00   2009-03-01 00:00:00.000                                812.00

000003 CCCCCCCCCCC 4.00   2009-04-01 00:00:00.000                                812.00

000003 CCCCCCCCCCC 4.00   2009-05-01 00:00:00.000                                812.00

000004 DDDDDDDDDDD 6.45   2009-03-01 00:00:00.000                                212.00

000004 DDDDDDDDDDD 6.45   2009-04-01 00:00:00.000                                822.00

000004 DDDDDDDDDDD 6.45   2009-05-01 00:00:00.000                                820.00



（所影响的行数为 12 行）

*/
--小F-- 2009-07-24
  • 打赏
  • 举报
 回复 


select

    *

from

    tb t

where 

    exists

(select 1 from tb where 用户名称=t.用户名称 and 污水费计费度=t.污水费计费度)

    and 

abs(datediff(mm,计费月份,t.计费月份))=1

order by

     用户号,用户名称
csdyyr 2009-07-24
  • 打赏
  • 举报
 回复 
DECLARE @TB TABLE([用户号] VARCHAR(6), [用户名称] VARCHAR(11), [污水费计费度] DECIMAL(18,2), [计费月份] DATETIME, [水费金额] DECIMAL(18,2))

INSERT @TB 

SELECT '000001', 'AAAAAAAAAAA', 1.00, '2009-01-01', 120.00 UNION ALL 

SELECT '000002', 'BBBBBBBBBBB', 1.11, '2009-01-01', 122.00 UNION ALL 

SELECT '000003', 'CCCCCCCCCCC', 4.00, '2009-01-01', 812.00 UNION ALL 

SELECT '000004', 'DDDDDDDDDDD', 6.45, '2009-01-01', 822.00 UNION ALL 

SELECT '000001', 'AAAAAAAAAAA', 1.10, '2009-02-01', 20.00 UNION ALL 

SELECT '000002', 'BBBBBBBBBBB', 1.31, '2009-02-01', 22.00 UNION ALL 

SELECT '000003', 'CCCCCCCCCCC', 4.40, '2009-02-01', 82.00 UNION ALL 

SELECT '000004', 'DDDDDDDDDDD', 2.30, '2009-02-01', 23.00 UNION ALL 

SELECT '000001', 'AAAAAAAAAAA', 1.00, '2009-03-01', 120.00 UNION ALL 

SELECT '000002', 'BBBBBBBBBBB', 1.11, '2009-03-01', 122.00 UNION ALL 

SELECT '000003', 'CCCCCCCCCCC', 4.00, '2009-03-01', 812.00 UNION ALL 

SELECT '000004', 'DDDDDDDDDDD', 6.45, '2009-03-01', 212.00 UNION ALL 

SELECT '000001', 'AAAAAAAAAAA', 1.00, '2009-04-01', 120.00 UNION ALL 

SELECT '000002', 'BBBBBBBBBBB', 1.11, '2009-04-01', 122.00 UNION ALL 

SELECT '000003', 'CCCCCCCCCCC', 4.00, '2009-04-01', 812.00 UNION ALL 

SELECT '000004', 'DDDDDDDDDDD', 6.45, '2009-04-01', 822.00 UNION ALL 

SELECT '000001', 'AAAAAAAAAAA', 1.00, '2009-05-01', 120.00 UNION ALL 

SELECT '000002', 'BBBBBBBBBBB', 1.11, '2009-05-01', 122.00 UNION ALL 

SELECT '000003', 'CCCCCCCCCCC', 4.00, '2009-05-01', 812.00 UNION ALL 

SELECT '000004', 'DDDDDDDDDDD', 6.45, '2009-05-01', 820.00



SELECT *

FROM @TB AS T

WHERE EXISTS(SELECT * FROM @TB WHERE 用户号=T.用户号 AND 污水费计费度=T.污水费计费度 AND ABS(DATEDIFF(MONTH,计费月份,T.计费月份))=1)

ORDER BY 用户号,污水费计费度,计费月份

/*

用户号    用户名称        污水费计费度               计费月份                                                   水费金额                 

------ ----------- -------------------- ------------------------------------------------------ -------------------- 

000001 AAAAAAAAAAA 1.00                 2009-03-01 00:00:00.000                                120.00

000001 AAAAAAAAAAA 1.00                 2009-04-01 00:00:00.000                                120.00

000001 AAAAAAAAAAA 1.00                 2009-05-01 00:00:00.000                                120.00

000002 BBBBBBBBBBB 1.11                 2009-03-01 00:00:00.000                                122.00

000002 BBBBBBBBBBB 1.11                 2009-04-01 00:00:00.000                                122.00

000002 BBBBBBBBBBB 1.11                 2009-05-01 00:00:00.000                                122.00

000003 CCCCCCCCCCC 4.00                 2009-03-01 00:00:00.000                                812.00

000003 CCCCCCCCCCC 4.00                 2009-04-01 00:00:00.000                                812.00

000003 CCCCCCCCCCC 4.00                 2009-05-01 00:00:00.000                                812.00

000004 DDDDDDDDDDD 6.45                 2009-03-01 00:00:00.000                                212.00

000004 DDDDDDDDDDD 6.45                 2009-04-01 00:00:00.000                                822.00

000004 DDDDDDDDDDD 6.45                 2009-05-01 00:00:00.000                                820.00



(12 row(s) affected)

*/
qgcsoft 2009-07-24
  • 打赏
  • 举报
 回复
[Quote=引用 1 楼 guguda2008 的回复:]
只要那个结果的话
SELECT * FROM 表名
ORDER BY 用户号      ,污水费计费度    ,计费月份   
[/Quote]
楼上的你有没有看清题目呀，

（如何查询相同“用户号”和“污水费计费度”并且连续“计费月份”的记录。）
在用户号和污水费计费度相同条件下，还有一个条件月份要连续的
仙道彰 2009-07-24
  • 打赏
  • 举报
 回复
参考这个
http://topic.csdn.net/u/20090724/12/97b09dc8-8d73-4601-86fe-55fbc55d0681.html
仙道彰 2009-07-24
  • 打赏
  • 举报
 回复 
select * from t a where 

not exists

(select 1 from t 

where

 id=a.id 

and 

abs(datediff(mm,date,a.date))=2 

or 

abs(datediff(mm,date,a.date))=1)
guguda2008 2009-07-24
  • 打赏
  • 举报
 回复
只要那个结果的话
SELECT * FROM 表名
ORDER BY 用户号 ,污水费计费度 ,计费月份
SQL-连续记录查询
记录一下一道秋招笔试SQL题目：【主要关于连续记录查询】 题目：X市建了一个新的体育馆，每日人流量信息记录在这三列的信息中，序号（id）、日期（visit_date)、人流量（people）。请编写一个查询语句，找到人流量的高峰期。高峰期时，至少连续三行记录中的人流量不少于100人。 例如： 对于上面的示例数据，输出结果为如下： 分析解答：【本文使用mysql】 1....
SQL查询连续记录
目的：编写一个语句查询出连续三条记录的人数不少于100的记录 记录： 记录中的visit_date是连续的，但是people的数量是不一样的， 输出结果： 思路： 先将人数大于100的查询出来 select * from data WHERE people >= 100 输出来的结果： 接下来我需要在这个表格的基础上找出至少连续的三条记录。这里我使用的是以ip为一条记录。 思路： 先...
sql进阶—— 查询重复数据& 查询连续数据
一、索引的类型1. 普通索引2. 唯一索引3. 全文索引（FULLTEXT）4. 单列索引、多列索引多个单列索引与单个多列索引的查询效果不同，因为执行查询时，MySQL只能使用一个索引，会从多个索引中选择一个限制最为严格的索引。5. 组合索引（最左前缀）平时用的SQL查询语句一般都有比较多的限制条件，所以为了进一步榨取MySQL的效率，就要考虑建立组合索引。方法：使用多个自连表来实现(当查找的连续值过多时，运行效率过低)MySQL 索引_多则惑少则明的博客-CSDN博客。查询至少连续出现3次的成绩。
SQL查询字段A连续三条相同的记录ID
初始表数据如下： ID a b c 1 1 2 3 2 1 4 5 3 1 3 6 4 2 3 3 5 1 5 7 6 2 5 8 7 1 6 9 8 1 2 3 9 1 4 5 10 1 3 6 要求用SQL实现如下效果： id a b c 1 1 2 3 2 1 4 5 3 1 3 6 7 1 6 9 8...
Sql练习--查询连续出现三次的数据
编写一个 SQL 查询，查找所有至少连续出现三次的数字。 +----+-----+ | Id | Num | +----+-----+ | 1 | 1 | | 2 | 1 | | 3 | 1 | | 4 | 2 | | 5 | 1 | | 6 | 2 | | 7 | 2 | +----+-----+ 例如，给定上面的 Logs 表， 1 是唯一...
应用实例

27,579

社区成员

68,558

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server 应用实例
社区管理员
  • 应用实例社区
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章