22,209
社区成员
发帖
与我相关
我的任务
分享
SELECT a.*,
CASE
WHEN b.dt IS NOT NULL THEN
ROUND(CONVERT(FLOAT, (a.uv - b.uv) / b.uv), 2)
ELSE
0
END AS 日环比,
CASE
WHEN c.dt IS NOT NULL THEN
ROUND(CONVERT(FLOAT, (a.uv - c.uv) / c.uv), 2)
ELSE
0
END AS 周环比
FROM
(
SELECT dt,
ROUND(SUM(CAST((uv) AS FLOAT)), 2) AS uv
FROM table_uv
WHERE DATEDIFF(dd, dt, CONVERT(VARCHAR, GETDATE(), 112)) <= 7
GROUP BY dt
) a
LEFT JOIN
(
SELECT dt,
ROUND(SUM(CAST((uv) AS FLOAT)), 2) AS uv
FROM table_uv
WHERE 1 <= DATEDIFF(dd, dt, CONVERT(VARCHAR, GETDATE(), 112))
AND DATEDIFF(dd, dt, CONVERT(VARCHAR, GETDATE(), 112)) < 8
GROUP BY dt
) b
ON DATEDIFF(DAY, b.dt, a.dt) = 1
LEFT JOIN
(
SELECT dt,
ROUND(SUM(CAST((uv) AS FLOAT)), 2) AS uv
FROM table_uv
WHERE 7 <= DATEDIFF(dd, dt, CONVERT(VARCHAR, GETDATE(), 112))
AND DATEDIFF(dd, dt, CONVERT(VARCHAR, GETDATE(), 112)) <= 14
GROUP BY dt
) c
ON DATEDIFF(DAY, c.dt, a.dt) = 7;
21#那个上半部分代码是测试用的数据,然后测试数据对应的结果也出来了,这个测试结果对吗?如果对就执行上边的这段代码,如果上边这段select代码还出不来结果,那楼主把你真实数据的一部分,以及这部分对应的结果写出来,再写语句。如果上边这个测试用的数据和结果都不对,那楼主按照这个测试数据给出正确的结果,再改这个语句……[/quote]
好的~~ 太感谢斑斑了~~ SELECT a.*,
CASE
WHEN b.dt IS NOT NULL THEN
ROUND(CONVERT(FLOAT, (a.uv - b.uv) / b.uv), 2)
ELSE
0
END AS 日环比,
CASE
WHEN c.dt IS NOT NULL THEN
ROUND(CONVERT(FLOAT, (a.uv - c.uv) / c.uv), 2)
ELSE
0
END AS 周环比
FROM
(
SELECT dt,
ROUND(SUM(CAST((uv) AS FLOAT)), 2) AS uv
FROM table_uv
WHERE DATEDIFF(dd, dt, CONVERT(VARCHAR, GETDATE(), 112)) <= 7
GROUP BY dt
) a
LEFT JOIN
(
SELECT dt,
ROUND(SUM(CAST((uv) AS FLOAT)), 2) AS uv
FROM table_uv
WHERE 1 <= DATEDIFF(dd, dt, CONVERT(VARCHAR, GETDATE(), 112))
AND DATEDIFF(dd, dt, CONVERT(VARCHAR, GETDATE(), 112)) < 8
GROUP BY dt
) b
ON DATEDIFF(DAY, b.dt, a.dt) = 1
LEFT JOIN
(
SELECT dt,
ROUND(SUM(CAST((uv) AS FLOAT)), 2) AS uv
FROM table_uv
WHERE 7 <= DATEDIFF(dd, dt, CONVERT(VARCHAR, GETDATE(), 112))
AND DATEDIFF(dd, dt, CONVERT(VARCHAR, GETDATE(), 112)) <= 14
GROUP BY dt
) c
ON DATEDIFF(DAY, c.dt, a.dt) = 7;
21#那个上半部分代码是测试用的数据,然后测试数据对应的结果也出来了,这个测试结果对吗?如果对就执行上边的这段代码,如果上边这段select代码还出不来结果,那楼主把你真实数据的一部分,以及这部分对应的结果写出来,再写语句。如果上边这个测试用的数据和结果都不对,那楼主按照这个测试数据给出正确的结果,再改这个语句……--测试数据
if not object_id(N'table_uv') is null
drop table table_uv
Go
Create table table_uv([dt] Date,[uv] int)
Insert table_uv
select '2018-04-27',80 union all
select '2018-04-26',70 union ALL
select '2018-04-25',40 union all
select '2018-04-24',40 union all
select '2018-04-23',30 union ALL
select '2018-04-22',25 union all
select '2018-04-21',20 union all
select '2018-04-20',10
Go
--测试数据结束
SELECT a.*,
CASE
WHEN b.dt IS NOT NULL THEN
ROUND(CONVERT(FLOAT, (a.uv - b.uv) / b.uv), 2)
ELSE
0
END AS 日环比,
CASE
WHEN c.dt IS NOT NULL THEN
ROUND(CONVERT(FLOAT, (a.uv - c.uv) / c.uv), 2)
ELSE
0
END AS 周环比
FROM
(
SELECT dt,
ROUND(SUM(CAST((uv) AS FLOAT)), 2) AS uv
FROM table_uv
WHERE DATEDIFF(dd, dt, CONVERT(VARCHAR, GETDATE(), 112)) <= 7
GROUP BY dt
) a
LEFT JOIN
(
SELECT dt,
ROUND(SUM(CAST((uv) AS FLOAT)), 2) AS uv
FROM table_uv
WHERE 1 <= DATEDIFF(dd, dt, CONVERT(VARCHAR, GETDATE(), 112))
AND DATEDIFF(dd, dt, CONVERT(VARCHAR, GETDATE(), 112)) < 8
GROUP BY dt
) b
ON DATEDIFF(DAY, b.dt, a.dt) = 1
LEFT JOIN
(
SELECT dt,
ROUND(SUM(CAST((uv) AS FLOAT)), 2) AS uv
FROM table_uv
WHERE 7 <= DATEDIFF(dd, dt, CONVERT(VARCHAR, GETDATE(), 112))
AND DATEDIFF(dd, dt, CONVERT(VARCHAR, GETDATE(), 112)) <= 14
GROUP BY dt
) c
ON DATEDIFF(DAY, c.dt, a.dt) = 7;
SELECT a.*,
CASE
WHEN b.dt IS NOT NULL THEN
CONVERT(DECIMAL(18, 2), (a.uv - b.uv)) / b.uv
ELSE
0
END AS 日环比,
CASE
WHEN c.dt IS NOT NULL THEN
CONVERT(DECIMAL(18, 2), (a.uv - c.uv)) / c.uv
ELSE
0
END AS 周环比
FROM
(
SELECT dt,
cast(uv as bigint) as uv
FROM table_uv
WHERE DATEDIFF(dd, dt, CONVERT(VARCHAR, GETDATE(), 112)) <= 7
) a
LEFT JOIN
(
SELECT dt,
cast(uv as bigint) as uv
FROM table_uv
WHERE 1 <= DATEDIFF(dd, dt, CONVERT(VARCHAR, GETDATE(), 112))
AND DATEDIFF(dd, dt, CONVERT(VARCHAR, GETDATE(), 112)) < 8
) b
ON DATEDIFF(DAY, b.dt, a.dt) = 1
LEFT JOIN
(
SELECT dt,
cast(uv as bigint) as uv
FROM table_uv
WHERE 7 < DATEDIFF(dd, dt, CONVERT(VARCHAR, GETDATE(), 112))
AND DATEDIFF(dd, dt, CONVERT(VARCHAR, GETDATE(), 112)) <= 14
) c
ON DATEDIFF(DAY, c.dt, a.dt) = 7
--测试数据
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([dt] Date,[uv] int)
Insert #T
select '2018-01-08',100 union all
select '2018-01-07',80 union all
select '2018-01-06',70 union all
select '2018-01-05',40 union all
select '2018-01-04',40 union all
select '2018-01-03',30 union all
select '2018-01-02',20 union all
select '2018-01-01',10
Go
--测试数据结束
SELECT a.*,
CASE
WHEN b.dt IS NOT NULL THEN
CONVERT(DECIMAL(5, 2), (a.uv - b.uv)) / b.uv
ELSE
0
END AS 日环比,
CASE
WHEN c.dt IS NOT NULL THEN
CONVERT(DECIMAL(5, 2), (a.uv - c.uv)) / c.uv
ELSE
0
END AS 周环比
FROM #T a
LEFT JOIN #T b
ON DATEDIFF(DAY, b.dt, a.dt) = 1
LEFT JOIN #T c
ON DATEDIFF(DAY, c.dt, a.dt) = 7;
;WITH b
AS (SELECT dt,
uv
FROM table_uv
WHERE DATEDIFF(dd, dt, CONVERT(VARCHAR, GETDATE(), 112)) <= 14
)
SELECT dt,
uv,
(uv - duv) / luv AS rihuanbi,
(uv - wuv) / uv AS zhouhuanbi
FROM
(
SELECT dt,
uv,
(
SELECT TOP 1 uv FROM b WHERE DATEDIFF(DAY, b.dt, dt) = 1
) AS duv,
(
SELECT TOP 1 uv FROM b WHERE DATEDIFF(DAY, b.dt, dt) = 7
) AS wuv
FROM b
) t
WHERE DATEDIFF(dd, dt, CONVERT(VARCHAR, GETDATE(), 112)) <= 7;
后边两个得给出具体表结构和数据了