如果另一个临时表没有返回行，则从一个临时表返回值
•浏览 1
Returning values from a temp table if another temp table returns no rows
如果另一个临时表没有返回行，我需要编写一个从一个临时表返回值的查询。
编辑：整个表具有以下结构
NAME TYPE DATE VALUE
Washington 1 NULL 1000 <--default value
Washington 2 NULL 750 <--default value
Washington 3 NULL 500 <--default value
Washington 1 04.11.2015 500
Washington 2 04.11.2015 250
Washington 3 04.11.2015 100
Washington 1 07.11.2015 600
Washington 2 07.11.2015 300
Washington 3 07.11.2015 200Select *
from temp1
where date =GETDATE()select * from temp2.WITH defaultvalues
AS ( SELECT DISTINCT
n.Name ,
Type ,
Value
FROM mytable
LEFT JOIN nametable AS n ON mytable.x = r.Id
AND Date IS NULL
),
changedvalue
AS ( SELECT DISTINCT
n.Name ,
type ,
date ,
CASE WHEN d.Date = GETDATE()
AND Parameter = '1' THEN Value
WHEN Parameter = '2' THEN '0'
END AS CAP
FROM mytable
LEFT JOIN nametable AS n ON mytable.x = r.Id
WHERE Parameter IN ( '1', '2' )
)
SELECT CASE WHEN NOT EXISTS ( SELECT *
FROM changedvalue
WHERE changedvalue.Date = GETDATE() )
THEN ( SELECT *
FROM defaultvalues
)
ENDWITH defaultvalues
AS (
SELECT DISTINCT
n.Name, [type], [date], [Value] AS cap
FROM mytable
LEFT JOIN nametable AS n ON mytable.x = r.Id AND
[Date] IS NULL
),
changedvalue
AS (
SELECT DISTINCT
n.Name, [type], [date],
CASE WHEN d.Date = GETDATE() AND
Parameter = '1' THEN [Value]
WHEN Parameter = '2' THEN '0'
END AS CAP
FROM mytable
LEFT JOIN nametable AS n ON mytable.x = r.Id
WHERE Parameter IN ( '1', '2' )
)
SELECT *
FROM [changedvalue]
UNION
SELECT *
FROM [defaultvalues]
WHERE NOT EXISTS ( SELECT *
FROM [changedvalue] );WITH defaultvalues
AS ( SELECT DISTINCT
n.Name ,
Type ,
Value
FROM mytable
LEFT JOIN nametable AS n ON mytable.x = r.Id
AND Date IS NULL
),
changedvalue
AS ( SELECT DISTINCT
n.Name ,
type ,
date ,
CASE WHEN d.Date = GETDATE()
AND Parameter = '1' THEN Value
WHEN Parameter = '2' THEN '0'
END AS CAP
FROM mytable
LEFT JOIN nametable AS n ON mytable.x = r.Id
WHERE Parameter IN ( '1', '2' )
)
( SELECT *
FROM changedvalue
WHERE changedvalue.Date = GETDATE() )
UNION
( SELECT *
FROM defaultvalues
WHERE NOT EXISTS (SELECT 1 FROM changedvalue ))
ENDWITH defaultvalues
AS ( SELECT DISTINCT
n.Name ,
Type ,
Value
FROM mytable
LEFT JOIN nametable AS n ON mytable.x = r.Id
AND Date IS NULL
),
changedvalue
AS ( SELECT DISTINCT
n.Name ,
type ,
date ,
CASE WHEN d.Date = GETDATE()
AND Parameter = '1' THEN Value
WHEN Parameter = '2' THEN '0'
END AS CAP
FROM mytable
LEFT JOIN nametable AS n ON mytable.x = r.Id
WHERE Parameter IN ( '1', '2' )
),
final
AS ( SELECT * ,
RANK() OVER ( ORDER BY c ) r
FROM ( SELECT * ,
1 AS c
FROM changedvalue
UNION ALL
SELECT * ,
2 AS c
FROM defaultvalues
) t
)
SELECT *
FROM final
WHERE r = 1CREATE TABLE #temp1 (Name VARCHAR(10), [type] VARCHAR(10), [date] DATE, value int)
CREATE TABLE #temp2 (Name VARCHAR(10), [type] VARCHAR(10), [date] DATE, value int)
INSERT INTO #temp1 ( Name, type, date, value )
VALUES ( 'bob', 'male', '2015-03-12', 123)
INSERT INTO #temp2 ( Name, type, date, value )
VALUES ( 'jane', 'female', GETDATE(), 456)
DECLARE @switch BIT = 1
-- with @switch set to 1, #temp1 will be returned
SELECT t1.*
FROM ( SELECT * FROM #temp1
WHERE @switch = 1
UNION
SELECT * FROM #temp2
WHERE @switch = 0
) t1
SET @switch = 0
--with @switch set to 0, #temp2 will be returned
SELECT t1.*
FROM ( SELECT * FROM #temp1
WHERE @switch = 1
UNION
SELECT * FROM #temp2
WHERE @switch = 0
) t1
DROP TABLE #temp1
DROP TABLE #temp2临时表：Name, type, date, value 其中日期 IS NULL 返回"默认"值
临时表：Name, type, date, value，其中每个唯一的 Name 将有 3 种类型(1,2,3)，每种类型都有不同的值，它们都具有相同的日期。
当日期列没有条目时，这意味着值应该是"默认"，它们存储在同一个表中日期为空的行下
我也有 8 个不同的参数来确定值，但我只对 '1' 和 '2' 感兴趣，因此是 'case when'
所以我最后想要得到的是：
NAME TYPE DATE VALUE
Washington 1 NULL 1000 <--default value
Washington 2 NULL 750 <--default value
Washington 3 NULL 500 <--default value
Washington 1 04.11.2015 500
Washington 2 04.11.2015 250
Washington 3 04.11.2015 100
Washington 1 07.11.2015 600
Washington 2 07.11.2015 300
Washington 3 07.11.2015 200Select *
from temp1
where date =GETDATE()select * from temp2.WITH defaultvalues
AS ( SELECT DISTINCT
n.Name ,
Type ,
Value
FROM mytable
LEFT JOIN nametable AS n ON mytable.x = r.Id
AND Date IS NULL
),
changedvalue
AS ( SELECT DISTINCT
n.Name ,
type ,
date ,
CASE WHEN d.Date = GETDATE()
AND Parameter = '1' THEN Value
WHEN Parameter = '2' THEN '0'
END AS CAP
FROM mytable
LEFT JOIN nametable AS n ON mytable.x = r.Id
WHERE Parameter IN ( '1', '2' )
)
SELECT CASE WHEN NOT EXISTS ( SELECT *
FROM changedvalue
WHERE changedvalue.Date = GETDATE() )
THEN ( SELECT *
FROM defaultvalues
)
ENDWITH defaultvalues
AS (
SELECT DISTINCT
n.Name, [type], [date], [Value] AS cap
FROM mytable
LEFT JOIN nametable AS n ON mytable.x = r.Id AND
[Date] IS NULL
),
changedvalue
AS (
SELECT DISTINCT
n.Name, [type], [date],
CASE WHEN d.Date = GETDATE() AND
Parameter = '1' THEN [Value]
WHEN Parameter = '2' THEN '0'
END AS CAP
FROM mytable
LEFT JOIN nametable AS n ON mytable.x = r.Id
WHERE Parameter IN ( '1', '2' )
)
SELECT *
FROM [changedvalue]
UNION
SELECT *
FROM [defaultvalues]
WHERE NOT EXISTS ( SELECT *
FROM [changedvalue] );WITH defaultvalues
AS ( SELECT DISTINCT
n.Name ,
Type ,
Value
FROM mytable
LEFT JOIN nametable AS n ON mytable.x = r.Id
AND Date IS NULL
),
changedvalue
AS ( SELECT DISTINCT
n.Name ,
type ,
date ,
CASE WHEN d.Date = GETDATE()
AND Parameter = '1' THEN Value
WHEN Parameter = '2' THEN '0'
END AS CAP
FROM mytable
LEFT JOIN nametable AS n ON mytable.x = r.Id
WHERE Parameter IN ( '1', '2' )
)
( SELECT *
FROM changedvalue
WHERE changedvalue.Date = GETDATE() )
UNION
( SELECT *
FROM defaultvalues
WHERE NOT EXISTS (SELECT 1 FROM changedvalue ))
ENDWITH defaultvalues
AS ( SELECT DISTINCT
n.Name ,
Type ,
Value
FROM mytable
LEFT JOIN nametable AS n ON mytable.x = r.Id
AND Date IS NULL
),
changedvalue
AS ( SELECT DISTINCT
n.Name ,
type ,
date ,
CASE WHEN d.Date = GETDATE()
AND Parameter = '1' THEN Value
WHEN Parameter = '2' THEN '0'
END AS CAP
FROM mytable
LEFT JOIN nametable AS n ON mytable.x = r.Id
WHERE Parameter IN ( '1', '2' )
),
final
AS ( SELECT * ,
RANK() OVER ( ORDER BY c ) r
FROM ( SELECT * ,
1 AS c
FROM changedvalue
UNION ALL
SELECT * ,
2 AS c
FROM defaultvalues
) t
)
SELECT *
FROM final
WHERE r = 1CREATE TABLE #temp1 (Name VARCHAR(10), [type] VARCHAR(10), [date] DATE, value int)
CREATE TABLE #temp2 (Name VARCHAR(10), [type] VARCHAR(10), [date] DATE, value int)
INSERT INTO #temp1 ( Name, type, date, value )
VALUES ( 'bob', 'male', '2015-03-12', 123)
INSERT INTO #temp2 ( Name, type, date, value )
VALUES ( 'jane', 'female', GETDATE(), 456)
DECLARE @switch BIT = 1
-- with @switch set to 1, #temp1 will be returned
SELECT t1.*
FROM ( SELECT * FROM #temp1
WHERE @switch = 1
UNION
SELECT * FROM #temp2
WHERE @switch = 0
) t1
SET @switch = 0
--with @switch set to 0, #temp2 will be returned
SELECT t1.*
FROM ( SELECT * FROM #temp1
WHERE @switch = 1
UNION
SELECT * FROM #temp2
WHERE @switch = 0
) t1
DROP TABLE #temp1
DROP TABLE #temp2如果它不存在，我希望它返回：
NAME TYPE DATE VALUE
Washington 1 NULL 1000 <--default value
Washington 2 NULL 750 <--default value
Washington 3 NULL 500 <--default value
Washington 1 04.11.2015 500
Washington 2 04.11.2015 250
Washington 3 04.11.2015 100
Washington 1 07.11.2015 600
Washington 2 07.11.2015 300
Washington 3 07.11.2015 200Select *
from temp1
where date =GETDATE()select * from temp2.WITH defaultvalues
AS ( SELECT DISTINCT
n.Name ,
Type ,
Value
FROM mytable
LEFT JOIN nametable AS n ON mytable.x = r.Id
AND Date IS NULL
),
changedvalue
AS ( SELECT DISTINCT
n.Name ,
type ,
date ,
CASE WHEN d.Date = GETDATE()
AND Parameter = '1' THEN Value
WHEN Parameter = '2' THEN '0'
END AS CAP
FROM mytable
LEFT JOIN nametable AS n ON mytable.x = r.Id
WHERE Parameter IN ( '1', '2' )
)
SELECT CASE WHEN NOT EXISTS ( SELECT *
FROM changedvalue
WHERE changedvalue.Date = GETDATE() )
THEN ( SELECT *
FROM defaultvalues
)
ENDWITH defaultvalues
AS (
SELECT DISTINCT
n.Name, [type], [date], [Value] AS cap
FROM mytable
LEFT JOIN nametable AS n ON mytable.x = r.Id AND
[Date] IS NULL
),
changedvalue
AS (
SELECT DISTINCT
n.Name, [type], [date],
CASE WHEN d.Date = GETDATE() AND
Parameter = '1' THEN [Value]
WHEN Parameter = '2' THEN '0'
END AS CAP
FROM mytable
LEFT JOIN nametable AS n ON mytable.x = r.Id
WHERE Parameter IN ( '1', '2' )
)
SELECT *
FROM [changedvalue]
UNION
SELECT *
FROM [defaultvalues]
WHERE NOT EXISTS ( SELECT *
FROM [changedvalue] );WITH defaultvalues
AS ( SELECT DISTINCT
n.Name ,
Type ,
Value
FROM mytable
LEFT JOIN nametable AS n ON mytable.x = r.Id
AND Date IS NULL
),
changedvalue
AS ( SELECT DISTINCT
n.Name ,
type ,
date ,
CASE WHEN d.Date = GETDATE()
AND Parameter = '1' THEN Value
WHEN Parameter = '2' THEN '0'
END AS CAP
FROM mytable
LEFT JOIN nametable AS n ON mytable.x = r.Id
WHERE Parameter IN ( '1', '2' )
)
( SELECT *
FROM changedvalue
WHERE changedvalue.Date = GETDATE() )
UNION
( SELECT *
FROM defaultvalues
WHERE NOT EXISTS (SELECT 1 FROM changedvalue ))
ENDWITH defaultvalues
AS ( SELECT DISTINCT
n.Name ,
Type ,
Value
FROM mytable
LEFT JOIN nametable AS n ON mytable.x = r.Id
AND Date IS NULL
),
changedvalue
AS ( SELECT DISTINCT
n.Name ,
type ,
date ,
CASE WHEN d.Date = GETDATE()
AND Parameter = '1' THEN Value
WHEN Parameter = '2' THEN '0'
END AS CAP
FROM mytable
LEFT JOIN nametable AS n ON mytable.x = r.Id
WHERE Parameter IN ( '1', '2' )
),
final
AS ( SELECT * ,
RANK() OVER ( ORDER BY c ) r
FROM ( SELECT * ,
1 AS c
FROM changedvalue
UNION ALL
SELECT * ,
2 AS c
FROM defaultvalues
) t
)
SELECT *
FROM final
WHERE r = 1CREATE TABLE #temp1 (Name VARCHAR(10), [type] VARCHAR(10), [date] DATE, value int)
CREATE TABLE #temp2 (Name VARCHAR(10), [type] VARCHAR(10), [date] DATE, value int)
INSERT INTO #temp1 ( Name, type, date, value )
VALUES ( 'bob', 'male', '2015-03-12', 123)
INSERT INTO #temp2 ( Name, type, date, value )
VALUES ( 'jane', 'female', GETDATE(), 456)
DECLARE @switch BIT = 1
-- with @switch set to 1, #temp1 will be returned
SELECT t1.*
FROM ( SELECT * FROM #temp1
WHERE @switch = 1
UNION
SELECT * FROM #temp2
WHERE @switch = 0
) t1
SET @switch = 0
--with @switch set to 0, #temp2 will be returned
SELECT t1.*
FROM ( SELECT * FROM #temp1
WHERE @switch = 1
UNION
SELECT * FROM #temp2
WHERE @switch = 0
) t1
DROP TABLE #temp1
DROP TABLE #temp2我只对今天、明天和后天的结果感兴趣
这是我到目前为止所得到的，但它显然不起作用。
如果能得到任何帮助，我将不胜感激
NAME TYPE DATE VALUE
Washington 1 NULL 1000 <--default value
Washington 2 NULL 750 <--default value
Washington 3 NULL 500 <--default value
Washington 1 04.11.2015 500
Washington 2 04.11.2015 250
Washington 3 04.11.2015 100
Washington 1 07.11.2015 600
Washington 2 07.11.2015 300
Washington 3 07.11.2015 200Select *
from temp1
where date =GETDATE()select * from temp2.WITH defaultvalues
AS ( SELECT DISTINCT
n.Name ,
Type ,
Value
FROM mytable
LEFT JOIN nametable AS n ON mytable.x = r.Id
AND Date IS NULL
),
changedvalue
AS ( SELECT DISTINCT
n.Name ,
type ,
date ,
CASE WHEN d.Date = GETDATE()
AND Parameter = '1' THEN Value
WHEN Parameter = '2' THEN '0'
END AS CAP
FROM mytable
LEFT JOIN nametable AS n ON mytable.x = r.Id
WHERE Parameter IN ( '1', '2' )
)
SELECT CASE WHEN NOT EXISTS ( SELECT *
FROM changedvalue
WHERE changedvalue.Date = GETDATE() )
THEN ( SELECT *
FROM defaultvalues
)
ENDWITH defaultvalues
AS (
SELECT DISTINCT
n.Name, [type], [date], [Value] AS cap
FROM mytable
LEFT JOIN nametable AS n ON mytable.x = r.Id AND
[Date] IS NULL
),
changedvalue
AS (
SELECT DISTINCT
n.Name, [type], [date],
CASE WHEN d.Date = GETDATE() AND
Parameter = '1' THEN [Value]
WHEN Parameter = '2' THEN '0'
END AS CAP
FROM mytable
LEFT JOIN nametable AS n ON mytable.x = r.Id
WHERE Parameter IN ( '1', '2' )
)
SELECT *
FROM [changedvalue]
UNION
SELECT *
FROM [defaultvalues]
WHERE NOT EXISTS ( SELECT *
FROM [changedvalue] );WITH defaultvalues
AS ( SELECT DISTINCT
n.Name ,
Type ,
Value
FROM mytable
LEFT JOIN nametable AS n ON mytable.x = r.Id
AND Date IS NULL
),
changedvalue
AS ( SELECT DISTINCT
n.Name ,
type ,
date ,
CASE WHEN d.Date = GETDATE()
AND Parameter = '1' THEN Value
WHEN Parameter = '2' THEN '0'
END AS CAP
FROM mytable
LEFT JOIN nametable AS n ON mytable.x = r.Id
WHERE Parameter IN ( '1', '2' )
)
( SELECT *
FROM changedvalue
WHERE changedvalue.Date = GETDATE() )
UNION
( SELECT *
FROM defaultvalues
WHERE NOT EXISTS (SELECT 1 FROM changedvalue ))
ENDWITH defaultvalues
AS ( SELECT DISTINCT
n.Name ,
Type ,
Value
FROM mytable
LEFT JOIN nametable AS n ON mytable.x = r.Id
AND Date IS NULL
),
changedvalue
AS ( SELECT DISTINCT
n.Name ,
type ,
date ,
CASE WHEN d.Date = GETDATE()
AND Parameter = '1' THEN Value
WHEN Parameter = '2' THEN '0'
END AS CAP
FROM mytable
LEFT JOIN nametable AS n ON mytable.x = r.Id
WHERE Parameter IN ( '1', '2' )
),
final
AS ( SELECT * ,
RANK() OVER ( ORDER BY c ) r
FROM ( SELECT * ,
1 AS c
FROM changedvalue
UNION ALL
SELECT * ,
2 AS c
FROM defaultvalues
) t
)
SELECT *
FROM final
WHERE r = 1CREATE TABLE #temp1 (Name VARCHAR(10), [type] VARCHAR(10), [date] DATE, value int)
CREATE TABLE #temp2 (Name VARCHAR(10), [type] VARCHAR(10), [date] DATE, value int)
INSERT INTO #temp1 ( Name, type, date, value )
VALUES ( 'bob', 'male', '2015-03-12', 123)
INSERT INTO #temp2 ( Name, type, date, value )
VALUES ( 'jane', 'female', GETDATE(), 456)
DECLARE @switch BIT = 1
-- with @switch set to 1, #temp1 will be returned
SELECT t1.*
FROM ( SELECT * FROM #temp1
WHERE @switch = 1
UNION
SELECT * FROM #temp2
WHERE @switch = 0
) t1
SET @switch = 0
--with @switch set to 0, #temp2 will be returned
SELECT t1.*
FROM ( SELECT * FROM #temp1
WHERE @switch = 1
UNION
SELECT * FROM #temp2
WHERE @switch = 0
) t1
DROP TABLE #temp1
DROP TABLE #temp2您没有提供示例数据，只是基于您的 SQL 认为您只是在最后一部分遇到了问题：
NAME TYPE DATE VALUE
Washington 1 NULL 1000 <--default value
Washington 2 NULL 750 <--default value
Washington 3 NULL 500 <--default value
Washington 1 04.11.2015 500
Washington 2 04.11.2015 250
Washington 3 04.11.2015 100
Washington 1 07.11.2015 600
Washington 2 07.11.2015 300
Washington 3 07.11.2015 200Select *
from temp1
where date =GETDATE()select * from temp2.WITH defaultvalues
AS ( SELECT DISTINCT
n.Name ,
Type ,
Value
FROM mytable
LEFT JOIN nametable AS n ON mytable.x = r.Id
AND Date IS NULL
),
changedvalue
AS ( SELECT DISTINCT
n.Name ,
type ,
date ,
CASE WHEN d.Date = GETDATE()
AND Parameter = '1' THEN Value
WHEN Parameter = '2' THEN '0'
END AS CAP
FROM mytable
LEFT JOIN nametable AS n ON mytable.x = r.Id
WHERE Parameter IN ( '1', '2' )
)
SELECT CASE WHEN NOT EXISTS ( SELECT *
FROM changedvalue
WHERE changedvalue.Date = GETDATE() )
THEN ( SELECT *
FROM defaultvalues
)
ENDWITH defaultvalues
AS (
SELECT DISTINCT
n.Name, [type], [date], [Value] AS cap
FROM mytable
LEFT JOIN nametable AS n ON mytable.x = r.Id AND
[Date] IS NULL
),
changedvalue
AS (
SELECT DISTINCT
n.Name, [type], [date],
CASE WHEN d.Date = GETDATE() AND
Parameter = '1' THEN [Value]
WHEN Parameter = '2' THEN '0'
END AS CAP
FROM mytable
LEFT JOIN nametable AS n ON mytable.x = r.Id
WHERE Parameter IN ( '1', '2' )
)
SELECT *
FROM [changedvalue]
UNION
SELECT *
FROM [defaultvalues]
WHERE NOT EXISTS ( SELECT *
FROM [changedvalue] );WITH defaultvalues
AS ( SELECT DISTINCT
n.Name ,
Type ,
Value
FROM mytable
LEFT JOIN nametable AS n ON mytable.x = r.Id
AND Date IS NULL
),
changedvalue
AS ( SELECT DISTINCT
n.Name ,
type ,
date ,
CASE WHEN d.Date = GETDATE()
AND Parameter = '1' THEN Value
WHEN Parameter = '2' THEN '0'
END AS CAP
FROM mytable
LEFT JOIN nametable AS n ON mytable.x = r.Id
WHERE Parameter IN ( '1', '2' )
)
( SELECT *
FROM changedvalue
WHERE changedvalue.Date = GETDATE() )
UNION
( SELECT *
FROM defaultvalues
WHERE NOT EXISTS (SELECT 1 FROM changedvalue ))
ENDWITH defaultvalues
AS ( SELECT DISTINCT
n.Name ,
Type ,
Value
FROM mytable
LEFT JOIN nametable AS n ON mytable.x = r.Id
AND Date IS NULL
),
changedvalue
AS ( SELECT DISTINCT
n.Name ,
type ,
date ,
CASE WHEN d.Date = GETDATE()
AND Parameter = '1' THEN Value
WHEN Parameter = '2' THEN '0'
END AS CAP
FROM mytable
LEFT JOIN nametable AS n ON mytable.x = r.Id
WHERE Parameter IN ( '1', '2' )
),
final
AS ( SELECT * ,
RANK() OVER ( ORDER BY c ) r
FROM ( SELECT * ,
1 AS c
FROM changedvalue
UNION ALL
SELECT * ,
2 AS c
FROM defaultvalues
) t
)
SELECT *
FROM final
WHERE r = 1CREATE TABLE #temp1 (Name VARCHAR(10), [type] VARCHAR(10), [date] DATE, value int)
CREATE TABLE #temp2 (Name VARCHAR(10), [type] VARCHAR(10), [date] DATE, value int)
INSERT INTO #temp1 ( Name, type, date, value )
VALUES ( 'bob', 'male', '2015-03-12', 123)
INSERT INTO #temp2 ( Name, type, date, value )
VALUES ( 'jane', 'female', GETDATE(), 456)
DECLARE @switch BIT = 1
-- with @switch set to 1, #temp1 will be returned
SELECT t1.*
FROM ( SELECT * FROM #temp1
WHERE @switch = 1
UNION
SELECT * FROM #temp2
WHERE @switch = 0
) t1
SET @switch = 0
--with @switch set to 0, #temp2 will be returned
SELECT t1.*
FROM ( SELECT * FROM #temp1
WHERE @switch = 1
UNION
SELECT * FROM #temp2
WHERE @switch = 0
) t1
DROP TABLE #temp1
DROP TABLE #temp2使用 Union 来组合两个答案
NAME TYPE DATE VALUE
Washington 1 NULL 1000 <--default value
Washington 2 NULL 750 <--default value
Washington 3 NULL 500 <--default value
Washington 1 04.11.2015 500
Washington 2 04.11.2015 250
Washington 3 04.11.2015 100
Washington 1 07.11.2015 600
Washington 2 07.11.2015 300
Washington 3 07.11.2015 200Select *
from temp1
where date =GETDATE()select * from temp2.WITH defaultvalues
AS ( SELECT DISTINCT
n.Name ,
Type ,
Value
FROM mytable
LEFT JOIN nametable AS n ON mytable.x = r.Id
AND Date IS NULL
),
changedvalue
AS ( SELECT DISTINCT
n.Name ,
type ,
date ,
CASE WHEN d.Date = GETDATE()
AND Parameter = '1' THEN Value
WHEN Parameter = '2' THEN '0'
END AS CAP
FROM mytable
LEFT JOIN nametable AS n ON mytable.x = r.Id
WHERE Parameter IN ( '1', '2' )
)
SELECT CASE WHEN NOT EXISTS ( SELECT *
FROM changedvalue
WHERE changedvalue.Date = GETDATE() )
THEN ( SELECT *
FROM defaultvalues
)
ENDWITH defaultvalues
AS (
SELECT DISTINCT
n.Name, [type], [date], [Value] AS cap
FROM mytable
LEFT JOIN nametable AS n ON mytable.x = r.Id AND
[Date] IS NULL
),
changedvalue
AS (
SELECT DISTINCT
n.Name, [type], [date],
CASE WHEN d.Date = GETDATE() AND
Parameter = '1' THEN [Value]
WHEN Parameter = '2' THEN '0'
END AS CAP
FROM mytable
LEFT JOIN nametable AS n ON mytable.x = r.Id
WHERE Parameter IN ( '1', '2' )
)
SELECT *
FROM [changedvalue]
UNION
SELECT *
FROM [defaultvalues]
WHERE NOT EXISTS ( SELECT *
FROM [changedvalue] );WITH defaultvalues
AS ( SELECT DISTINCT
n.Name ,
Type ,
Value
FROM mytable
LEFT JOIN nametable AS n ON mytable.x = r.Id
AND Date IS NULL
),
changedvalue
AS ( SELECT DISTINCT
n.Name ,
type ,
date ,
CASE WHEN d.Date = GETDATE()
AND Parameter = '1' THEN Value
WHEN Parameter = '2' THEN '0'
END AS CAP
FROM mytable
LEFT JOIN nametable AS n ON mytable.x = r.Id
WHERE Parameter IN ( '1', '2' )
)
( SELECT *
FROM changedvalue
WHERE changedvalue.Date = GETDATE() )
UNION
( SELECT *
FROM defaultvalues
WHERE NOT EXISTS (SELECT 1 FROM changedvalue ))
ENDWITH defaultvalues
AS ( SELECT DISTINCT
n.Name ,
Type ,
Value
FROM mytable
LEFT JOIN nametable AS n ON mytable.x = r.Id
AND Date IS NULL
),
changedvalue
AS ( SELECT DISTINCT
n.Name ,
type ,
date ,
CASE WHEN d.Date = GETDATE()
AND Parameter = '1' THEN Value
WHEN Parameter = '2' THEN '0'
END AS CAP
FROM mytable
LEFT JOIN nametable AS n ON mytable.x = r.Id
WHERE Parameter IN ( '1', '2' )
),
final
AS ( SELECT * ,
RANK() OVER ( ORDER BY c ) r
FROM ( SELECT * ,
1 AS c
FROM changedvalue
UNION ALL
SELECT * ,
2 AS c
FROM defaultvalues
) t
)
SELECT *
FROM final
WHERE r = 1CREATE TABLE #temp1 (Name VARCHAR(10), [type] VARCHAR(10), [date] DATE, value int)
CREATE TABLE #temp2 (Name VARCHAR(10), [type] VARCHAR(10), [date] DATE, value int)
INSERT INTO #temp1 ( Name, type, date, value )
VALUES ( 'bob', 'male', '2015-03-12', 123)
INSERT INTO #temp2 ( Name, type, date, value )
VALUES ( 'jane', 'female', GETDATE(), 456)
DECLARE @switch BIT = 1
-- with @switch set to 1, #temp1 will be returned
SELECT t1.*
FROM ( SELECT * FROM #temp1
WHERE @switch = 1
UNION
SELECT * FROM #temp2
WHERE @switch = 0
) t1
SET @switch = 0
--with @switch set to 0, #temp2 will be returned
SELECT t1.*
FROM ( SELECT * FROM #temp1
WHERE @switch = 1
UNION
SELECT * FROM #temp2
WHERE @switch = 0
) t1
DROP TABLE #temp1
DROP TABLE #temp2这是一个例子：
NAME TYPE DATE VALUE
Washington 1 NULL 1000 <--default value
Washington 2 NULL 750 <--default value
Washington 3 NULL 500 <--default value
Washington 1 04.11.2015 500
Washington 2 04.11.2015 250
Washington 3 04.11.2015 100
Washington 1 07.11.2015 600
Washington 2 07.11.2015 300
Washington 3 07.11.2015 200Select *
from temp1
where date =GETDATE()select * from temp2.WITH defaultvalues
AS ( SELECT DISTINCT
n.Name ,
Type ,
Value
FROM mytable
LEFT JOIN nametable AS n ON mytable.x = r.Id
AND Date IS NULL
),
changedvalue
AS ( SELECT DISTINCT
n.Name ,
type ,
date ,
CASE WHEN d.Date = GETDATE()
AND Parameter = '1' THEN Value
WHEN Parameter = '2' THEN '0'
END AS CAP
FROM mytable
LEFT JOIN nametable AS n ON mytable.x = r.Id
WHERE Parameter IN ( '1', '2' )
)
SELECT CASE WHEN NOT EXISTS ( SELECT *
FROM changedvalue
WHERE changedvalue.Date = GETDATE() )
THEN ( SELECT *
FROM defaultvalues
)
ENDWITH defaultvalues
AS (
SELECT DISTINCT
n.Name, [type], [date], [Value] AS cap
FROM mytable
LEFT JOIN nametable AS n ON mytable.x = r.Id AND
[Date] IS NULL
),
changedvalue
AS (
SELECT DISTINCT
n.Name, [type], [date],
CASE WHEN d.Date = GETDATE() AND
Parameter = '1' THEN [Value]
WHEN Parameter = '2' THEN '0'
END AS CAP
FROM mytable
LEFT JOIN nametable AS n ON mytable.x = r.Id
WHERE Parameter IN ( '1', '2' )
)
SELECT *
FROM [changedvalue]
UNION
SELECT *
FROM [defaultvalues]
WHERE NOT EXISTS ( SELECT *
FROM [changedvalue] );WITH defaultvalues
AS ( SELECT DISTINCT
n.Name ,
Type ,
Value
FROM mytable
LEFT JOIN nametable AS n ON mytable.x = r.Id
AND Date IS NULL
),
changedvalue
AS ( SELECT DISTINCT
n.Name ,
type ,
date ,
CASE WHEN d.Date = GETDATE()
AND Parameter = '1' THEN Value
WHEN Parameter = '2' THEN '0'
END AS CAP
FROM mytable
LEFT JOIN nametable AS n ON mytable.x = r.Id
WHERE Parameter IN ( '1', '2' )
)
( SELECT *
FROM changedvalue
WHERE changedvalue.Date = GETDATE() )
UNION
( SELECT *
FROM defaultvalues
WHERE NOT EXISTS (SELECT 1 FROM changedvalue ))
ENDWITH defaultvalues
AS ( SELECT DISTINCT
n.Name ,
Type ,
Value
FROM mytable
LEFT JOIN nametable AS n ON mytable.x = r.Id
AND Date IS NULL
),
changedvalue
AS ( SELECT DISTINCT
n.Name ,
type ,
date ,
CASE WHEN d.Date = GETDATE()
AND Parameter = '1' THEN Value
WHEN Parameter = '2' THEN '0'
END AS CAP
FROM mytable
LEFT JOIN nametable AS n ON mytable.x = r.Id
WHERE Parameter IN ( '1', '2' )
),
final
AS ( SELECT * ,
RANK() OVER ( ORDER BY c ) r
FROM ( SELECT * ,
1 AS c
FROM changedvalue
UNION ALL
SELECT * ,
2 AS c
FROM defaultvalues
) t
)
SELECT *
FROM final
WHERE r = 1CREATE TABLE #temp1 (Name VARCHAR(10), [type] VARCHAR(10), [date] DATE, value int)
CREATE TABLE #temp2 (Name VARCHAR(10), [type] VARCHAR(10), [date] DATE, value int)
INSERT INTO #temp1 ( Name, type, date, value )
VALUES ( 'bob', 'male', '2015-03-12', 123)
INSERT INTO #temp2 ( Name, type, date, value )
VALUES ( 'jane', 'female', GETDATE(), 456)
DECLARE @switch BIT = 1
-- with @switch set to 1, #temp1 will be returned
SELECT t1.*
FROM ( SELECT * FROM #temp1
WHERE @switch = 1
UNION
SELECT * FROM #temp2
WHERE @switch = 0
) t1
SET @switch = 0
--with @switch set to 0, #temp2 will be returned
SELECT t1.*
FROM ( SELECT * FROM #temp1
WHERE @switch = 1
UNION
SELECT * FROM #temp2
WHERE @switch = 0
) t1
DROP TABLE #temp1
DROP TABLE #temp2您只需将结果与不同的 c 列合并。然后在列 c 上排名并选择排名等于 1 的位置。
如果没有样本数据，很难阅读和完全理解您的问题，但您似乎希望根据条件从两个表之一返回值。
看到两个临时表的架构相同，实现此目的的一种方法是简单地 UNION 将结果与 WHERE 子句中的条件检查一起。
我尝试创建一个简化的示例来展示如何执行此操作，如果您将以下代码复制并粘贴到 Management Studio 中，则该代码可以运行。
NAME TYPE DATE VALUE
Washington 1 NULL 1000 <--default value
Washington 2 NULL 750 <--default value
Washington 3 NULL 500 <--default value
Washington 1 04.11.2015 500
Washington 2 04.11.2015 250
Washington 3 04.11.2015 100
Washington 1 07.11.2015 600
Washington 2 07.11.2015 300
Washington 3 07.11.2015 200Select *
from temp1
where date =GETDATE()select * from temp2.WITH defaultvalues
AS ( SELECT DISTINCT
n.Name ,
Type ,
Value
FROM mytable
LEFT JOIN nametable AS n ON mytable.x = r.Id
AND Date IS NULL
),
changedvalue
AS ( SELECT DISTINCT
n.Name ,
type ,
date ,
CASE WHEN d.Date = GETDATE()
AND Parameter = '1' THEN Value
WHEN Parameter = '2' THEN '0'
END AS CAP
FROM mytable
LEFT JOIN nametable AS n ON mytable.x = r.Id
WHERE Parameter IN ( '1', '2' )
)
SELECT CASE WHEN NOT EXISTS ( SELECT *
FROM changedvalue
WHERE changedvalue.Date = GETDATE() )
THEN ( SELECT *
FROM defaultvalues
)
ENDWITH defaultvalues
AS (
SELECT DISTINCT
n.Name, [type], [date], [Value] AS cap
FROM mytable
LEFT JOIN nametable AS n ON mytable.x = r.Id AND
[Date] IS NULL
),
changedvalue
AS (
SELECT DISTINCT
n.Name, [type], [date],
CASE WHEN d.Date = GETDATE() AND
Parameter = '1' THEN [Value]
WHEN Parameter = '2' THEN '0'
END AS CAP
FROM mytable
LEFT JOIN nametable AS n ON mytable.x = r.Id
WHERE Parameter IN ( '1', '2' )
)
SELECT *
FROM [changedvalue]
UNION
SELECT *
FROM [defaultvalues]
WHERE NOT EXISTS ( SELECT *
FROM [changedvalue] );WITH defaultvalues
AS ( SELECT DISTINCT
n.Name ,
Type ,
Value
FROM mytable
LEFT JOIN nametable AS n ON mytable.x = r.Id
AND Date IS NULL
),
changedvalue
AS ( SELECT DISTINCT
n.Name ,
type ,
date ,
CASE WHEN d.Date = GETDATE()
AND Parameter = '1' THEN Value
WHEN Parameter = '2' THEN '0'
END AS CAP
FROM mytable
LEFT JOIN nametable AS n ON mytable.x = r.Id
WHERE Parameter IN ( '1', '2' )
)
( SELECT *
FROM changedvalue
WHERE changedvalue.Date = GETDATE() )
UNION
( SELECT *
FROM defaultvalues
WHERE NOT EXISTS (SELECT 1 FROM changedvalue ))
ENDWITH defaultvalues
AS ( SELECT DISTINCT
n.Name ,
Type ,
Value
FROM mytable
LEFT JOIN nametable AS n ON mytable.x = r.Id
AND Date IS NULL
),
changedvalue
AS ( SELECT DISTINCT
n.Name ,
type ,
date ,
CASE WHEN d.Date = GETDATE()
AND Parameter = '1' THEN Value
WHEN Parameter = '2' THEN '0'
END AS CAP
FROM mytable
LEFT JOIN nametable AS n ON mytable.x = r.Id
WHERE Parameter IN ( '1', '2' )
),
final
AS ( SELECT * ,
RANK() OVER ( ORDER BY c ) r
FROM ( SELECT * ,
1 AS c
FROM changedvalue
UNION ALL
SELECT * ,
2 AS c
FROM defaultvalues
) t
)
SELECT *
FROM final
WHERE r = 1CREATE TABLE #temp1 (Name VARCHAR(10), [type] VARCHAR(10), [date] DATE, value int)
CREATE TABLE #temp2 (Name VARCHAR(10), [type] VARCHAR(10), [date] DATE, value int)
INSERT INTO #temp1 ( Name, type, date, value )
VALUES ( 'bob', 'male', '2015-03-12', 123)
INSERT INTO #temp2 ( Name, type, date, value )
VALUES ( 'jane', 'female', GETDATE(), 456)
DECLARE @switch BIT = 1
-- with @switch set to 1, #temp1 will be returned
SELECT t1.*
FROM ( SELECT * FROM #temp1
WHERE @switch = 1
UNION
SELECT * FROM #temp2
WHERE @switch = 0
) t1
SET @switch = 0
--with @switch set to 0, #temp2 will be returned
SELECT t1.*
FROM ( SELECT * FROM #temp1
WHERE @switch = 1
UNION
SELECT * FROM #temp2
WHERE @switch = 0
) t1
DROP TABLE #temp1
DROP TABLE #temp2我在 WHERE 条件上使用 @switch 的值来确定在 UNION 中调用哪个表。