34,590
社区成员
发帖
与我相关
我的任务
分享
SELECT ID, COUNT(1) cnt
FROM [tb] t
WHERE EXISTS(
SELECT 1
FROM tb
WHERE id = t.id
AND flag >= 3
)
AND EXISTS(
SELECT 1
FROM tb
WHERE id = t.id
AND (date=t.date+1 OR date=t.date-1)
)
GROUP BY ID
HAVING COUNT(1)>=10
--> 生成测试数据表: [tb]
IF OBJECT_ID('[tb]') IS NOT NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb] ([ID] [nvarchar](10),[Flag] [int],[Date] [int])
INSERT INTO [tb]
SELECT '232','1','1' UNION ALL
SELECT '232','2','2' UNION ALL
SELECT '232','1','3' UNION ALL
SELECT '233','4','2' UNION ALL
SELECT '233','2','3' UNION ALL
SELECT '233','3','4' UNION ALL
SELECT 'dff','1','7'
-->SQL查询如下:
SELECT ID, COUNT(1) cnt
FROM [tb] t
WHERE EXISTS(
SELECT 1
FROM tb
WHERE id = t.id
AND flag >= 3
)
AND EXISTS(
SELECT 1
FROM tb
WHERE id = t.id
AND (date=t.date+1 OR date=t.date-1)
)
GROUP BY ID
HAVING COUNT(1)>=3
/*
ID cnt
---------- -----------
233 3
(1 行受影响)
*/
--ID相同且连续三天或三天以上Flag值大于等于2的ID及数量
declare @a table(ID INT, Flag INT, Date INT)
INSERT @a SELECT 232, 1, 1
union all select 232 ,2, 2
union all select 232 ,1, 3
union all select 233, 4, 2
union all select 233, 2, 3
union all select 233, 3, 4
union all select 333, 1, 7
SELECT id,COUNT(date-o) [COUNT] FROM
(
SELECT * ,o=(SELECT COUNT(1)+1 FROM @a WHERE id=a.id AND date<a.date AND flag>=2)
FROM @a a
WHERE flag>=2
)aa
GROUP BY id,date-o
HAVING COUNT(date-o)>2
--result
/*
id COUNT
----------- -----------
233 3
(所影响的行数为 1 行)
*/
--> 测试数据:[TB]
if object_id('[TB]') is not null drop table [TB]
create table [TB]([ID] varchar(3),[Flag] int,[Date] int)
insert [TB]
select '232',1,1 union all
select '232',2,2 union all
select '232',1,3 union all
select '233',2,2 union all
select '233',2,3 union all
select '233',2,4 union all
select 'dff',1,7
select * from [TB]
;WITH TT
AS(
SELECT id,flag,date, [rowid]=ROW_NUMBER()OVER (PARTITION BY id ORDER BY date) FROM TB WHERE flag = 2)
SELECT id,cnt = COUNT(1)
FROM (
SELECT * FROM TT WHERE date = rowid+1
)A
GROUP BY id HAVING COUNT(1)>=3
/*id cnt
---- -----------
233 3
(1 行受影响)*/
-->加个大于等于三天的条件:
SELECT ID, COUNT(1) cnt
FROM [tb] t
WHERE EXISTS(
SELECT 1
FROM tb
WHERE id = t.id
AND flag = 2
)
AND EXISTS(
SELECT 1
FROM tb
WHERE id = t.id
AND (date=t.date+1 OR date=t.date-1)
)
GROUP BY ID
HAVING COUNT(1)>=3
/*
ID cnt
---------- -----------
232 3
233 3
(2 行受影响)
*/
--> 生成测试数据表: [tb]
IF OBJECT_ID('[tb]') IS NOT NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb] ([ID] [nvarchar](10),[Flag] [int],[Date] [int])
INSERT INTO [tb]
SELECT '232','1','1' UNION ALL
SELECT '232','2','2' UNION ALL
SELECT '232','1','3' UNION ALL
SELECT '233','4','2' UNION ALL
SELECT '233','2','3' UNION ALL
SELECT '233','3','4' UNION ALL
SELECT 'dff','1','7'
-->SQL查询如下:
SELECT ID, COUNT(1) cnt
FROM [tb] t
WHERE EXISTS(
SELECT 1
FROM tb
WHERE id = t.id
AND flag = 2
)
AND EXISTS(
SELECT 1
FROM tb
WHERE id = t.id
AND (date=t.date+1 OR date=t.date-1)
)
GROUP BY ID
/*
ID cnt
---------- -----------
232 3
233 3
(2 行受影响)
*/