一个表,三个字段,Table A(id int,P int, V varchar(10)) ,P代表某个事件,相同的P值代表同一个事件。V代表其某个属性的值。
表中数据类似以下:1, 1, 'A'
2, 2, 'C'
3, 1, 'B'
4, 2, 'D'
5, 2, 'C'
6, 1, 'A'
7, 2, 'C'
8, 1, 'A'现在要找出同一个P的V值改变时的最小ID。如上述记录中,P为 1 时的记录:
1,1, 'A'
3,1, 'B'
6,1, 'A'
8,1, 'A'
ID分别为1、3、6时属性改变。要找出1、3、6这三条记录。数据量非常大。我想找出这些记录存到另一个表。需要有一个比较高效的语句。谢谢
表中数据类似以下:1, 1, 'A'
2, 2, 'C'
3, 1, 'B'
4, 2, 'D'
5, 2, 'C'
6, 1, 'A'
7, 2, 'C'
8, 1, 'A'现在要找出同一个P的V值改变时的最小ID。如上述记录中,P为 1 时的记录:
1,1, 'A'
3,1, 'B'
6,1, 'A'
8,1, 'A'
ID分别为1、3、6时属性改变。要找出1、3、6这三条记录。数据量非常大。我想找出这些记录存到另一个表。需要有一个比较高效的语句。谢谢
IF OBJECT_ID('A') IS NOT NULL
DROP TABLE A
GO
CREATE TABLE A(id INT,P INT,V VARCHAR(10))
INSERT INTO A
SELECT 1, 1, 'A' UNION ALL
SELECT 2, 2, 'C' UNION ALL
SELECT 3, 1, 'B' UNION ALL
SELECT 4, 2, 'D' UNION ALL
SELECT 5, 2, 'C' UNION ALL
SELECT 6, 1, 'A' UNION ALL
SELECT 7, 2, 'C' UNION ALL
SELECT 8, 1, 'A'
GO;WITH cte AS
(
SELECT id,p,v,n=(SELECT COUNT(1) FROM a t2 WHERE t1.p=t2.p AND t2.v=t1.v GROUP BY v ) FROM a t1
)
SELECT t1.id,t1.p,t1.v FROM cte t1 INNER JOIN
(SELECT p,MAX(n) AS maxNum FROM cte GROUP BY p) t2 ON
t1.p=t2.p WHERE t1.n=t2.maxNum
go
create table [A] (id int,p int,v nvarchar(2))
insert into [A]
select 1,1,'A' union all
select 2,2,'C' union all
select 3,1,'B' union all
select 4,2,'D' union all
select 5,2,'C' union all
select 6,1,'A' union all
select 7,2,'C' union all
select 8,1,'A' union all
select 9,1,'D'
select A.id,A.p,A.v
from (
select ROW_NUMBER() over(partition by p,v order by id) as no ,*
from A)A
where no <3
order by p,id,v/*
1 1 A
3 1 B
6 1 A
9 1 D
2 2 C
4 2 D
5 2 C
WITH
A as (
SELECT 1 id, 1 p, 'A' v UNION ALL
SELECT 2, 2, 'C' UNION ALL
SELECT 3, 2, 'C' UNION ALL
SELECT 4, 2, 'C' UNION ALL
SELECT 5, 2, 'C' UNION ALL
SELECT 6, 1, 'B' UNION ALL
SELECT 7, 2, 'D' UNION ALL
SELECT 8, 2, 'C' UNION ALL
SELECT 9, 1, 'A' UNION ALL
SELECT 10, 2, 'C' UNION ALL
SELECT 11, 1, 'A'
), B as
(select ROW_NUMBER() over(partition by p order by id) as num ,*
from A)
select *
from B t1
where exists (select * from B where (B.num=t1.num-1 and t1.p=B.p and t1.v<>B.v) or t1.num=1)
order by id
create table t1
(
id int,
p int,
v varchar(10)
)
insert into t1
select 1, 1, 'A' union all
select 2, 2, 'C' union all
select 3, 1, 'B' union all
select 4, 2, 'D' union all
select 5, 2, 'C' union all
select 6, 1, 'A' union all
select 7, 2, 'C' union all
select 8, 1, 'A'
select * from t1;with aaa as
(
select ROW_NUMBER() over(partition by p order by id) as rowindex,* from t1
)
select * from t1 where id not in
(select b.id from aaa as a inner join aaa as b on a.p=b.p and a.rowindex=b.rowindex-1 and a.v=b.v) order by p,id----------------------------
id p v
1 1 A
3 1 B
6 1 A
2 2 C
4 2 D
5 2 C
create table t1
(
id int,
p int,
v varchar(10)
)
insert into t1
select 1, 1, 'A' union all
select 2, 2, 'C' union all
select 3, 1, 'B' union all
select 4, 2, 'D' union all
select 5, 2, 'C' union all
select 6, 1, 'A' union all
select 7, 2, 'C' union all
select 8, 1, 'A'
select * from t1;with aaa as
(
select ROW_NUMBER() over(partition by p order by id) as rowindex,* from t1
)
delete from t1 where id in
(
select b.id from aaa as a inner join aaa as b on a.p=b.p and a.rowindex=b.rowindex-1 and a.v=b.v
)------------------------------
id p v
1 1 A
2 2 C
3 1 B
4 2 D
5 2 C
6 1 A