--> liangCK小梁 于2008-10-20
--> 生成测试数据: #T
IF OBJECT_ID('tempdb.dbo.#T') IS NOT NULL DROP TABLE #T
CREATE TABLE #T (month INT,Id INT,No INT,Status INT)
INSERT INTO #T
SELECT 200801,1,1,0 UNION ALL
SELECT 200801,1,2,0 UNION ALL
SELECT 200801,2,1,0 UNION ALL
SELECT 200801,2,2,1 UNION ALL
SELECT 200801,3,1,2 UNION ALL
SELECT 200801,3,2,0 UNION ALL
SELECT 200801,4,1,1 UNION ALL
SELECT 200801,4,2,2--SQL查询如下:SELECT month,Id,
CASE SUM(Status)
WHEN 0
THEN '成功'
WHEN 1
THEN '正在处理'
WHEN 2
THEN '出错'
ELSE '正在处理'
END AS Status
FROM #T
GROUP BY month,Id/*
month Id Status
----------- ----------- --------
200801 1 成功
200801 2 正在处理
200801 3 出错
200801 4 正在处理(4 行受影响)*/
--> 生成测试数据: #T
IF OBJECT_ID('tempdb.dbo.#T') IS NOT NULL DROP TABLE #T
CREATE TABLE #T (month INT,Id INT,No INT,Status INT)
INSERT INTO #T
SELECT 200801,1,1,0 UNION ALL
SELECT 200801,1,2,0 UNION ALL
SELECT 200801,2,1,0 UNION ALL
SELECT 200801,2,2,1 UNION ALL
SELECT 200801,3,1,2 UNION ALL
SELECT 200801,3,2,0 UNION ALL
SELECT 200801,4,1,1 UNION ALL
SELECT 200801,4,2,2--SQL查询如下:SELECT month,Id,
CASE SUM(Status)
WHEN 0
THEN '成功'
WHEN 1
THEN '正在处理'
WHEN 2
THEN '出错'
ELSE '正在处理'
END AS Status
FROM #T
GROUP BY month,Id/*
month Id Status
----------- ----------- --------
200801 1 成功
200801 2 正在处理
200801 3 出错
200801 4 正在处理(4 行受影响)*/
go
create table # (month varchar(10), Id int, No int , Status int)
insert # select '200801',1,1,0
insert # select '200801',1,2,0
insert # select '200801',2,1,0
insert # select '200801',2,2,1
insert # select '200801',3,1,2
insert # select '200801',3,2,0
insert # select '200801',4,1,1
insert # select '200801',4,2,2
SELECT month,Id,
CASE when sum(Status)=0
THEN N'成功'
WHEN sum(Status)=1
THEN N'正在处理'
WHEN sum(Status)=2
THEN N'出错'
ELSE N'正在处理'
END
FROM #
GROUP BY month,Id
/*
month Id
---------- ----------- ----
200801 1 成功
200801 2 正在处理
200801 3 出错
200801 4 正在处理
*/
正确的如下:
status是1代表成功
0代表正在处理
-1代表出错 这样的话,似乎上面的解法就不行了吧?
--> 测试数据: @s
declare @s table ([month] int,Id int,No int,Status int)
insert into @s
select 200801,1,1,1 union all
select 200801,1,2,1 union all
select 200801,2,1,1 union all
select 200801,2,2,0 union all
select 200801,3,1,-1 union all
select 200801,3,2,0 union all
select 200801,4,1,0 union all
select 200801,4,2,-1
select [month],id,status=case when not exists(select 1 from @s where [month]=a.[month] and id=a.id and status<=0)
then '成功' else
case when not exists(select 1 from @s where [month]=a.[month] and id=a.id and status<0) then '正在处理' else '出错' end end
from @s a group by [month],id--结果:
month id status
----------- ----------- --------
200801 1 成功
200801 2 正在处理
200801 3 出错
200801 4 出错
status是1代表成功
0代表正在处理
-1代表出错 如果一组相同的month和Id,对应的status都是1,最终状态就是“成功”
如果status既有0又有-1,最终状态是“正在处理”
else
如果staus存在0,最终状态是“正在处理”
如果staus存在-1,最终状态是“出错”
declare @s table ([month] int,Id int,No int,Status int)
insert into @s
select 200801,1,1,1 union all
select 200801,1,2,1 union all
select 200801,2,1,1 union all
select 200801,2,2,0 union all
select 200801,3,1,-1 union all
select 200801,3,2,0 union all
select 200801,4,1,0 union all
select 200801,4,2,-1 union all
select 200801,5,1,-1
select [month],id,status=case when exists(select 1 from @s where [month]=a.[month] and id=a.id group by [month],id having count(status)=sum([status]))
then '成功'
when exists(select 1 from @s where [month]=a.[month] and id=a.id and status=0) then '正在处理'
when exists(select 1 from @s where [month]=a.[month] and id=a.id and status=-1) then '出错' end
from @s a group by [month],id
--> 生成测试数据: #T
IF OBJECT_ID('tempdb.dbo.#T') IS NOT NULL DROP TABLE #T
CREATE TABLE #T (month INT,Id INT,Status INT)
INSERT INTO #T
SELECT 200809,1,1 UNION ALL
SELECT 200809,1,1 UNION ALL
SELECT 200809,2,0 UNION ALL
SELECT 200809,2,1 UNION ALL
SELECT 200809,3,-1 UNION ALL
SELECT 200809,3,0 UNION ALL
SELECT 200809,4,1 UNION ALL
SELECT 200809,4,-1--SQL查询如下:SELECT month,Id,
CASE SUM(CASE WHEN Status>=0 THEN Status ELSE 0 END)
+SUM(CASE WHEN Status=-1 THEN 2 ELSE 0 END)
WHEN 0
THEN '成功'
WHEN 1
THEN '正在处理'
WHEN 2
THEN '出错'
ELSE '正在处理'
END AS Status
FROM #T
GROUP BY month,Id/*
month Id Status
----------- ----------- --------
200809 1 出错
200809 2 正在处理
200809 3 出错
200809 4 正在处理(4 行受影响)*/
最后的status="成功"
else 存在status =0 并且 存在status =-1
最后的status="正在处理"
else 存在status =0
最后的status="正在处理"
else 存在status=-1
最后的status="出错"
具体逻辑就是上面说的,再写一遍吧:if status 都是1
最后的status="成功"
else 存在status =0 并且 存在status =-1
最后的status="正在处理"
else 存在status =0
最后的status="正在处理"
else 存在status=-1
最后的status="出错"
--> 生成测试数据: #T
IF OBJECT_ID('tempdb.dbo.#T') IS NOT NULL DROP TABLE #T
CREATE TABLE #T (month INT,Id INT,Status INT)
INSERT INTO #T
SELECT 200809,1,1 UNION ALL
SELECT 200809,1,1 UNION ALL
SELECT 200809,2,0 UNION ALL
SELECT 200809,2,1 UNION ALL
SELECT 200809,3,-1 UNION ALL
SELECT 200809,3,0 UNION ALL
SELECT 200809,4,1 UNION ALL
SELECT 200809,4,-1--SQL查询如下:SELECT month,Id,
CASE WHEN COUNT(CASE WHEN Status=1 THEN 1 END)=COUNT(*)
THEN '成功'
WHEN SUM(CASE WHEN Status=0 THEN 1 ELSE 0 END)>0
THEN '正在处理'
WHEN SUM(CASE WHEN Status<0 THEN Status END)<0
THEN '出错'
ELSE '正在处理'
END AS Status
FROM #T
GROUP BY month,Id/*
month Id Status
----------- ----------- --------
200809 1 成功
200809 2 正在处理
200809 3 正在处理
200809 4 出错(4 行受影响)
*/
--那就这样:
--> 测试数据: @s
declare @s table ([month] int,id int,status int)
insert into @s
select 200809,1,1 union all
select 200809,1,1 union all
select 200809,2,0 union all
select 200809,2,1 union all
select 200809,3,-1 union all
select 200809,3,0 union all
select 200809,4,1 union all
select 200809,4,-1
select [month],id,status=case when not exists(select 1 from @s where [month]=a.[month] and id=a.id and status<=0)
then '成功' else
case when not exists(select 1 from @s where [month]=a.[month] and id=a.id and status=0) then '出错' else '正在处理' end end
from @s a group by [month],id