select b.* from push a, history b where a.a1 = b.a1 and a.a2 = b.a2 and a.a3 = b.a3 group by a.a1,a.a2.a.a3 having count(*)>=2
试试Select A.id,A.a1,A.a2,A.a3,B.content 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
select * into #t from (select * from (select a1,a2,a3 from push union all select a1,a2,a3 from history)T group by a1,a2,a3 having count(*)>2)M select * from #t select * from history where a1 in (select a1 from #t) and a2 in (select a2 from #t) and a3 in (select a3 from #t)
select * into #t from (select * from (select a1,a2,a3 from push union all select a1,a2,a3 from history)T group by a1,a2,a3 having count(*)>2)M select a.* from history a inner join #t b on a.a1=b.a1 and a.a2=b.a2 and a.a3=b.a3
talantlee 如果不用临时表最好不用.存储过程也行.上面的结果多了第二条记录
select a.* from history a inner join (select * from (select a1,a2,a3 from push union all select a1,a2,a3 from history)T group by a1,a2,a3 having count(*)>2)M on a.a1=M.a1 and a.a2=M.a2 and a.a3=M.a3----一條語句實現
写错了一点地方。现在可以了。Select A.id,A.a1,A.a2,A.a3,B.content 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
--建立测试环境 Create Table push(id varchar(10),a1 varchar(10),a2 varchar(10),a3 varchar(10),content varchar(10)) --插入数据 insert into push select '1','2','3','4','新闻' union select '2','1','2','3','时事' select * from push --测试语句
--删除测试环境 --Drop Table push --建立测试环境 Create Table history(id varchar(10),a1 varchar(10),a2 varchar(10),a3 varchar(10),content varchar(10)) --插入数据 insert into history select '1','2','3','4','女性' union select '2','1','2','3','游戏' union select '3','2','3','4','专家' union select '4','1','2','2','高手.' select * from history --测试语句
--删除测试环境 --Drop Table history select a.a1, a.a2, a.a3, b.content from push a, history b where a.a1 = b.a1 and a.a2 = b.a2 and a.a3 = b.a3 and convert(char(10),b.a1)+convert(char(10),b.a2)+convert(char(10),b.a3) in (select convert(char(10),a1)+convert(char(10),a2)+convert(char(10),a3) from history group by convert(char(10),a1)+convert(char(10),a2)+convert(char(10),a3) having count(*)>=2)--删除测试环境 Drop Table push--删除测试环境 Drop Table history /*a1 a2 a3 content ---------- ---------- ---------- ---------- 2 3 4 女性 2 3 4 专家(所影响的行数为 2 行) */
再改下 Select B.id,A.a1,A.a2,A.a3,B.content 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
--建立测试环境 Create table push (id Int, a1 Int, a2 Int, a3 Int, content Nvarchar(50))Create table history (id Int, a1 Int, a2 Int, a3 Int, content Nvarchar(50)) GO --插入数据 Insert push Values(1, 2, 3, 4, N'新闻') Insert push Values(2, 1, 2, 3, N'时事')Insert history Values(1, 2, 3, 4, N'女性') Insert history Values(2, 1, 2, 3, N'游戏') Insert history Values(3, 2, 3, 4, N'专家') Insert history Values(4, 1, 2, 2, N'高手') GO --测试 Select B.id,A.a1,A.a2,A.a3,B.content 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 --删除测试环境 Drop table push,history --结果 GO /* id a1 a2 a3 content 1 2 3 4 女性 3 2 3 4 专家 */
Select B.* from (Select * from push C Where Exists(Select * from history Where a1=C.a1 And a2=C.a2 And a3=C.a3 Having Count(*) >=2 ) ) A , history B where A.a1=B.a1 And A.a2=B.a2 And A.a3=B.a3
如果a1,a2,a3是NULL,对应的history的a1,a2,a3也是NULL。Select B.id,A.a1,A.a2,A.a3,B.content 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 这个语句不会将a1,a2,a3是NULL的记录查询出来。
得到前十条,并且去掉Null,楼主试下这个语句。 Select B.id,A.a1,A.a2,A.a3,B.content 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 (Select * from history C Where Exists (Select * from history Where a1=C.a1 And a2=C.a2 And a3=C.a3 And id>C.id Having Count(*)<10)) B On A.a1=B.a1 And A.a2=B.a2 And A.a3=B.a3
group by a.a1,a.a2.a.a3 having count(*)>=2
(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
: talantlee(小心傷風感冒...(身體好,學習自然好)) ( ) 信誉:100 找到记录.但字段不是HISTORY里面所有的字段,回复人: comerliang(天地良心) ( ) 信誉:100 出错
group by a1,a2,a3 having count(*)>2)M
select * from #t
select * from history where a1 in (select a1 from #t) and a2 in (select a2 from #t) and a3 in (select a3 from #t)
group by a1,a2,a3 having count(*)>2)M
select a.* from history a inner join #t b on a.a1=b.a1 and a.a2=b.a2 and a.a3=b.a3
group by a1,a2,a3 having count(*)>2)M on a.a1=M.a1 and a.a2=M.a2 and a.a3=M.a3----一條語句實現
(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
Create Table push(id varchar(10),a1 varchar(10),a2 varchar(10),a3 varchar(10),content varchar(10))
--插入数据
insert into push
select '1','2','3','4','新闻' union
select '2','1','2','3','时事'
select * from push
--测试语句
--删除测试环境
--Drop Table push
--建立测试环境
Create Table history(id varchar(10),a1 varchar(10),a2 varchar(10),a3 varchar(10),content varchar(10))
--插入数据
insert into history
select '1','2','3','4','女性' union
select '2','1','2','3','游戏' union
select '3','2','3','4','专家' union
select '4','1','2','2','高手.'
select * from history
--测试语句
--删除测试环境
--Drop Table history
select a.a1, a.a2, a.a3, b.content from push a, history b where a.a1 = b.a1 and a.a2 = b.a2 and a.a3 = b.a3
and convert(char(10),b.a1)+convert(char(10),b.a2)+convert(char(10),b.a3) in
(select convert(char(10),a1)+convert(char(10),a2)+convert(char(10),a3) from history
group by convert(char(10),a1)+convert(char(10),a2)+convert(char(10),a3) having count(*)>=2)--删除测试环境
Drop Table push--删除测试环境
Drop Table history
/*a1 a2 a3 content
---------- ---------- ---------- ----------
2 3 4 女性
2 3 4 专家(所影响的行数为 2 行)
*/
Select B.id,A.a1,A.a2,A.a3,B.content 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
--建立测试环境
Create table push
(id Int,
a1 Int,
a2 Int,
a3 Int,
content Nvarchar(50))Create table history
(id Int,
a1 Int,
a2 Int,
a3 Int,
content Nvarchar(50))
GO
--插入数据
Insert push Values(1, 2, 3, 4, N'新闻')
Insert push Values(2, 1, 2, 3, N'时事')Insert history Values(1, 2, 3, 4, N'女性')
Insert history Values(2, 1, 2, 3, N'游戏')
Insert history Values(3, 2, 3, 4, N'专家')
Insert history Values(4, 1, 2, 2, N'高手')
GO
--测试
Select B.id,A.a1,A.a2,A.a3,B.content 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
--删除测试环境
Drop table push,history
--结果
GO
/*
id a1 a2 a3 content
1 2 3 4 女性
3 2 3 4 专家
*/
(Select * from push C Where Exists(Select * from history Where a1=C.a1 And a2=C.a2 And a3=C.a3 Having Count(*) >=2 ) ) A
, history B
where
A.a1=B.a1 And A.a2=B.a2 And A.a3=B.a3
(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
这个语句不会将a1,a2,a3是NULL的记录查询出来。
Select B.id,A.a1,A.a2,A.a3,B.content 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 (Select * from history C Where Exists (Select * from history Where a1=C.a1 And a2=C.a2 And a3=C.a3 And id>C.id Having Count(*)<10)) B
On A.a1=B.a1 And A.a2=B.a2 And A.a3=B.a3