22,209
社区成员
发帖
与我相关
我的任务
分享
/*
AA BB
11 0
11 2
11 2
11 3
11 2
11 2
11 2
11 1
*/
/*
--结果应该显示为:
AA BB
---------
11 1
*/
---测试数据---
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([AA] int,[BB] int)
insert [tb]
select 11,0 union all
select 11,2 union all
select 11,2 union all
select 11,3 union all
select 11,2 union all
select 11,2 union all
select 11,2 union all
select 11,1
go
---查询---
;with josy as
(
select rn1=row_number() over(order by getdate()),* from tb
)
select a.AA,count(1) as BB
from
(select rn2=row_number() over(order by getdate()),*
from josy t
where not exists(select 1 from josy where bb=t.bb and rn1=t.rn1-1)
) a,
(select rn2=row_number() over(order by getdate()),*
from josy t
where not exists(select 1 from josy where bb=t.bb and rn1=t.rn1+1)
) b
where a.bb=b.bb and a.rn2=b.rn2 and b.rn1-a.rn1>=2
group by a.AA
---结果---
AA BB
----------- -----------
11 1
(1 行受影响)
IF OBJECT_ID('TB') IS NOT NULL DROP TABLE TB
GO
CREATE TABLE TB(AA INT,BB INT)
INSERT INTO TB
SELECT 11, 0 UNION ALL
SELECT 11, 2 UNION ALL
SELECT 11, 2 UNION ALL
SELECT 11, 3 UNION ALL
SELECT 11, 2 UNION ALL
SELECT 11, 2 UNION ALL
SELECT 11, 2 UNION ALL
SELECT 11, 1
GO
ALTER TABLE TB ADD CC INT
GO
DECLARE @AA INT,@BB INT,@CC INT
UPDATE TB SET @CC=CASE WHEN @AA=AA AND @BB=BB AND @BB>=2 THEN @CC+1 ELSE 1 END,@AA=AA,@BB=BB,CC=@CC
SELECT AA,COUNT(1) FROM TB
WHERE CC=3
GROUP BY AA
/*
11 1
*/
-- 表中的记录应该用 identity 列标示顺序,依赖存储顺序是不可靠的。
declare @tab table(aa int,bb int);
insert into @tab (aa,bb)
select 11,0 union all select 11,2 union all
select 11,2 union all select 11,3 union all
select 11,2 union all select 11,2 union all
select 11,2 union all select 11,1;
with t0 as(
select row_number() over (order by getdate()) id,aa,bb from @tab
),
t1 as(
select id,aa,bb,
id-ROW_NUMBER() over (partition by bb order by id) grp from t0
),
t2 as(
select id,aa,bb,count(1) over (partition by grp) cnt from t1
)
select aa,bb from t0
where id in (select max(id)+1 from t2
where cnt>2 and bb>=2);
--> 测试数据:#
if object_id('tempdb.dbo.#') is not null drop table #
create table #(AA int, BB int)
insert into #
select 11, 0 union all
select 11, 2 union all
select 11, 2 union all
select 11, 3 union all
select 11, 2 union all
select 11, 2 union all
select 11, 2 union all
select 11, 1
; with a as
(
select row=row_number()over(order by getdate()),* from #
),
b as
(
select id=row-row_number()over(partition by bb order by row),* from a where BB>=2
)
select AA,BB=1 from b group by id,AA having count(1)>2
/*
AA BB
----------- -----------
11 1
*/