27,580
社区成员
发帖
与我相关
我的任务
分享
DECLARE @date1 DATE= '2016-06-01'
DECLARE @date2 DATE= '2016-06-02'
SELECT *
FROM ( SELECT DATEADD(DAY, number, @date1) AS 日期 ,
t.*
FROM master.dbo.spt_values a
CROSS APPLY ( SELECT b.number AS 小时 ,
c.number * 5 AS 分钟
FROM master.dbo.spt_values b
CROSS APPLY ( SELECT
number
FROM
master.dbo.spt_values b
WHERE
type = 'P'
) c
WHERE b.number BETWEEN 1 AND 24
AND c.number BETWEEN 1 AND 12
AND b.type = 'P'
) t
WHERE a.type = 'P'
AND a.number <= DATEDIFF(DAY, @date1, @date2)
) t
WHERE NOT EXISTS ( SELECT 1
FROM b
WHERE t.日期 = b.日期
AND t.小时 = b.小时
AND t.分钟 = b.分钟 )
--增加测试表及数据 开始
IF OBJECT_ID('tempdb..#a') IS NOT NULL DROP TABLE #a
IF OBJECT_ID('tempdb..#b') IS NOT NULL DROP TABLE #b
;WITH dt AS (
SELECT sv.number AS d FROM MASTER.dbo.spt_values AS sv WHERE sv.[type]='P' AND sv.number BETWEEN 0 AND 29
),ht AS(
SELECT sv.number AS h FROM MASTER.dbo.spt_values AS sv WHERE sv.[type]='P' AND sv.number BETWEEN 0 AND 23
),mt AS(
SELECT sv.number AS m FROM MASTER.dbo.spt_values AS sv WHERE sv.[type]='P' AND sv.number BETWEEN 0 AND 59
)
SELECT
DATEADD(DAY,dt.d,'2016-06-01') AS [日期]
,ht.h AS [小时]
,mt.m AS [分钟]
INTO #a
FROM dt
CROSS APPLY ht
CROSS APPLY mt
ORDER BY 1,2,3
SELECT *
INTO #b
FROM #a
WHERE [日期]!='2016-06-02'
--增加测试表及数据 开始
SELECT * FROM #a a
WHERE NOT EXISTS (
SELECT * FROM #b b WHERE a.[日期]=b.[日期] and a.[小时]=b.[小时] and a.[分钟]=b.[分钟]
)
ORDER BY 1,2,3
DECLARE @date1 DATE= '2016-06-01'
DECLARE @date2 DATE= '2016-06-02'
SELECT *
FROM ( SELECT DATEADD(DAY, number, @date1) AS 日期 ,
t.*
FROM master.dbo.spt_values a
CROSS APPLY ( SELECT b.number AS 小时 ,
c.number * 5 AS 分钟
FROM master.dbo.spt_values b
CROSS APPLY ( SELECT
number
FROM
master.dbo.spt_values b
WHERE
type = 'P'
) c
WHERE b.number BETWEEN 1 AND 24
AND c.number BETWEEN 1 AND 12
AND b.type = 'P'
) t
WHERE a.type = 'P'
AND a.number <= DATEDIFF(DAY, @date1, @date2)
ORDER BY DATEADD(DAY, number, @date1) ,
t.小时 ,
t.分钟
) t
WHERE NOT EXISTS ( SELECT 1
FROM b
WHERE t.日期 = b.日期
AND t.小时 = b.小时
AND t.分钟 = b.分钟 )
DECLARE @date1 DATE= '2016-06-01'
DECLARE @date2 DATE= '2016-06-02'
SELECT DATEADD(DAY, number, @date1) AS 日期 ,
t.*
FROM master.dbo.spt_values a
CROSS APPLY ( SELECT b.number AS 小时 ,
c.number * 5 AS 分钟
FROM master.dbo.spt_values b
CROSS APPLY ( SELECT number
FROM master.dbo.spt_values b
WHERE type = 'P'
) c
WHERE b.number BETWEEN 1 AND 24
AND c.number BETWEEN 1 AND 12
AND b.type = 'P'
) t
WHERE a.type = 'P'
AND a.number <= DATEDIFF(DAY, @date1, @date2)
ORDER BY DATEADD(DAY, number, @date1) ,
t.小时 ,
t.分钟
;WITH dt AS (
SELECT sv.number AS d FROM MASTER.dbo.spt_values AS sv WHERE sv.[type]='P' AND sv.number BETWEEN 0 AND 29
),ht AS(
SELECT sv.number AS h FROM MASTER.dbo.spt_values AS sv WHERE sv.[type]='P' AND sv.number BETWEEN 0 AND 23
),mt AS(
SELECT sv.number AS m FROM MASTER.dbo.spt_values AS sv WHERE sv.[type]='P' AND sv.number BETWEEN 0 AND 59
)
SELECT
DATEADD(DAY,dt.d,'2016-06-01') AS [日期]
,ht.h AS [小时]
,mt.m AS [分钟]
FROM dt
CROSS APPLY ht
CROSS APPLY mt
ORDER BY 1,2,3
DECLARE @date1 DATE= '2016-06-01'
DECLARE @date2 DATE= '2016-06-02'
SELECT * INTO #ttt --这里直接into 表
FROM ( SELECT DATEADD(DAY, number, @date1) AS 日期 ,
t.*
FROM master.dbo.spt_values a
CROSS APPLY ( SELECT b.number AS 小时 ,
c.number * 5 AS 分钟
FROM master.dbo.spt_values b
CROSS APPLY ( SELECT
number
FROM
master.dbo.spt_values b
WHERE
type = 'P'
) c
WHERE b.number BETWEEN 1 AND 24
AND c.number BETWEEN 1 AND 12
AND b.type = 'P'
) t
WHERE a.type = 'P'
AND a.number <= DATEDIFF(DAY, @date1, @date2)
) t
WHERE NOT EXISTS ( SELECT 1
FROM b
WHERE t.日期 = b.日期
AND t.小时 = b.小时
AND t.分钟 = b.分钟 )