if exists (select * from sysobjects where [name] = '#xx' and type='U')//在开头注意对于已经存在的临时表进行删除的操作,以防出错。
DROP TABLE #xx
BEGIN
CREATE TABLE #xx
(
dates DATETIME
)
INSERT INTO #xx//这里用到了要对临时表插入从现在时间开始的后7天的时间,没有找到好的方法,希望能有人指点一下。
(
dates
)
VALUES
(
GETDATE()
)
INSERT INTO #xx
(
dates
)
VALUES
(
DATEADD(dd, 1, GETDATE())
)
INSERT INTO #xx
(
dates
)
VALUES
(
DATEADD(dd, 2, GETDATE())
)
INSERT INTO #xx
(
dates
)
VALUES
(
DATEADD(dd, 3, GETDATE())
)
INSERT INTO #xx
(
dates
)
VALUES
(
DATEADD(dd, 4, GETDATE())
)
INSERT INTO #xx
(
dates
)
VALUES
(
DATEADD(dd, 5, GETDATE())
)
INSERT INTO #xx
(
dates
)
VALUES
(
DATEADD(dd, 6, GETDATE())
)
INSERT INTO #xx
(
dates
)
VALUES
(
DATEADD(dd, 7, GETDATE())
)
SELECT d.rtuid,d.relayid,d.dates,boort.OnTime1,boort.OffTime1//下面就是简单的查询操作了。
FROM
(
SELECT b.rtuid,
b.relayid,
c.dates
FROM B_RtuRelay b,
#xx c)
d
LEFT OUTER JOIN B_OnOffRealTime boort
ON d.rtuid = boort.RTUID AND d.relayid = boort.RelayID AND CONVERT(VARCHAR(30),d.dates,11) = CONVERT(VARCHAR(30),boort.OnOffDate,11)
WHERE d.rtuid=05321112 AND boort.OnTime1 IS NULL
ORDER BY
rtuid,
d.RelayID,
d.dates
DROP TABLE #xx
END