test1 col3不存在有相同的col1,col2 test2 中col4表中不存在相同co1,col2 要查询的结果的条件: 1.test1 和test2中的col2必须相等. 2.比对每组test2中的col4对应的test2中col1, col1必须包含全部数据data(可以多,不可以少) data指的是test1col4每组数据各自对应本表col1中的全部数据. 想要的结果是 test2中的记录: 11 a x ee 12 b x ee 13 c x ee 4 d y ff
select a.*,b.col4 from test1 a left join test2 b on a.col2=b.col2 where a.col1=b.col1
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 */ */
select identity(int,1,1) id, * into #t123 from test2 where exists(select 1 from test1 where test2.col1 = test1.col1 and test2.col2 = test1.col2) go select * from #t123 where not exists(select 1 from #t123 as aa where aa.col1 = #t123.col1 and aa.col2 = #t123.col2 and aa.id<#t123.id) 这样???
6楼结果是一样的,但是逻辑不对.数据改变结果就不对. 我要的是关联到col3 和col4的数据
怒了:select b2.* from test2 b2 join test1 a2 on a2.col2=b2.col2 and a2.col1=b2.col1 where col4 in( select col4 from test1 a 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
select b2.* from test2 b2 join test1 a2 on a2.col2=b2.col2 and a2.col1=b2.col1 where col4 in( select col4 from test1 a 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
test1 col3不存在有相同的col1,col2
test2 中col4表中不存在相同co1,col2 要查询的结果的条件:
1.test1 和test2中的col2必须相等.
2.比对每组test2中的col4对应的test2中col1, col1必须包含全部数据data(可以多,不可以少)
data指的是test1col4每组数据各自对应本表col1中的全部数据. 想要的结果是 test2中的记录: 11 a x ee
12 b x ee
13 c x ee
4 d y ff
left join test2 b on a.col2=b.col2
where a.col1=b.col1
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
*/
*/
select identity(int,1,1) id, * into #t123 from test2
where exists(select 1 from test1 where test2.col1 = test1.col1 and test2.col2 = test1.col2)
go
select * from #t123
where not exists(select 1 from #t123 as aa where aa.col1 = #t123.col1 and aa.col2 = #t123.col2 and aa.id<#t123.id)
这样???
我要的是关联到col3 和col4的数据
on a2.col2=b2.col2 and a2.col1=b2.col1
where col4 in(
select col4 from test1 a 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
on a2.col2=b2.col2 and a2.col1=b2.col1
where col4 in(
select col4 from test1 a 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