select a.*
from t a
join (select b,count(1) from t group by b hanving count(1)>1)b
on a.b=b.b
解决方案 »
- insert into HBTrigger_EnglishNews select id, 'Update', @updateFields from Inser
- 跪求SQL语句,4表连接查询!
- Microsoft OLE DB Provider for SQL Server (0x80040E07)
- 高手指教:同时访问Sql 2005和2000
- 生成SQL脚本的问题
- 表的行和列转换问题
- 一个入门级的问题!~
- 请帮忙给一个简单的sql语句指出错误在哪 谢谢,
- 求1简单sSQL,对的立刻给分!
- 应用程序角色问题
- 有人要写一个图书销的管理系统,不知道要多少钱好?
- 同时访问多个数据源,请邹老大等人相助.
(a int, b int)
insert into #select 1, 2 union all
select 2, 3 union all
select 3, 3 union all
select 4, 2 union all
select 5, 5 union all
select 6, 5 union all
select 7, 5 union all
select 8, 6 select * from #
where b in
(
select a.b
from # a
where exists(select 1 from # where a.b=b and a=a.a+1)
)/*(所影响的行数为 8 行)a b
----------- -----------
2 3
3 3
5 5
6 5
7 5(所影响的行数为 5 行)
*/
上面这个不行,请用这个
create table #
(a int, b int)
insert into #select 1, 2 union all
select 2, 3 union all
select 3, 3 union all
select 4, 2 union all
select 5, 5 union all
select 6, 5 union all
select 7, 5 union all
select 8, 6 union all
select 9, 5 union all
select 10, 1 union all
select 11, 1 union all
select 12, 5 select *
from # a
where exists(select 1 from # where a.b=b and (a.a=a-1 or a.a=a+1))/*
a b
----------- -----------
2 3
3 3
5 5
6 5
7 5
10 1
11 1(所影响的行数为 7 行)
*--drop table #
create table #
(a int, b int)
insert into #select 1, 2 union all
select 2, 3 union all
select 3, 3 union all
select 4, 2 union all
select 5, 5 union all
select 6, 5 union all
select 7, 5 union all
select 8, 6 select * from # p where exists
(select q.a from # q where (p.a=q.a+1 or p.a=q.a-1) and p.b=q.b)
/*
a b
----------- -----------
2 3
3 3
5 5
6 5
7 5(5 row(s) affected)
*/