筛选查询

[intellectual123456]

/*建立test1,test2*/

create table test1
(test1id int ,col1 char(1),col2 char(1),col3 char(1))

insert into test1 (test1id,col1,col2,col3)

select 1,'a','x' ,'l'
union all
select 2,'b','x' ,'l'
union all
select 3,'c','x' ,'m'
union all
select 4,'d','x' ,'m'
union all
select 5,'a','y' ,'n'
--drop table test1
--drop table test2

create table test2
(test2id int ,col1 char(1),col2 char(1),col4 char(2))

insert into test2 (test2id,col1,col2,col4)
SELECT 11,'a','x' ,'ee'
UNION ALL SELECT 12,'c','x','ee'
UNION ALL select 1,'b','x' ,'ee'
union all
select 2,'b','x' ,'ff'
union all
select 3,'a','x' ,'ff'
union all
select 4,'a','y' ,'gg'
union all
select 5,'b','y' ,'gg'
union all
select 6,'c','y' ,'hh'
union all
select 7,'a','z','hh'

--select * from test1
----select * from test2
--查询一 test2中a.col2=b.col2 ,并且筛选包含test1 a.col3='l'所对应的col1中的两条记录'a','b'
select * from test2 b
where exists(select 0 from test1 a where a.col2=b.col2 and a.col3='l' and a.col1='a')
and exists(select 0 from test1 a where a.col2=b.col2 and a.col3='l' and a.col1='b')

--查询二 test2中a.col2=b.col2 ,并且筛选包含test1 a.col3='m'所对应的col1中的两条记录'c','d'
select * from test2 b
where exists(select 0 from test1 a where a.col2=b.col2 and a.col3='m' and a.col1='d')
and exists(select 0 from test1 a where a.col2=b.col2 and a.col3='m' and a.col1='d')
--查询三 test2中a.col2=b.col2 ,并且筛选包含test1 a.col3='n'所对应的col1中的记录'a'
select * from test2 b
where exists(select 0 from test1 a where a.col2=b.col2 and a.col3='n' and a.col1='a')
--有没有办法一次查询出这些记录,并且不直接引用'l','a','m'.等记录

[intellectual123456]

create table test1
(test1id int ,col1 char(1),col2 char(1),col3 char(1))
insert into test1 (test1id,col1,col2,col3)
select 1,'a','x' ,'l'
union all
select 3,'c','x' ,'m'
union all
select 5,'a','y' ,'n'
--drop table test1
--drop table test2
create table test2
(test2id int ,col1 char(1),col2 char(1),col4 char(2))
insert into test2 (test2id,col1,col2,col4)
SELECT 11,'a','x' ,'ee'
union all
select 4,'a','y' ,'gg'
union all
select 5,'b','y' ,'gg'
union all
select 6,'c','y' ,'hh'
union all
select 7,'a','z','hh'
--七楼的老师:我改一下数据你看看 exists(a) and exists(b) 并不等于exists(ab)
select * from test2 b
where exists(select 0 from test1 a where a.col2=b.col2 and a.col3='l' and a.col1='a')
and exists(select 0 from test1 a where a.col2=b.col2 and a.col3='l' and a.col1='b')
并不相当于:
select * from test2 b
where exists(select 0 from test1 a where a.col2=b.col2 and a.col3='l')

[wgzaaa]

是不等了，那么a.col3='l' and a.col1='b'这里的了又从何而来？

[intellectual123456]

是我错了.
请教一下:
/*建立test1,test2*/

create table test1
(test1id int ,col1 char(1),col2 char(1),col3 char(1))

insert into test1 (test1id,col1,col2,col3)

select 1,'a','x' ,'l'
union all
select 2,'b','x' ,'l'
union all
select 3,'c','x' ,'m'
union all
select 4,'d','x' ,'m'
union all
select 5,'a','y' ,'n'
--drop table test1
--drop table test2

create table test2
(test2id int ,col1 char(1),col2 char(1),col4 char(2))

insert into test2 (test2id,col1,col2,col4)
SELECT 11,'a','x' ,'ee'

union all
select 4,'a','y' ,'gg'
union all
select 5,'b','y' ,'gg'
union all
select 6,'c','y' ,'hh'
union all
select 7,'a','z','hh'

我要查询test2中col1中既有a又有b的列,test2中的col2必须和test1 中的col1
一样(x).
select * from test2 b
where
exists(select * from test1 a where a.col2=b.col2
and a.col1=b.col1 and a.col3='l' and a.col1 in ('a','b') )
这样查询的话,只要包含 ab任何一列就会出来.
如何包含ab,结果应该是没有记录.

[intellectual123456]

test2中的col2必须和test1 中的col2
一样(x).

[wgzaaa]

select * from test2 where test2id in
( select max(test2id) from test2 a
right join test1 b on a.col2=b.col2 and a.col1=b.col1
group by col3 having count(*)=count(test2id)
)

[wgzaaa]

上面不对

[wgzaaa]

--先用这个试下吧
select a.* from test2 a left join test1 b on a.col2=b.col2 and a.col1=b.col1
where col3 in
( select b.col3 from test2 a
right join test1 b on a.col2=b.col2 and a.col1=b.col1
group by b.col3 having count(*)=count(test2id)
)

[intellectual123456]

太感谢了你的帮助了!
由于我的数据比较复杂,我在简化的时候出了一些问题.所以还有一个问题请你再看看
我重新建立了更好的测试数据.
/*建立test1,test2*/
create table test1
(test1id int ,col1 char(1),col2 char(1),col3 char(1))

insert into test1 (test1id,col1,col2,col3)

select 1,'a','x' ,'l'
union all
select 2,'b','x' ,'l'
union all
select 3,'c','x' ,'l'
union all
select 4,'d','y' ,'m'
union all
select 5,'e','y' ,'n'
union all
select 4,'f','z' ,'o'
union all
select 5,'g','z' ,'o'
--drop table test1
--drop table test2

create table test2
(test2id int ,col1 char(1),col2 char(1),col4 char(2))

insert into test2 (test2id,col1,col2,col4)
SELECT 11,'a','x' ,'ee'
union all
SELECT 11,'b','x' ,'ee'
union all
select 4,'d','y' ,'ff'
union all
select 5,'c','y' ,'ff'
union all
select 6,'a','y' ,'ff'
union all
select 7,'a','z','hh'
union all
SELECT 11,'a','x' ,'gg'
union all
SELECT 11,'c','x' ,'gg'

--按照你的代码,查询后数据如下
select a.* from test2 a left join test1 b on a.col2=b.col2 and a.col1=b.col1
where col3 in
( select b.col3 from test2 a
right join test1 b on a.col2=b.col2 and a.col1=b.col1
group by b.col3 having count(*)=count(test2id)
)
/*
test2id col1 col2 col4
----------- ---- ---- ----
11 a x ee
11 a x gg
11 b x ee
11 c x gg
4 d y ff
*/
--按照前面的规则结果正确,满足条件(col2 和col2相等,col1包含 a,b,c,)
--现在有一个问题,x对应的col4结果中ee 和gg都没有各自全部包含 a,b,c
--现在要在原来的规则上面,加上col4的分组结果也要满足各个规则.
--test2id =11的这四条记录不符合要求.
--如何在原来的查询中去掉?

[wgzaaa]

如果还有一条记录
11 c x ee
那么是不是
11 a x ee
11 b x ee
11 c x ee
应该显示
而
11 a x gg
11 c x gg
不显示？

[wgzaaa]

select 4,'f','z' ,'o'
union all
select 5,'g','z' ,'o' --已经有4,5的test1id了
正确吗，你的数据是不是test2中test2id不重复，那test1id可重复吗？是不是不同的col3不存在有相同的col1,col2?
test2中的数据呢，再仔细考虑一下，贴出最终样例数据，并加以说明。

[intellectual123456]

/*建立test1,test2*/
create table test1
(test1id int ,col1 char(1),col2 char(1),col3 char(1))

insert into test1 (test1id,col1,col2,col3)

select 1,'a','x' ,'l'
union all
select 2,'b','x' ,'l'
union all
select 3,'c','x' ,'l'
union all
select 4,'d','y' ,'m'
union all
select 5,'e','y' ,'n'
union all
select 6,'f','z' ,'o'
union all
select 7,'g','z' ,'o'
--drop table test1
--drop table test2

create table test2
(test2id int ,col1 char(1),col2 char(1),col4 char(2))

insert into test2 (test2id,col1,col2,col4)
SELECT 11,'a','x' ,'ee'
union all
SELECT 12,'b','x' ,'ee'
union all
SELECT 13,'c','x' ,'ee'
union all
select 4,'d','y' ,'ff'
union all
select 5,'c','y' ,'ff'
union all
select 6,'a','y' ,'ff'
union all
select 7,'a','z','hh'
union all
SELECT 15,'a','x' ,'gg'
union all
SELECT 16,'c','x' ,'gg'

/* 1.test1 和test2中的id都不重复

test1 col3不存在有相同的col1,col2
test2 中col4表中不存在co1,col2

要查询的结果的条件:
1.test1 和test2中的col2必须相等.
2.比对每组test2中的col4对应的test2中col1, col1必须包含全部数据data(可以多,不可以少)
data指的是test1col4每组数据各自对应本表col1中的全部数据.
结果应该是:

11 a x ee
12 b x ee
13 c x ee
4 d y ff
*/

[intellectual123456]

test2 中col4表中不存在相同的co1,col2

[wgzaaa]

4 d y ff--已经显示，下面的一定不能显示吗？
5 c y ff
6 a y ff

[wgzaaa]

--要能显示就用下面的，要忙活了。
select * from test2 where col4 in(
select col4 from test1 a left join test2 b
on a.col2=b.col2 and a.col1=b.col1
group by col3,col4 having count(col4)=(select count(*) from test1 where col3=a.col3)
)order by col4

[intellectual123456]

之所以ff的后两条不出来是因为为5 c y ff
6 a y ff
这两条记录不符合要求.
所对应的
select 4,'d','y' ,'m'
union all
select 5,'e','y' ,'n'
中间没有c和a

如果你有全部出来的代码,给我也可以了.
我试试看,我有问题,再开一帖.已经很麻烦你了.

[intellectual123456]

这个查询还没有解决我的问题.不过帖子太长了.我先结了.

[intellectual555555]

select * from test2 where col4 in(
select col4 from test1 a left join test2 b
on a.col2=b.col2 and a.col1=b.col1
group by col3,col4 having count(col4)=(select count(*) from test1 where col3=a.col3)
)order by col4
/*结果
test2id col1 col2 col4
----------- ---- ---- ----
11 a x ee
12 b x ee
13 c x ee
4 d y ff
5 c y ff
6 a y ff

此结果多了两条不要的记录
之所以ff的后两条不出来是因为为5 c y ff
6 a y ff
这两条记录不符合要求.
所对应的
select 4,'d','y' ,'m'
union all
select 5,'e','y' ,'n'
中间没有c和a
(6 行受影响)


想要的记录
结果应该是:

11 a x ee
12 b x ee
13 c x ee
4 d y ff
*/
*/

[intellectual123456]

应该显示
11 c x ee
11 a x ee
11 b x ee
4 d y ff

[wgzaaa]

select ta.* from test2 ta,(
select col4,test2id from test1 a left join test2 b on a.col2=b.col2 and a.col1=b.col1
group by col3,test2id,col4 having count(col4)=(select count(*) from test1 where col3=a.col3)
)tb where ta.test2id=tb.test2id and ta.col4=tb.col4
----------------------------
4 d y ff
11 a x ee
11 b x ee
11 c x ee

[intellectual123456]

/*大哥:数据我没有改动,但是上面test2中的标识列前面有重复.我改成不重复了,
结果就不对了.请你再看看!谢谢啦!
*/

/*建立test1,test2*/
create table test1
(test1id int ,col1 char(1),col2 char(1),col3 char(1))

insert into test1 (test1id,col1,col2,col3)

select 1,'a','x' ,'l'
union all
select 2,'b','x' ,'l'
union all
select 3,'c','x' ,'l'
union all
select 4,'d','y' ,'m'
union all
select 5,'e','y' ,'n'
union all
select 4,'f','z' ,'o'
union all
select 5,'g','z' ,'o'
--drop table test1
--drop table test2

create table test2
(test2id int ,col1 char(1),col2 char(1),col4 char(2))

insert into test2 (test2id,col1,col2,col4)
SELECT 11,'a','x' ,'ee'
union all
SELECT 12,'b','x' ,'ee'
union all
SELECT 13,'c','x' ,'ee'
union all
select 4,'d','y' ,'ff'
union all
select 5,'c','y' ,'ff'
union all
select 6,'a','y' ,'ff'
union all
select 7,'a','z','hh'
union all
SELECT 15,'a','x' ,'gg'
union all
SELECT 16,'c','x' ,'gg'
/*结果
4 d y ff
*/

/*
SELECT 11,'a','x' ,'ee'
union all
SELECT 12,'b','x' ,'ee'
union all
SELECT 13,'c','x' ,'ee'
应该显示的,没有显示.
*/