，sql中某列日期存在空值则取空值如果不存在空值，则取最大日期
求助，sql中某列日期存在空值则取空值如果不存在空值，则取最大日期
求助：如题
在sql中某列日期存在空值则取空值如果不存在空值，则取最大日期；
实例数据如下
得到如下结果
其实意思就是 从销售订单开始，生产任务单， 采购订单，入库时间 四个阶段都有完成的节点，统计销售订单各个环节所需时间，只要某一时间点无时间信息就是空，有时间则取最大时间； 多行数据并成一列！
------解决思路----------------------
简化结果后的语句，看看是不是你想要的。
------解决思路----------------------
------解决思路----------------------
求助：如题
在sql中某列日期存在空值则取空值如果不存在空值，则取最大日期；
实例数据如下
FBillno FDate FCheckdate WorkNo wfdate wfcheckdate fnumber poorderNo Pfdate Pfcheckdate
GS15001 2015-01-02 2015-01-03 W201501 2015-01-04 2015-01-10 1.01.0003 PO201501 2015-01-15 2015-01-16
GS15001 2015-01-02 2015-01-03 W201501 2015-01-04 2015-01-10 1.01.0004 PO201501 2015-01-16 2015-01-20
GS15001 2015-01-02 2015-01-03 W201501 2015-01-04 2015-01-10 1.01.0005 PO201501 null null
GS15001 2015-01-02 2015-01-03 W201501 2015-01-04 2015-01-10 1.01.0006 PO201501 2015-01-18 2015-01-22
GS15002 2015-01-02 2015-01-03 W201501 2015-01-04 2015-01-10 1.01.0007 PO201501 2015-01-15 2015-01-16
GS15002 2015-01-02 2015-01-03 W201501 2015-01-04 2015-01-10 1.01.0007 PO201501 2015-01-15 2015-01-20
得到如下结果
FBillno FDate FCheckdate WorkNo wfdate wfcheckdate fnumber poorderNo Pfdate Pfcheckdate
GS15001 2015-01-02 2015-01-03 W201501 2015-01-04 2015-01-10 1.01.0003 PO201501 null null
GS15002 2015-01-02 2015-01-03 W201501 2015-01-04 2015-01-10 1.01.0007 PO201501 2015-01-15 2015-01-20
其实意思就是 从销售订单开始，生产任务单， 采购订单，入库时间 四个阶段都有完成的节点，统计销售订单各个环节所需时间，只要某一时间点无时间信息就是空，有时间则取最大时间； 多行数据并成一列！
------解决思路----------------------
简化结果后的语句，看看是不是你想要的。
with table1 as
(
select 'GS15001' FBillno, '2015-01-15' Pfdate, '2015-01-16' Pfcheckdate union all
select 'GS15001' FBillno, '2015-01-16' Pfdate, '2015-01-20' Pfcheckdate union all
select 'GS15001' FBillno, null Pfdate, null Pfcheckdate union all
select 'GS15002' FBillno, '2015-01-15' Pfdate, '2015-01-16' Pfcheckdate union all
select 'GS15002' FBillno, '2015-01-15' Pfdate, '2015-01-20' Pfcheckdate
)
select FBillno
, case when MAX(case when pfdate is null then '2099-12-31' else Pfdate end) = '2099-12-31' then null else MAX(case when pfdate is null then '2099-12-31' else Pfdate end) end Pfdate
, case when MAX(case when Pfcheckdate is null then '2099-12-31' else Pfcheckdate end) = '2099-12-31' then null else MAX(case when Pfcheckdate is null then '2099-12-31' else Pfcheckdate end) end Pfdate
from table1
group by FBillno
------解决思路----------------------
with cte(FBillno,FDate,FCheckdate,WorkNo,wfdate,wfcheckdate,fnumber,poorderNo,Pfdate,Pfcheckdate) as
(
select 'GS15001','2015-01-02','2015-01-03','W201501','2015-01-04','2015-01-10','1.01.0003','PO201501','2015-01-15','2015-01-16'
union all select 'GS15001','2015-01-02','2015-01-03','W201501','2015-01-04','2015-01-10','1.01.0004','PO201501','2015-01-16','2015-01-20'
union all select 'GS15001','2015-01-02','2015-01-03','W201501','2015-01-04','2015-01-10','1.01.0005','PO201501',null,null
union all select 'GS15001','2015-01-02','2015-01-03','W201501','2015-01-04','2015-01-10','1.01.0006','PO201501','2015-01-18','2015-01-22'
union all select 'GS15002','2015-01-02','2015-01-03','W201501','2015-01-04','2015-01-10','1.01.0007','PO201501','2015-01-15','2015-01-16'
union all select 'GS15002','2015-01-02','2015-01-03','W201501','2015-01-04','2015-01-10','1.01.0007','PO201501','2015-01-15','2015-01-20'
)
,cte1 as
(
select row_number()over(partition by FBillno,FDate,FCheckdate,WorkNo,wfdate,wfcheckdate,poorderNo order by (case when Pfdate is null then cast('9999-12-31' as datetime) else cast(Pfdate as datetime) end) desc) as Pfdate_Number
,row_number()over(partition by FBillno,FDate,FCheckdate,WorkNo,wfdate,wfcheckdate,poorderNo order by (case when Pfcheckdate is null then cast('9999-12-31' as datetime) else cast(Pfcheckdate as datetime) end) desc) as Pfcheckdate_Number
,*
from cte
)
select FBillno,FDate,FCheckdate,WorkNo,wfdate,wfcheckdate,MIN(fnumber) as fnumber,poorderNo,MAX(case when Pfdate_Number=1 then Pfdate end ) as Pfdate,MAX(case when Pfcheckdate_Number=1 then Pfcheckdate end) as Pfcheckdate
from cte1
group by FBillno,FDate,FCheckdate,WorkNo,wfdate,wfcheckdate,poorderNo
/**
FBillno FDate FCheckdate WorkNo wfdate wfcheckdate fnumber poorderNo Pfdate Pfcheckdate
------- ---------- ---------- ------- ---------- ----------- --------- --------- ---------- -----------
GS15001 2015-01-02 2015-01-03 W201501 2015-01-04 2015-01-10 1.01.0003 PO201501 NULL NULL
GS15002 2015-01-02 2015-01-03 W201501 2015-01-04 2015-01-10 1.01.0007 PO201501 2015-01-15 2015-01-20
Warning: Null value is eliminated by an aggregate or other SET operation.
(2 行受影响)
**/
------解决思路----------------------
SELECT FBillno,
CASE WHEN COUNT(*)<>COUNT(Pfdate)
THEN NULL
ELSE MAX(Pfdate)
END Pfdate,
... -- 需要NULL的字段都按照上面的方式写
FROM table1
GROUP BY FBillno