试一下吧 with T as ( select id, TO_DATE(SUBSTR(日期,1,INSTR(日期,'-')-1),'YYYY.MM.DD') AS S, TO_DATE(SUBSTR(日期,INSTR(日期,'-')+1),'YYYY.MM.DD') AS E FROM T ) SELECT ID,SUM(greatest(E-greatest(S,PRE+1)+1,0)) 天数 FROM (SELECT ID,S,E,LAG(E,1,S-1)OVER(PARTITION BY ID ORDER BY E) PRE FROM T) GROUP BY ID ORDER BY ID
WITH t (id,S,E) AS ( --借用#1的拆分 ) ,t1 AS ( SELECT *, ROW_NUMBER() OVER(PARTITION BY id ORDER BY S) rn FROM t ) ,t2 AS (-- 用下一段的 S 剪切前一段的 E,去除重叠段落 SELECT ta.id, ta.S, CASE WHEN tb.S IS NULL THEN ta.E WHEN tb.S > ta.E THEN ta.E ELSE tb.S-1 END E FROM t1 ta LEFT JOIN t1 tb ON tb.id = ta.id AND tb.rn = ta.rn+1 ) SELECT id, SUM(E-S+1) days FROM t2 GROUP BY id 上面求出的是去重后的天数(#2的10);如果要求重复就不计的天数(#2的8),就从t中统计出全部段落的天数、再减去这个去重后的天数。
with T as (
select id,
TO_DATE(SUBSTR(日期,1,INSTR(日期,'-')-1),'YYYY.MM.DD') AS S,
TO_DATE(SUBSTR(日期,INSTR(日期,'-')+1),'YYYY.MM.DD') AS E
FROM T
)
SELECT ID,SUM(greatest(E-greatest(S,PRE+1)+1,0)) 天数
FROM
(SELECT ID,S,E,LAG(E,1,S-1)OVER(PARTITION BY ID ORDER BY E) PRE
FROM T)
GROUP BY ID
ORDER BY ID
--借用#1的拆分
)
,t1 AS (
SELECT *,
ROW_NUMBER() OVER(PARTITION BY id ORDER BY S) rn
FROM t
)
,t2 AS (-- 用下一段的 S 剪切前一段的 E,去除重叠段落
SELECT ta.id,
ta.S,
CASE WHEN tb.S IS NULL THEN
ta.E
WHEN tb.S > ta.E THEN
ta.E
ELSE
tb.S-1
END E
FROM t1 ta
LEFT JOIN t1 tb
ON tb.id = ta.id
AND tb.rn = ta.rn+1
)
SELECT id,
SUM(E-S+1) days
FROM t2
GROUP BY id
上面求出的是去重后的天数(#2的10);如果要求重复就不计的天数(#2的8),就从t中统计出全部段落的天数、再减去这个去重后的天数。
这种计算大部分情况都是在报表准备数据源,这时候可以试试润乾集算报表,其中集成了强化计算的集算引擎,能方便地支持很复杂的集合运算,可以直接完成报表数据源计算,避免编写复杂的SQL。比如这里的运算用集算引擎脚本写出来是这样的: 这里就这个问题给了更详细的说明http://blog.raqsoft.com.cn/?p=357。
如果不是出报表或要采用别的报表工具,也可以把集算报表中的集算引擎单拿出来使用,完成运算后再将结果集传给JAVA或其它报表工具。集算引擎本身也是免费的。