计算账户交易明细前后交易时间的间隔天数

最新推荐文章于 2023-07-25 21:30:26 发布

伊万洛夫拉裤兜

最新推荐文章于 2023-07-25 21:30:26 发布

阅读量1k

点赞数

分类专栏： SQL查询文章标签： SQL 计算天数

本文链接：https://blog.csdn.net/u010317783/article/details/79215727

版权

SQL查询专栏收录该内容

2 篇文章 0 订阅

订阅专栏

表结构

将记录上下两条合并成一行

SQL语句

1.详细记录

SELECT *,DATEDIFF(DAY, K.JYSJ, B.JYSJ) AS TS
FROM (
SELECT B1.YE, B1.JYSJ, ROW_NUMBER() OVER (ORDER BY JYSJ) AS R
FROM (
SELECT A.YE, A.JYSJ
FROM FIXF_SVR_XZHJYMX A
WHERE A.ZHID = '000090000000044076'
AND CAST(LEFT(convert(varchar, A.JYSJ, 120), 10) AS datetime) >= CAST('2016-12-30' AS datetime)
AND CAST(LEFT(convert(varchar, A.JYSJ, 120), 10) AS datetime) <= CAST('2017-12-30' AS datetime)
UNION ALL
SELECT 0, CAST('2017-12-30' AS datetime)
) B1
) K,
(
SELECT B1.YE, B1.JYSJ, ROW_NUMBER() OVER (ORDER BY JYSJ) AS R
FROM (
SELECT A.YE, A.JYSJ
FROM FIXF_SVR_XZHJYMX A
WHERE A.ZHID = '000090000000044076'
AND CAST(LEFT(convert(varchar, A.JYSJ, 120), 10) AS datetime) >= CAST('2016-12-30' AS datetime)
AND CAST(LEFT(convert(varchar, A.JYSJ, 120), 10) AS datetime) <= CAST('2017-12-30' AS datetime)
UNION ALL
SELECT 0, CAST('2017-12-30' AS datetime)
) B1
) B
WHERE K.R = B.R - 1

2.删除余额和天数为0的记录

SELECT Z.YE, Z.TS
FROM (
SELECT K.YE, DATEDIFF(DAY, K.JYSJ, B.JYSJ) AS TS
FROM (
SELECT B1.YE, B1.JYSJ, ROW_NUMBER() OVER (ORDER BY JYSJ) AS R
FROM (
SELECT A.YE, A.JYSJ
FROM FIXF_SVR_XZHJYMX A
WHERE A.ZHID = '000090000000044076'
AND CAST(LEFT(convert(varchar, A.JYSJ, 120), 10) AS datetime) >= CAST('2016-12-30' AS datetime)
AND CAST(LEFT(convert(varchar, A.JYSJ, 120), 10) AS datetime) <= CAST('2017-12-30' AS datetime)
UNION ALL
SELECT 0, CAST('2017-12-30' AS datetime)
) B1
) K,
(
SELECT B1.YE, B1.JYSJ, ROW_NUMBER() OVER (ORDER BY JYSJ) AS R
FROM (
SELECT A.YE, A.JYSJ
FROM FIXF_SVR_XZHJYMX A
WHERE A.ZHID = '000090000000044076'
AND CAST(LEFT(convert(varchar, A.JYSJ, 120), 10) AS datetime) >= CAST('2016-12-30' AS datetime)
AND CAST(LEFT(convert(varchar, A.JYSJ, 120), 10) AS datetime) <= CAST('2017-12-30' AS datetime)
UNION ALL
SELECT 0, CAST('2017-12-30' AS datetime)
) B1
) B
WHERE K.R = B.R - 1
) Z
WHERE Z.TS > 0 AND Z.YE > 0