查询问题，来都有分

[lxysjl]

我有二个结构一模一样的表。
push
id, a1, a2, a3
1 2 3 4
2 1 2 3

history
id, a1, a2, a3
1 2 3 4
2 1 2 3
3 2 3 4
4 1 2 2
我要的结果是这样

得出history表中id为1，3的记录
也就有说查找push表中a1,a2,a3等于history表中a1,a2,a3的记录，并且相同的要有二条或二条以上才找出来。

[wubala]

我好像做过跟着个类似的问题,我是用游标来实现的,你可以试试

[paoluo]

Select * from
(Select * from push Where Exists(Select * from history Where a1=push.a1 And a2=push.a2 And a3=push.a3 Having Count(*)>2) ) A
Inner Join history B
On A.a1=B.a1 And A.a2=B.a2 And A.a3=B.a3

[hsj20041004]

select A.* from
(select a1, a2, a3 from push group by a1, a2, a3 having count(*)>1) A
inner join
(select a1, a2, a3 from history group by a1, a2, a3 having count(*)>1) B
on A.a1=B.a1 and A.a2=B.a2 and A.a3=B.a3

[jim138]

history中的ID字段要吗?若不要,可用:

select p.a1,p.a2,p.a3 from push p where exists (select h.a1,h.a2,h.a3 from history h where h.a1=p.a1 and h.a2=p.a2 and h.a3=p.a3 group by h.a1,h.a2,h.a3 having count(*)>=2)

[lxysjl]

上面的不行啊。由于那二个种不只那么一点字段，而且最后的结果都是history里面的所有字段，条件那就刚才那样。找到来的结果是history里面id为1，3的二条记录。但是其它字段的值会不同的。

[qudymeteor]

都说了，我还说什么呢，呵呵

[dwyao]

create table push(id int primary key ,a1 int ,a2 int,a3 int)
insert into push values(1,2,3,4)
insert into push values(2,1,2,3)
insert into push values(3,1,2,3)


create table history(id int primary key ,a1 int ,a2 int,a3 int)
insert into history values(1,2,3,4)
insert into history values(2,1,2,3)
insert into history values(3,2,3,4)
insert into history values(4,1,2,2)


select A.* from
push A
inner join
(select a1, a2, a3 from history group by a1, a2, a3 having count(*)>1) B
on A.a1=B.a1 and A.a2=B.a2 and A.a3=B.a3


drop table push
drop table history

[qizhirui]

接点分

[lxysjl]

帮帮我啊，分数不够，谁写出来了再放50分

[fellowcheng]

顶先