select L1,L2 from A group by L1,L2 having count(*)>1
--> 测试数据: #T1 if object_id('tempdb.dbo.#T1') is not null drop table #T1 create table #T1 (L1 int,L2 int) insert into #T1 select 1,2 union all select 1,3 union all select 1,2 union all select 2,3 union all select 3,4 union all select 4,5 union all select 3,3 union all select 4,6 union all select 4,5select * from #T1 group by L1,L2 having count(*)>1 /* L1 L2 1 2 4 5 */select a.* from #T1 as a inner join ( select * from #T1 group by L1,L2 having count(*)>1 ) as b on a.L1=b.L1 and a.L2=b.L2 /* L1 L2 1 2 1 2 4 5 4 5 */
declare @t table(l1 int,l2 int) insert @t select 1,2 insert @t select 1,3 insert @t select 1,2 insert @t select 2,3 insert @t select 3,4 insert @t select 4,5 insert @t select 3,3 insert @t select 4,6 insert @t select 4,5 select * from @t a where exists(select l1,l2 from @t group by l1,l2 having count(*)>1 and l1=a.l1 and l2=a.l2) l1 l2 ----------- ----------- 1 2 1 2 4 5 4 5(4 行受影响)
Select l1,l2 from tb group by l1,l2 having count(*)>1
select * from a , (select L1,L2 from A group by L1,L2 having count(*)>1) b where a.l1 = b.l1 and a.l2 = b.l2
TO LS的高手: "select a.* from [catalog] as a inner join(select * from [catalog] group by book_id,serial_num having count(*)>1)as b on a.book_id=b.book_id and a.serial_num=b.serial_num;"; //没有结果 "select * from [catalog] a where exists(select book_id,serial_num from [catalog] group by book_id,serial_num having count(*)>1 and a.book_id=book_id and a.serial_num=serial_num)"; //没响应 "select * from [catalog] ,(select book_id,serial_num from [catalog] group by book_id,serial_num having count(*)>1) b where a.book_id=b.book_id and a.serial_num=b.serial_num;"; //没结果 麻烦看下
select * from [catalog] group by book_id,serial_num having count(*)>1 这个有结果吗?
if object_id('tb') is not null drop table tb create table tb (L1 int,L2 int) insert into tb select 1,2 union all select 1,3 union all select 1,2 union all select 2,3 union all select 3,4 union all select 4,5 union all select 3,3 union all select 4,6 union all select 4,5select * from tb b where exists (select L1,L2 from tb where b.L1=L1 and b.L2=L2 group by L1,L2 having count(*)>=2) L1 L2 ----------- ----------- 1 2 1 2 4 5 4 5(4 行受影响)
if object_id('tb') is not null drop table tb create table tb (L1 int,L2 int) insert into tb select 1,2 union all select 1,3 union all select 1,2 union all select 2,3 union all select 3,4 union all select 4,5 union all select 3,3 union all select 4,6 union all select 4,5select * from tb b where exists (select L1,L2 from tb where b.L1=L1 and b.L2=L2 group by L1,L2 having count(*)>=2) L1 L2 ----------- ----------- 1 2 1 2 4 5 4 5(4 行受影响)
sql没问题,有问题的话就是你的数据根本没相同的.
Create table tb (l1 int,l2 int) insert tb select 1,2 union all select 1,3 union all select 1,2 union all select 2,3 union all select 3,4 union all select 4,5 union all select 3,3 union all select 4,6 union all select 4,5select a.l1,a.l2 from (select b.l1,b.l2 from tb b group by l1,l2 having count(*)>1) b left join tb a on b.l1=a.l1 and b.l2=a.l2drop table tb go/* l1 l2 ----------- ----------- 1 2 1 2 4 5 4 5(4 行受影响)*/
SQL没问题,你尝试一句"select 1"看有没有问题吧
--加个索引试试 create index ix_01 on [catalog](book_id,serial_num)
HEROWANG htl258 perfectaction 现在有结果了,并且结果是对了,用的是这个语句:select book_id,serial_num from [catalog] b where exists (select book_id,serial_num from [catalog] where b.book_id=book_id and b.serial_num=serial_num group by book_id,serial_num having count(*)>=2)
用的是ACCESS,第一次用这个,不知道这个能加不?
select L1,L2 from A group by L1,L2 having count(*)>1
declare @t table(L1 int,L2 int) insert into @t select 1,2 union all select 1,3 union all select 1,2 union all select 2,3 union all select 3,4 union all select 4,5 union all select 3,3 union all select 4,6 union all select 4,5select a.l1,a.l2 from @t a inner join ( select l1,l2 from @t group by l1,l2 having count(1)>1 ) b on (a.l1=b.l1 and a.l2=b.l2)
--> 测试数据: #T1
if object_id('tempdb.dbo.#T1') is not null drop table #T1
create table #T1 (L1 int,L2 int)
insert into #T1
select 1,2 union all
select 1,3 union all
select 1,2 union all
select 2,3 union all
select 3,4 union all
select 4,5 union all
select 3,3 union all
select 4,6 union all
select 4,5select * from #T1 group by L1,L2 having count(*)>1
/*
L1 L2
1 2
4 5
*/select a.* from #T1 as a inner join
(
select * from #T1 group by L1,L2 having count(*)>1
) as b on a.L1=b.L1 and a.L2=b.L2
/*
L1 L2
1 2
1 2
4 5
4 5
*/
insert @t select 1,2
insert @t select 1,3
insert @t select 1,2
insert @t select 2,3
insert @t select 3,4
insert @t select 4,5
insert @t select 3,3
insert @t select 4,6
insert @t select 4,5
select * from @t a where exists(select l1,l2 from @t group by l1,l2 having count(*)>1 and l1=a.l1 and l2=a.l2)
l1 l2
----------- -----------
1 2
1 2
4 5
4 5(4 行受影响)
Select l1,l2 from tb group by l1,l2 having count(*)>1
from a ,
(select L1,L2 from A group by L1,L2 having count(*)>1) b
where a.l1 = b.l1 and a.l2 = b.l2
"select a.* from [catalog] as a inner join(select * from [catalog] group by book_id,serial_num having count(*)>1)as b on a.book_id=b.book_id and a.serial_num=b.serial_num;";
//没有结果
"select * from [catalog] a where exists(select book_id,serial_num from [catalog] group by book_id,serial_num having count(*)>1 and a.book_id=book_id and a.serial_num=serial_num)";
//没响应
"select * from [catalog] ,(select book_id,serial_num from [catalog] group by book_id,serial_num having count(*)>1) b where a.book_id=b.book_id and a.serial_num=b.serial_num;";
//没结果
麻烦看下
这个有结果吗?
create table tb (L1 int,L2 int)
insert into tb
select 1,2 union all
select 1,3 union all
select 1,2 union all
select 2,3 union all
select 3,4 union all
select 4,5 union all
select 3,3 union all
select 4,6 union all
select 4,5select * from tb b where exists (select L1,L2 from tb where b.L1=L1 and b.L2=L2 group by L1,L2 having count(*)>=2)
L1 L2
----------- -----------
1 2
1 2
4 5
4 5(4 行受影响)
if object_id('tb') is not null drop table tb
create table tb (L1 int,L2 int)
insert into tb
select 1,2 union all
select 1,3 union all
select 1,2 union all
select 2,3 union all
select 3,4 union all
select 4,5 union all
select 3,3 union all
select 4,6 union all
select 4,5select * from tb b where exists (select L1,L2 from tb where b.L1=L1 and b.L2=L2 group by L1,L2 having count(*)>=2)
L1 L2
----------- -----------
1 2
1 2
4 5
4 5(4 行受影响)
insert tb
select 1,2 union all
select 1,3 union all
select 1,2 union all
select 2,3 union all
select 3,4 union all
select 4,5 union all
select 3,3 union all
select 4,6 union all
select 4,5select a.l1,a.l2 from (select b.l1,b.l2 from tb b group by l1,l2 having count(*)>1) b left join tb a
on b.l1=a.l1 and b.l2=a.l2drop table tb
go/*
l1 l2
----------- -----------
1 2
1 2
4 5
4 5(4 行受影响)*/
--加个索引试试
create index ix_01 on [catalog](book_id,serial_num)
htl258
perfectaction
现在有结果了,并且结果是对了,用的是这个语句:select book_id,serial_num from [catalog] b where exists (select book_id,serial_num from [catalog] where b.book_id=book_id and b.serial_num=serial_num group by book_id,serial_num having count(*)>=2)
insert into @t
select 1,2 union all
select 1,3 union all
select 1,2 union all
select 2,3 union all
select 3,4 union all
select 4,5 union all
select 3,3 union all
select 4,6 union all
select 4,5select a.l1,a.l2
from @t a inner join (
select l1,l2
from @t
group by l1,l2
having count(1)>1
) b on (a.l1=b.l1 and a.l2=b.l2)