求SQL语句,项目中的难题

[panwen516]

表A
id groupid number state
1 1 1 0
2 1 2 0
3 1 3 0
4 1 4 0
5 1 5 0
6 2 1 0
7 2 2 0
8 2 3 0
。。。。
state字段有3个状态0,1,2；
数据都是由groupid这个字段来分组。
外部有个线程不停的在循环读这张表A，找出每组中number最小；state=0 但 state!=2 的id，限制条件是每组中有一个state=1的那么这个组所有的id都不要.number中的编号在每组中都是有序的，可以理解为对应每组中的编号。
如：
字段顺序如上
1 1 1 0
2 1 2 0
3 1 3 0
4 2 1 2
5 2 2 0
6 3 1 1
7 3 2 0
8 3 3 0
9 3 4 0
这张表求出的结果就是
id
1
5
组1中求出number最小（1组中number=1最小）,state=0 and state !=2 ，对应id = 1
组2中求出number最小（2组中number=2最小）,state=0 and state !=2 ，对应id = 5
组3中 因为state包含有1,所以本组不求结果
state 为0是初始状，为1表示动作状，为2表示结束状。
注意：在一个组中，是根据number最小来动作的，也就是说number =1 ,state=0;number=2,state=1或state=2这种情况是不会存在的
只会出现上一个number的state=2的时候，下一个number的state才会出现 =1或=2的状态。

[子陌红尘]

declare @t table(id int,groupid int,number int,state int)
insert into @t
      select 1,1,1,0 
union select 2,1,2,0 
union select 3,1,3,0 
union select 4,2,1,2 
union select 5,2,2,0 
union select 6,3,1,1 
union select 7,3,2,0 
union select 8,3,3,0 
union select 9,3,4,0

select
    t.*
from 
    @t t
where
    not exists(select 1 from @t where groupid=t.groupid and state=1)
    and
    not exists(select 1 from @t where groupid=t.groupid and state=t.state and number<t.number)
    and
    t.state=0

/*
id          groupid     number      state       
----------- ----------- ----------- ----------- 
1           1           1           0
5           2           2           0
*/ 

[liangCK]

-------------------------------------
--  Author : liangCK 梁爱兰
--  Comment: 小梁 爱 兰儿
--  Date   : 2009-06-25 12:56:00
-------------------------------------
 
--> 生成测试数据: @T
DECLARE @T TABLE (id INT,groupid INT,number INT,state INT)
INSERT INTO @T
SELECT 1,1,1,0 UNION ALL
SELECT 2,1,2,0 UNION ALL
SELECT 3,1,3,0 UNION ALL
SELECT 4,2,1,2 UNION ALL
SELECT 5,2,2,0 UNION ALL
SELECT 6,3,1,1 UNION ALL
SELECT 7,3,2,0 UNION ALL
SELECT 8,3,3,0 UNION ALL
SELECT 9,3,4,0

--SQL查询如下:

SELECT id
FROM @T AS A
WHERE NOT EXISTS(
          SELECT * FROM @T
          WHERE groupid = A.groupid AND state = 1
      )
    AND state = 0
    AND id IN(SELECT TOP 1 id 
              FROM (
                  SELECT *
                  FROM @T AS A
                  WHERE NOT EXISTS(
                           SELECT * FROM @T
                           WHERE groupid = A.groupid AND state = 1
                        )
                        AND state = 0
              ) AS T
              WHERE groupid = A.groupid
              ORDER BY number)

/*
id
-----------
1
5

(2 行受影响)
*/

[JonasFeng]

[Quote=引用 3 楼 libin_ftsafe 的回复:]
SQL code
declare @t table(id int,groupid int,number int,state int)
insert into @t
select 1,1,1,0
union select 2,1,2,0
union select 3,1,3,0
union select 4,2,1,2
union select 5,2,2,0
union select 6,3,1,1
union select 7,3,2,0
union select 8,3,3,0
union select 9,3,4,0

select
t.*
from
@t t
where
not exists(select 1 from @t where groupid=t.groupid and stat…
[/Quote]

正解

[liangCK]

-------------------------------------
--  Author : liangCK 梁爱兰
--  Comment: 小梁 爱 兰儿
--  Date   : 2009-06-25 12:56:00
-------------------------------------
 
--> 生成测试数据: @T
DECLARE @T TABLE (id INT,groupid INT,number INT,state INT)
INSERT INTO @T
SELECT 1,1,1,0 UNION ALL
SELECT 2,1,2,0 UNION ALL
SELECT 3,1,3,0 UNION ALL
SELECT 4,2,1,2 UNION ALL
SELECT 5,2,2,0 UNION ALL
SELECT 6,3,1,1 UNION ALL
SELECT 7,3,2,0 UNION ALL
SELECT 8,3,3,0 UNION ALL
SELECT 9,3,4,0

--SQL查询如下:

SELECT MIN(id)
FROM @T AS A
WHERE NOT EXISTS(
          SELECT * FROM @T
          WHERE groupid = A.groupid AND state = 1
      )
    AND state = 0
GROUP BY groupid;

/*
-----------
1
5

(2 行受影响)

*/

[liangCK]

错了.漏了个条件.

[panwen516]

[Quote=引用 3 楼 libin_ftsafe 的回复:]
SQL codedeclare@ttable(idint,groupidint,numberint,stateint)insertinto@tselect1,1,1,0unionselect2,1,2,0unionselect3,1,3,0unionselect4,2,1,2unionselect5,2,2,0unionselect6,3,1,1unionselect7,3,2,0unionselect8,3,3,0unionselect9,3,4,0selectt.*from@ttwherenotexists(select1from@twheregroupid=t.groupidandstate=1)andnotexists(select1from@twheregroupid=t.groupidandstate=t.stateandnumber<t.number)andt.state…
[/Quote]


not exists(select 1 from @t where groupid=t.groupid and state=t.state and number<t.number)
不太理解这句话的意思。能否解释一下。谢谢

[十一月猪]

try:


create table #test ( id int,
groupid int,
number int,
state int )

insert into #test
select 1, 1, 1, 0 union
select 2, 1, 2, 0 union
select 3, 1, 3, 0 union
select 4, 2, 1, 2 union
select 5, 2, 2, 0 union
select 6, 3, 1, 1 union
select 7, 3, 2, 0 union
select 8, 3, 3, 0

select min( id )
from (
select *
from #test
where groupid not in ( select groupid
from #test
where state = 1 ) )aa
where aa.state = 0
and aa.state <> 2
group by groupid

drop table #test