22,209
社区成员
发帖
与我相关
我的任务
分享
--创建一个专门的标量函数,用于获取条件时间
IF OBJECT_ID('dbo.Fun_GetWhereTime') IS NOT NULL
DROP FUNCTION dbo.Fun_GetWhereTime
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date, ,>
-- Description: <Description, ,>
-- =============================================
CREATE FUNCTION dbo.Fun_GetWhereTime()
RETURNS DATETIME
AS
BEGIN
DECLARE @d DATETIME
SET @d=GETDATE()
--每天的1~7点,我要查询昨天早8点到现在的数据
IF DATEPART(hour,@d)<=7
BEGIN
SET @d = CONVERT(CHAR(10),@d-1,120)+' 08:00:00'
END
--每天的8~24点,我要查询今天早8点到现在的数据
ELSE
BEGIN
SET @d = CONVERT(CHAR(10),@d,120)+' 08:00:00'
END
RETURN @d;
END
GO
--查询时直接取标量函数即可
SELECT * FROM [Time]>dbo.Fun_GetWhereTime()
这样代码就简洁多了--加索引
CREATE INDEX ix_JY203_deadcycletime_Time_h ON JY203_deadcycletime([Time],h)
CREATE INDEX ix_JY206_deadcycletime_Time_h ON JY206_deadcycletime([Time],h)
CREATE INDEX ix_JY208_deadcycletime_Time_h ON JY208_deadcycletime([Time],h)
GO
--不能用 DateDiff , 这是大忌
WITH A203 AS (
SELECT SUM(a) * 0.09385 a,
SUM(b) * 0.09385 b,
SUM(c) * 0.09385 c,
SUM(d) * 0.09385 d,
SUM(e) * 0.09385 e,
SUM(f) * 0.09385 f,
SUM(g) * 0.09385 g
FROM JY203_deadcycletime
WHERE [Time]>=CONVERT(CHAR(10), GETDATE(), 120)+' 08:00' AND [Time]<CONVERT(CHAR(10), GETDATE()+1, 120)+'08:00'
AND h = 0
GROUP BY
NUM
),
A206 AS (
SELECT SUM(a) * 0.09385 a,
SUM(b) * 0.09385 b,
SUM(c) * 0.09385 c,
SUM(d) * 0.09385 d,
SUM(e) * 0.09385 e,
SUM(f) * 0.09385 f,
SUM(g) * 0.09385 g
FROM JY206_deadcycletime
WHERE [Time]>=CONVERT(CHAR(10), GETDATE(), 120)+' 08:00' AND [Time]<CONVERT(CHAR(10), GETDATE()+1, 120)+'08:00'
AND h = 0
GROUP BY
NUM
),
A208 AS (
SELECT SUM(a) * 0.09385 a,
SUM(b) * 0.09385 b,
SUM(c) * 0.09385 c,
SUM(d) * 0.09385 d,
SUM(e) * 0.09385 e,
SUM(f) * 0.09385 f,
SUM(g) * 0.09385 g
FROM JY208_deadcycletime
WHERE [Time]>=CONVERT(CHAR(10), GETDATE(), 120)+' 08:00' AND [Time]<CONVERT(CHAR(10), GETDATE()+1, 120)+'08:00'
AND h = 0
GROUP BY
NUM
)
SELECT 'JY203' AS PRESS,
AVG(a) a,
AVG(b) b,
AVG(c) c,
AVG(d) d,
AVG(e) e,
AVG(f) f,
AVG(g) g
FROM A203
WHERE a != 0
AND b != 0
AND c != 0
AND d != 0
AND e != 0
AND f != 0
AND g != 0
union
SELECT 'JY206' AS PRESS,
AVG(a) a,
AVG(b) b,
AVG(c) c,
AVG(d) d,
AVG(e) e,
AVG(f) f,
AVG(g) g
FROM A206
WHERE a != 0
AND b != 0
AND c != 0
AND d != 0
AND e != 0
AND f != 0
AND g != 0
union
SELECT 'JY208' AS PRESS,
AVG(a) a,
AVG(b) b,
AVG(c) c,
AVG(d) d,
AVG(e) e,
AVG(f) f,
AVG(g) g
FROM A208
WHERE a != 0
AND b != 0
AND c != 0
AND d != 0
AND e != 0
AND f != 0
AND g != 0
先试下效果CREATE INDEX ix_JY203_deadcycletime_Time_h ON JY203_deadcycletime([Time]) WHERE h=0
CREATE INDEX ix_JY206_deadcycletime_Time_h ON JY206_deadcycletime([Time]) WHERE h=0
CREATE INDEX ix_JY208_deadcycletime_Time_h ON JY208_deadcycletime([Time]) WHERE h=0