SQL难题

Question

select t2.id
from a as t1 
inner join a as t2 on t1.name='a' and t2.name='c' and t1.name<t2.name and t1.id<t2.id
and (select count(*) from a where id>t1.id and id<t2.id and charindex(name,'b')>0)=1
/*
------
3
12
28
*/

Answer 1

drop table acreate table a(id int ,name varchar(30))
insert into a values(1,'a')
insert into a values(2,'b')
insert into a values(3,'c')
insert into a values(4,'a')
insert into a values(5,'a')
insert into a values(6,'b')
insert into a values(7,'b')
insert into a values(8,'c')
insert into a values(9,'a')
insert into a values(10,'b')
insert into a values(11,'ff')
insert into a values(12,'c')
insert into a values(13,'a')
insert into a values(14,'b')
insert into a values(15,'b')
insert into a values(16,'c')
insert into a values(17,'c')
insert into a values(18,'a')
insert into a values(19,'b')
insert into a values(20,'f')
insert into a values(21,'b')
insert into a values(22,'c')
insert into a values(23,'a')
insert into a values(24,'e')
insert into a values(25,'b')
insert into a values(26,'d')
insert into a values(27,'d')
insert into a values(28,'c') 
insert into a values(29,'a') 
insert into a values(30,'b') 
insert into a values(31,'a') 
insert into a values(32,'c') 
select t2.id
from a as t1 
inner join a as t2 on t1.name='a' and t2.name='c' and t1.name<t2.name and t1.id<t2.id
and (select count(*) from a where id>t1.id and id<t2.id and charindex(name,'b')>0)=1
and (select count(*) from a where id>t1.id and id<t2.id and charindex(name,'ac')>0)=0/*
------
3
12
28
*/

Answer 2

不错不错.
不过,t1.name<t2.name 这个条件可以不要.

Answer 3

嗯，是的，也可以这样：select t2.id
from a as t1 
inner join a as t2 on t1.name='a' and t2.name='c' and t1.id<t2.id
and (select sum(case when charindex(name,'b')>0 then 1 when charindex(name,'ac')>0 then 2 else 0 end)
from a where id>t1.id and id<t2.id )=1

Answer 4

create table a(id int ,name varchar(30)) insert into a values(1,'a') 
insert into a values(2,'b') 
insert into a values(3,'c') 
insert into a values(4,'a') 
insert into a values(5,'a') 
insert into a values(6,'b') 
insert into a values(7,'b') 
insert into a values(8,'c') 
insert into a values(9,'a') 
insert into a values(10,'b') 
insert into a values(11,'ff') 
insert into a values(12,'c') 
insert into a values(13,'a') 
insert into a values(14,'b') 
insert into a values(15,'b') 
insert into a values(16,'c') 
insert into a values(17,'c') 
insert into a values(18,'a') 
insert into a values(19,'b') 
insert into a values(20,'f') 
insert into a values(21,'b') 
insert into a values(22,'c') 
insert into a values(23,'a') 
insert into a values(24,'e') 
insert into a values(25,'b') 
insert into a values(26,'d') 
insert into a values(27,'d') 
insert into a values(28,'c') select id from a t1 where name='c' and
 exists(select 1 from a t2 where name='a' and id<t1.id
  and not exists(select 1 from a where (name='a' or name='c') and id<t2.id and id>t1.id)
  and exists(select 1 from a t3 where name='b' and id<t1.id and id>t2.id
   and not exists(select 1 from a where name='b' and id<t1.id and id>t3.id or name='b' and id>t2.id and id<t3.id)
  )
 )
go
drop table a
/*
id
-----------
3
12
28(3 行受影响)
*/

Answer 5

select t2.id
from a as t1 
inner join a as t2 
on t1.name='a' and t2.name='c' 
and t1.id<t2.id
and (select count(*) from a where id between t1.id and t2.id and name between 'a' and 'b')<3
/*
id          
----------- 
3
12
28（所影响的行数为 3 行）
*/drop table a

Answer 6

insert into a values(1,'a') 
insert into a values(2,'b') 
insert into a values(3,'c') 
insert into a values(4,'a') 
insert into a values(5,'a') 
insert into a values(6,'b') 
insert into a values(7,'f')  ---改这一条看看
insert into a values(8,'c') 
insert into a values(9,'a') 
insert into a values(10,'b') 

Answer 7

select t2.id
from a as t1 
inner join a as t2 
on t1.name='a' and t2.name='c' 
and t1.id<t2.id
and (select count(*) from a where id between t1.id and t2.id and name between 'a' and 'c')<=3

Answer 8

happyflystone加上我后加的数据测你的写法.
insert into a values(29,'a') 
insert into a values(30,'b') 
insert into a values(31,'a') 
insert into a values(32,'c'

Answer 9

qianjin036a的好像也有问题。
加这几条试
insert into a values(29,'a')
insert into a values(30,'b')
insert into a values(31,'a')
insert into a values(32,'c'

Answer 10

应该是等于select t2.id
from a as t1 
inner join a as t2 
on t1.name='a' and t2.name='c' 
and t1.id<t2.id 
and (select count(*) from a where id between t1.id and t2.id and name between 'a' and 'c')=3
/*
id          
----------- 
3
12
28（所影响的行数为 3 行）
*/

Answer 11

create table a(id int ,name varchar(30)) insert into a values(1,'a') 
insert into a values(2,'b') 
insert into a values(3,'c') 
insert into a values(4,'a') 
insert into a values(5,'a') 
insert into a values(6,'b') 
insert into a values(7,'b') 
insert into a values(8,'c') 
insert into a values(9,'a') 
insert into a values(10,'b') 
insert into a values(11,'ff') 
insert into a values(12,'c') 
insert into a values(13,'a') 
insert into a values(14,'b') 
insert into a values(15,'b') 
insert into a values(16,'c') 
insert into a values(17,'c') 
insert into a values(18,'a') 
insert into a values(19,'b') 
insert into a values(20,'f') 
insert into a values(21,'b') 
insert into a values(22,'c') 
insert into a values(23,'a') 
insert into a values(24,'e') 
insert into a values(25,'b') 
insert into a values(26,'d') 
insert into a values(27,'d') 
insert into a values(28,'c') 
insert into a values(29,'a') 
insert into a values(30,'b') 
insert into a values(31,'a') 
insert into a values(32,'c')
goselect t2.id
from a as t1 
inner join a as t2 
on t1.name='a' and t2.name='c' 
and t1.id<t2.id 
and (select count(*) from a where id between t1.id and t2.id and name between 'a' and 'c')=3
/*
id          
----------- 
3
12
28（所影响的行数为 3 行）
*/drop table a

Answer 12

那你再加上这几行试试呢：
insert into a values(33,'a') 
insert into a values(34,'a') 
insert into a values(35,'a') 
insert into a values(36,'c') 

Answer 13

再加几条数据呢：
insert into a values(36,'abc')
insert into a values(37,'ab')
insert into a values(38,'c') 
insert into a values(37,'a')
insert into a values(38,'bc') 

Answer 14

create table a(id int ,name varchar(30)) insert into a values(1,'a') 
insert into a values(2,'b') 
insert into a values(3,'c') 
insert into a values(4,'a') 
insert into a values(5,'a') 
insert into a values(6,'b') 
insert into a values(7,'b') 
insert into a values(8,'c') 
insert into a values(9,'a') 
insert into a values(10,'b') 
insert into a values(11,'ff') 
insert into a values(12,'c') 
insert into a values(13,'a') 
insert into a values(14,'b') 
insert into a values(15,'b') 
insert into a values(16,'c') 
insert into a values(17,'c') 
insert into a values(18,'a') 
insert into a values(19,'b') 
insert into a values(20,'f') 
insert into a values(21,'b') 
insert into a values(22,'c') 
insert into a values(23,'a') 
insert into a values(24,'e') 
insert into a values(25,'b') 
insert into a values(26,'d') 
insert into a values(27,'d') 
insert into a values(28,'c') 
insert into a values(29,'a') 
insert into a values(30,'b') 
insert into a values(31,'a') 
insert into a values(32,'c')
insert into a values(33,'a') 
insert into a values(34,'a') 
insert into a values(35,'a') 
insert into a values(36,'c') 
insert into a values(37,'ab') 
insert into a values(38,'c') 
insert into a values(37,'a') 
insert into a values(38,'bc') 
go
select t1id,t2id
from(
select max(t1.id) as t1id,t2.id as t2id
from a as t1 
join a as t2 
on t1.name='a' and t2.name='c' 
and t1.id<t2.id 
group by t2.id) a
where (select  count( name) from a where id between t1id and t2id and name between 'a' and 'c')=3/*
t1id        t2id        
----------- ----------- 
1           3
9           12
23          28（所影响的行数为 3 行）*/drop table a

Answer 15

非常感谢各位牛人。特别是：perfectaction 与 happyflystone 
但是很可惜大家的SQL都不能满足我的要求，其实这是我项目中碰到的问题，只是简化了。
perfectaction ：select t2.id
from a as t1 
inner join a as t2 on t1.name='a' and t2.name='c' and t1.name<t2.name and t1.id<t2.id
and (select count(*) from a where id>t1.id and id<t2.id and charindex(name,'b')>0)=1
and (select count(*) from a where id>t1.id and id<t2.id and charindex(name,'ac')>0)=0
里面用了函数，因为项目中要兼容mysql,sql2000,access（以后还要兼容其他的），所以只能用标准SQL写happyflystone：select t2.id
from a as t1 
inner join a as t2 
on t1.name='a' and t2.name='c' 
and t1.id<t2.id 
and (select count(*) from a where id between t1.id and t2.id and name between 'a' and 'c')=3里面没有用到函数，但是name between 'a' and 'c'不成立，因为项目中对应'a''b''c'的字符串是没有规律的，这样写不能满足我的要求。
前面没有说清楚实在是抱歉，请大家原谅。对了，ID列是自动增长列。

Answer 16

select id from a t1 where name='c' and
 exists(select 1 from a t2 where name='a' and id<t1.id
  and not exists(select 1 from a where (name='a' or name='c') and id<t2.id and id>t1.id)
  and exists(select 1 from a t3 where name='b' and id<t1.id and id>t2.id
   and not exists(select 1 from a where name='b' and id<t1.id and id>t3.id or name='b' and id>t2.id and id<t3.id)
  )
 )
这是标准SQL写法.

Answer 17

set nocount on
create table a(id int ,name varchar(30))insert into a values(1,'a')
insert into a values(2,'b')
insert into a values(3,'cc')
insert into a values(3,'c')
insert into a values(4,'a')
insert into a values(5,'a')
insert into a values(6,'b')
insert into a values(7,'b')
insert into a values(8,'c')
insert into a values(9,'a')
insert into a values(10,'b')
insert into a values(11,'ff')
insert into a values(12,'c')
insert into a values(13,'a')
insert into a values(14,'b')
insert into a values(15,'b')
insert into a values(16,'c')
insert into a values(17,'c')
insert into a values(18,'a')
insert into a values(19,'b')
insert into a values(20,'f')
insert into a values(21,'b')
insert into a values(22,'c')
insert into a values(23,'a')
insert into a values(24,'e')
insert into a values(25,'b')
insert into a values(26,'d')
insert into a values(27,'d')
insert into a values(28,'c') goalter table a add flag int
go
declare @s varchar(100)
set @s=''
update a set  @s=case when charindex(name,@s)>0 or name='a' then '' else @s end + case when name in('a','b','c') then name else '' end,flag=case when @s='abc' then 1 else null end from a
select * from a where flag=1
/*
3 c 1
12 c 1
28 c 1
*/
godrop table a
go
set nocount off
go