SELECT a.id, a.start_time, a.end_time,a.limit_power,DATEDIFF(a.end_time, a.start_time) as diffs,
CASE WHEN DATEDIFF(a.end_time, a.start_time) >= 365 THEN 365
WHEN DATEDIFF( a.end_time,start_time) =0 then 0
ELSE DATEDIFF(a.end_time, a.start_time)
END AS actdays
FROM t_limit_power a
WHERE 2018 BETWEEN DATE_FORMAT(a.start_time, '%Y') and DATE_FORMAT(a.end_time, '%Y')
and DATE_FORMAT(a.start_time, '%Y')!=2018 and DATE_FORMAT(a.end_time, '%Y') !=2018
union all
SELECT a.id, a.start_time, a.end_time,a.limit_power,DATEDIFF(a.end_time, a.start_time) as diffs,
CASE WHEN DATEDIFF(a.end_time, a.start_time) >= 365 THEN 365
WHEN DATEDIFF( a.end_time,start_time) =0 then 0
ELSE DATEDIFF(a.end_time, a.start_time)
END AS actdays
FROM t_limit_power a
WHERE DATE_FORMAT(a.start_time, '%Y')=2018 and DATE_FORMAT(a.end_time, '%Y') =2018
union all
SELECT a.id, a.start_time, a.end_time,a.limit_power,DATEDIFF(a.end_time, a.start_time) as diffs,
DATEDIFF( a.end_time,'2018-1-1') AS actdays
FROM t_limit_power a
WHERE DATE_FORMAT(a.start_time, '%Y')!=2018 and DATE_FORMAT(a.end_time, '%Y') =2018
union all
SELECT a.id, a.start_time, a.end_time,a.limit_power,DATEDIFF(a.end_time, a.start_time) as diffs,
CASE WHEN DATEDIFF(a.end_time, a.start_time) >= 365 THEN 365
WHEN 0<DATEDIFF( a.end_time,start_time) <365 then DATEDIFF( '2018-12-31',a.start_time)
ELSE DATEDIFF(a.end_time, a.start_time)
END AS actdays
FROM t_limit_power a
WHERE DATE_FORMAT(a.start_time, '%Y')=2018 and DATE_FORMAT(a.end_time, '%Y')!=2018
当年的苦-跨年sql
原创
©著作权归作者所有:来自51CTO博客作者小飞侠格鲁帅的原创作品,请联系作者获取转载授权,否则将追究法律责任
提问和评论都可以,用心的回复会被更多人看到
评论
发布评论
相关文章
-
2022跨年烟花代码
2022跨年烟花代码
html css3 前端 jquery css -
2016跨年之夜
过去一年走过的脚步,留下的是艰辛和攀登的苦,未来一年里展望的路,你我仍需带着更高的理
bug 程序人生 时代 加班 -
accessvba代码大全 accessvba编程代码大全
VBA的英文全称是:Visual Basic for Applications。它是一门标准的宏语言。VBA语言不能单独运行,只能被office软件(如:Word、Excel等)所调用。VBA是一种面向对象的解释性语言,通常使用来实现Excel中没有提供的功能、编写自定义函数、实现自动化功能等。VBA对于没有学过计算机语言的人来说会很陌生,会被它陌生的语句所吓倒,从而放弃对它的学习。但是小编可以告
accessvba代码大全 access vba代码大全 excel vba弹窗口置顶 excel vba系统下载