在“WITH”语句中有使用变量的方法吗？
在“WITH”语句中有使用变量的方法吗？
提问于 2019-12-24 19:59:28
我有这样的“WITH”声明，它可以提取一周中每一天的数据。代码可以工作，但是当我修改变量时，更改会变得很乏味，因为我必须遍历每个代码块。很多代码都是重复的。我想知道是否有一种方法将重复的代码放入变量中，然后只重复变量？
编辑:非常感谢你的回答！我没想到会得到一个。
WITH
cteMonday (CTEdate, CTEtime, DayAverage) AS
(
select CONVERT(VARCHAR(10), [Datetime], 120) AS Date --extract only the time portion
,CONVERT(VARCHAR(5), [Datetime], 108) AS Time --Extract only the date portion
,[High] - [Low] AS hilow
from [Trading].[dbo].MicroES
where DATEPART(dw, Datetime) = 2
AND CONVERT(VARCHAR(5), [Datetime], 108) = '14:00'
--AND [Date] >= '2016-07-27'
),
cteTuesday (CTEdate, CTEtime,DayAverage) AS
(
select CONVERT(VARCHAR(10), [Datetime], 120) AS Date
,CONVERT(VARCHAR(5), [Datetime], 108) AS Time --extract only the time portion
,[High] - [Low] AS hilow --Extract only the date portion
from [Trading].[dbo].MicroES
where DATEPART(dw, Datetime) = 3
--AND [Date] >= '2018-07-27'
--AND [Date] >= '2016-07-27'
),
cteWednesday (CTEdate, CTEtime, DayAverage) AS
(
select CONVERT(VARCHAR(10), [Datetime], 120) AS Date
,CONVERT(VARCHAR(5), [Datetime], 108) --extract only the time portion
,[High] - [Low] AS hilow --Extract only the date portion
from [Trading].[dbo].MicroES
where DATEPART(dw, Datetime) = 4
--AND [Date] >= '2018-07-27'
--AND [Date] >= '2016-07-27'
),
cteThursday (CTEdate, CTEtime, DayAverage) AS
(
select CONVERT(VARCHAR(10), [Datetime], 120) AS Date
,CONVERT(VARCHAR(5), [Datetime], 108) AS Time --extract only the time portion
,[High] - [Low] AS hilow --Extract only the date portion
from [Trading].[dbo].MicroES
where DATEPART(dw, Datetime) = 5
--AND [Date] >= '2018-07-27'
--AND [Date] >= '2016-07-27'
),
cteFriday (CTEdate, CTEtime, DayAverage) AS
(
select CONVERT(VARCHAR(10), [Datetime], 120) AS Date
,CONVERT(VARCHAR(5), [Datetime], 108) AS Time --extract only the time portion
,[High] - [Low] AS hilow --Extract only the date portion
from [Trading].[dbo].MicroES
where DATEPART(dw, Datetime) = 6
AND CONVERT(VARCHAR(5), [Datetime], 108) = '14:00'
--AND [Date] >= '2016-07-27'
)
select
'Monday' AS 'Day',
AVG(DayAverage) AS 'DayAverage',
MIN(DayAverage) as min_day,
MAX(DayAverage) as max_day,
COUNT(DayAverage) as 'Days'
from cteMonday
UNION ALL
select
'Tuesday' AS 'Day',
AVG(DayAverage) AS DayAverage,
MIN(DayAverage) as min_day,
MAX(DayAverage) as max_day,
COUNT(DayAverage) as 'Days'
from cteTuesday
UNION ALL
select
'Wednesday' AS 'Day',
AVG(DayAverage) AS 'DayAverage',
MIN(DayAverage) as min_day,
MAX(DayAverage) as max_day,
COUNT(DayAverage) as 'Days'
from cteWednesday
UNION ALL
select
'Thursday' AS 'Day',
AVG(DayAverage) AS 'DayAverage',
MIN(DayAverage) as min_day,
MAX(DayAverage) as max_day,
COUNT(DayAverage) as 'Days'
from cteThursday
UNION ALL
select
'Friday' AS 'Day',
AVG(DayAverage) AS 'DayAverage',
MIN(DayAverage) as min_day,
MAX(DayAverage) as max_day,
COUNT(DayAverage) as 'Days'
from cteFriday回答 1
Stack Overflow用户
回答已采纳
发布于 2019-12-24 20:14:09
这是太长的评论，但你不能重写整个声明如下？
USE Trading;
GO
SELECT DATENAME(WEEKDAY, M.[DateTime]),
AVG(M.[High] - M.[Low]) AS DayAverage,
MIN(M.[High] - M.[Low]) as min_day,
MAX(M.[High] - M.[Low]) as max_day,
COUNT(M.[High] - M.[Low]) as Days
FROM dbo.MicroES M
WHERE (DATEPART(weekday,M.[Datetime]) IN (3,4,5)
OR (DATEPART(weekday,M.[Datetime]) IN (2,6) AND CONVERT(time, M.[DateTime]) = '14:00')) --Courtesy of LukStorm's correction
GROUP BY DATENAME(WEEKDAY, M.[DateTime]);由于[Datetime]在WHERE中的函数，这仍然执行得很差，我为它们创建了计算列。但是，它肯定会比5次扫描表MicroES和相同的非SARGable子句更好。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/59472993
复制相关文章
相似问题
腾讯云开发者
Copyright © 2013 - 2026 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有
深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 
粤公网安备44030502008569号
腾讯云计算（北京）有限责任公司 京ICP证150476号 | 京ICP备11018762号