-------------------------------------
-- 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 行受影响)*/
-- 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 行受影响)*/
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,0select
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
*/
-- 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 行受影响)
*/
不太理解这句话的意思。能否解释一下。谢谢
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 groupiddrop table #test