CREATE TABLE #t (deptid INT,docID int,docSign VARCHAR(10),docSignDate DATETIME,docReply VARCHAR(10),docReplyDate DATETIME)
INSERT INTO #t
SELECT 1,10,'签收','2010-05-12 00:00:00.000','答复','2010-05-14 00:00:00.000' UNION ALL
SELECT 2,10,'拒收','2010-05-12 00:00:00.000',NULL,NULL UNION ALL
SELECT 3,10,NULL,NULL,NULL,NULL UNION ALL
SELECT 2,11,'签收','2010-05-13 00:00:00.000','退回','2010-05-16 00:00:00.000' UNION ALL
SELECT 3,11,'签收','2010-05-14 00:00:00.000','答复','2010-05-14 00:00:00.000' UNION ALL
SELECT 2,12,'签收','2010-05-14 00:00:00.000',NULL,NULL UNION ALL
SELECT 3,12,'退回','2010-05-14 00:00:00.000',NULL,NULL UNION ALL
SELECT 3,13,NULL,NULL,NULL,NULL UNION ALL
SELECT 1,13,NULL,NULL,NULL,NULL UNION ALL
SELECT 2,14,'签收','2010-05-18 00:00:00.000',NULL,NULL UNION ALL
SELECT 3,14,'签收','2010-05-19 00:00:00.000','答复','2010-05-22 00:00:00.000' UNION ALL
--签收和拒收都当作签收处理,答复和退回当作答复处理
--想要返回下列格式记录docID docSign docReply
10 部分签收 未答复
11 已签收 已答复
12 已签收 未答复
13 未签收 未答复
14 已签收 部分答复
INSERT INTO #t
SELECT 1,10,'签收','2010-05-12 00:00:00.000','答复','2010-05-14 00:00:00.000' UNION ALL
SELECT 2,10,'拒收','2010-05-12 00:00:00.000',NULL,NULL UNION ALL
SELECT 3,10,NULL,NULL,NULL,NULL UNION ALL
SELECT 2,11,'签收','2010-05-13 00:00:00.000','退回','2010-05-16 00:00:00.000' UNION ALL
SELECT 3,11,'签收','2010-05-14 00:00:00.000','答复','2010-05-14 00:00:00.000' UNION ALL
SELECT 2,12,'签收','2010-05-14 00:00:00.000',NULL,NULL UNION ALL
SELECT 3,12,'退回','2010-05-14 00:00:00.000',NULL,NULL UNION ALL
SELECT 3,13,NULL,NULL,NULL,NULL UNION ALL
SELECT 1,13,NULL,NULL,NULL,NULL UNION ALL
SELECT 2,14,'签收','2010-05-18 00:00:00.000',NULL,NULL UNION ALL
SELECT 3,14,'签收','2010-05-19 00:00:00.000','答复','2010-05-22 00:00:00.000' UNION ALL
--签收和拒收都当作签收处理,答复和退回当作答复处理
--想要返回下列格式记录docID docSign docReply
10 部分签收 未答复
11 已签收 已答复
12 已签收 未答复
13 未签收 未答复
14 已签收 部分答复
解决方案 »
- update问题:对所有记录中某个字段的值,取任一个记录,更新为1,其他为0
- 数据库置疑后无法修复。请高手解决!
- 如何批量更新两个相关表的数据???
- 求一SQL語句???????????????
- rsFaqList.AbsolutePage=3 ----------->这一句出错。另外的表则没有问题。
- !!!急!!!SQLserver 2000为什么不能在XPprofession下安装服务器组件??
- 取组中的最大值
- 设计表的字段问题
- ***sql-server存储过程的问题****
- 这个sql怎么写
- drop table @backtable表名能否用变量实现,急
- with查询后的结果集怎么取出来?
;with TT
as(
select docid,
docsign = case when docsign = '签收' then 1
when docsign = '拒收' then 1
when docsign ='退回' then 1 else 0 end
,docreply = case when docreply = '答复' then 1
when docreply = '退回' then 1
else 0 end
from #t)select docid, [docsign] = case when min(docsign) = 0 and max(docsign) = 1 then '部分签收'
when min(docsign) = 1 then '已签收'
when max(docsign) = 0 then '未签收' end,
[docreply] = case when min(docreply) = 0 and max(docreply) = 1 then '部分答复'
when min(docreply) = 1 then '已答复'
when max(docreply) = 0 then '未答复' endfrom TT
group by docid
/*
docid docsign docreply
----------- -------- --------
10 部分签收 部分答复
11 已签收 已答复
12 已签收 未答复
13 未签收 未答复
14 已签收 部分答复(5 行受影响)*/
1 10 签收 2010-05-12 00:00:00.000 答复 2010-05-14 00:00:00.000
2 10 拒收 2010-05-12 00:00:00.000 NULL NULL已经答复了一次啊!
部分答复????
INSERT INTO #t
SELECT 1,10,'签收','2010-05-12 00:00:00.000','答复','2010-05-14 00:00:00.000' UNION ALL
SELECT 2,10,'拒收','2010-05-12 00:00:00.000',NULL,NULL UNION ALL
SELECT 3,10,NULL,NULL,NULL,NULL UNION ALL
SELECT 2,11,'签收','2010-05-13 00:00:00.000','退回','2010-05-16 00:00:00.000' UNION ALL
SELECT 3,11,'签收','2010-05-14 00:00:00.000','答复','2010-05-14 00:00:00.000' UNION ALL
SELECT 2,12,'签收','2010-05-14 00:00:00.000',NULL,NULL UNION ALL
SELECT 3,12,'退回','2010-05-14 00:00:00.000',NULL,NULL UNION ALL
SELECT 3,13,NULL,NULL,NULL,NULL UNION ALL
SELECT 1,13,NULL,NULL,NULL,NULL UNION ALL
SELECT 2,14,'签收','2010-05-18 00:00:00.000',NULL,NULL UNION ALL
SELECT 3,14,'签收','2010-05-19 00:00:00.000','答复','2010-05-22 00:00:00.000'select docid,
docSign=case when not exists(select 1 from #T where docid=a.docid and (docsign='签收' or docsign='拒收')) then '未签收'
when not exists(select 1 from #T where docid=a.docid and (docsign='退回' or docReply is null)) then '已签收'
else '部分签收' end,
docReply=case when not exists(select 1 from #T where docid=a.docid and (docReply='答复' or docReply='退回')) then '未答复'
when not exists(select 1 from #T where docid=a.docid and docReply is null) then '已答复'
else '部分答复' end
from #T a
group by docid--结果:
docid docSign docReply
----------- -------- --------
10 部分签收 部分答复
11 已签收 已答复
12 部分签收 未答复
13 未签收 未答复
14 部分签收 部分答复
---------------------------
楼主结果错了吧?按照你说的,doc为12的是退回和签收,退回应该算未签收。那12就应该是部分签收了。
其他还很多
SELECT 3,12,'退回','2010-05-14 00:00:00.000',NULL,NULL UNION ALL
这条记录初始化错了,应该是
SELECT 3,12,'拒收','2010-05-14 00:00:00.000',NULL,NULL UNION ALL签收状态只有3种:拒收、签收 NULL