描述 :
表TEST有列A,B,C,D,E,F,G,H,J 数据类型均为整型筛选条件:
1、A,B,C,D,E,F列 全部重复的数据不需要(group by a,b,c,d,e,f having count(1) >1数据不需要)
2、A,B 列数据需要重复(group by a,b having count(1)>1 ),筛选后的数据这两列重复数量需要大于1
3、当G=1时取出H列的值,其中H列的值不能出现在J列。
4、G列的条件满足 :G<>1
5、C,D,E列 满足 C>-1, E>5例如 : A B C D E F G H J
100 5 -1 3 10 1000 0 0 1
100 5 2 3 10 1000 0 0 2
100 5 2 3 10 1000 0 0 3
100 5 10 4 10 1000 0 0 4
100 6 -1 5 8 1000 1 6 5
100 6 2 4 8 1000 0 0 6
100 6 0 4 8 1000 0 0 7
100 7 10 7 8 1000 0 0 8
100 7 12 7 8 1000 0 0 9 经过筛选后:
结果为
100 7 10 7 8 1000 0 0 8
100 7 12 7 8 1000 0 0 9
表TEST有列A,B,C,D,E,F,G,H,J 数据类型均为整型筛选条件:
1、A,B,C,D,E,F列 全部重复的数据不需要(group by a,b,c,d,e,f having count(1) >1数据不需要)
2、A,B 列数据需要重复(group by a,b having count(1)>1 ),筛选后的数据这两列重复数量需要大于1
3、当G=1时取出H列的值,其中H列的值不能出现在J列。
4、G列的条件满足 :G<>1
5、C,D,E列 满足 C>-1, E>5例如 : A B C D E F G H J
100 5 -1 3 10 1000 0 0 1
100 5 2 3 10 1000 0 0 2
100 5 2 3 10 1000 0 0 3
100 5 10 4 10 1000 0 0 4
100 6 -1 5 8 1000 1 6 5
100 6 2 4 8 1000 0 0 6
100 6 0 4 8 1000 0 0 7
100 7 10 7 8 1000 0 0 8
100 7 12 7 8 1000 0 0 9 经过筛选后:
结果为
100 7 10 7 8 1000 0 0 8
100 7 12 7 8 1000 0 0 9
select *
from tb t
where (select count(1) from tb where a=t.a and b=t.b and c=t.c and d=t.d and e=t.e and f=t.f)=1
and exists (select 1 from tb where a=t.a and b=t.b group by a,b having count(1) > 1)
and C>-1 and E>5 and H <> J
楼主第三个条件和第四个条件有点冲突,因为G<>1,所以3条件中没有G=1的!
最后的数据需要过滤掉G<>1例如100 6 -1 5 8 1000 1 6 5
这条数据出现了6,那么100 6 2 4 8 1000 0 0 6 这条数据不需要!
select *
from tb t
where (select count(1) from tb where a=t.a and b=t.b and c=t.c and d=t.d and e=t.e and f=t.f)=1
and exists (select 1 from tb where a=t.a and b=t.b group by a,b having count(1) > 1)
and C>-1 and E>5 and G <> 1
and J not in (select H from tb where a=t.a and b=t.b and G=1)
先看看是否是楼主需要的,以后再优化下。
drop table [dbo].[TEST]
GOCREATE TABLE [dbo].[TEST] (
[A] [int] NULL ,
[B] [int] NULL ,
[C] [int] NULL ,
[D] [int] NULL ,
[E] [int] NULL ,
[F] [int] NULL ,
[G] [int] NULL ,
[H] [int] NULL ,
[J] [int] NULL
) ON [PRIMARY]
GOinsert into test (a,b,c,d,e,f,g,h,j) values (100, 5, -1 ,3 ,10 ,1000, 0 ,0 ,1)insert into test (a,b,c,d,e,f,g,h,j) values (100, 5, 2, 3, 10, 1000, 0, 0 ,2)insert into test (a,b,c,d,e,f,g,h,j) values (100, 5, 2, 3, 10 ,1000 ,0, 0, 3)insert into test (a,b,c,d,e,f,g,h,j) values (100, 5 ,10 ,4 ,10 ,1000, 0, 0, 4)insert into test (a,b,c,d,e,f,g,h,j) values (100, 6, -1, 5, 8, 1000, 1, 6 ,5)insert into test (a,b,c,d,e,f,g,h,j) values (100 ,6 ,2, 4 ,8, 1000 ,0, 0, 6)insert into test (a,b,c,d,e,f,g,h,j) values (100, 6, 0, 4 ,8 ,1000, 0, 0, 7)insert into test (a,b,c,d,e,f,g,h,j) values (100, 7, 10, 7, 8 ,1000 ,0, 0, 8)insert into test (a,b,c,d,e,f,g,h,j) values (100 ,7 ,12 ,7 ,8 ,1000 ,0, 0 ,9 )
CREATE TABLE [dbo].[TEST] (
[A] [int] NULL ,
[B] [int] NULL ,
[C] [int] NULL ,
[D] [int] NULL ,
[E] [int] NULL ,
[F] [int] NULL ,
[G] [int] NULL ,
[H] [int] NULL ,
[J] [int] NULL
) ON [PRIMARY]
GO
set nocount on
insert into test (a,b,c,d,e,f,g,h,j) values (100, 5, -1 ,3 ,10 ,1000, 0 ,0 ,1)
insert into test (a,b,c,d,e,f,g,h,j) values (100, 5, 2, 3, 10, 1000, 0, 0 ,2)
insert into test (a,b,c,d,e,f,g,h,j) values (100, 5, 2, 3, 10 ,1000 ,0, 0, 3)
insert into test (a,b,c,d,e,f,g,h,j) values (100, 5 ,10 ,4 ,10 ,1000, 0, 0, 4)
insert into test (a,b,c,d,e,f,g,h,j) values (100, 6, -1, 5, 8, 1000, 1, 6 ,5)
insert into test (a,b,c,d,e,f,g,h,j) values (100 ,6 ,2, 4 ,8, 1000 ,0, 0, 6)
insert into test (a,b,c,d,e,f,g,h,j) values (100, 6, 0, 4 ,8 ,1000, 0, 0, 7)
insert into test (a,b,c,d,e,f,g,h,j) values (100, 7, 10, 7, 8 ,1000 ,0, 0, 8)
insert into test (a,b,c,d,e,f,g,h,j) values (100 ,7 ,12 ,7 ,8 ,1000 ,0, 0 ,9 )
set nocount off
gowith cte as
(
select *
from test t
where (select count(1) from test where a=t.a and b=t.b and c=t.c and d=t.d and e=t.e and f=t.f)=1
and C>-1 and E>5 and G <> 1
and J not in (select H from test where a=t.a and b=t.b and G=1)
)select *
from cte t
where exists (select 1 from cte where a=t.a and b=t.b group by a,b having count(1)>1)drop table test
/******************A B C D E F G H J
----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
100 7 10 7 8 1000 0 0 8
100 7 12 7 8 1000 0 0 9(2 行受影响)
请问你用的是SQL哪个版本??
from test t
where (select count(1) from test where a=t.a and b=t.b and c=t.c and d=t.d and e=t.e and f=t.f)=1
and C>-1 and E>5 and G <> 1
and J not in (select H from test where a=t.a and b=t.b and G=1)select *
from #tb t
where exists (select 1 from #tb where a=t.a and b=t.b group by a,b having count(1)>1)drop table #tb