根据优先级从不同的表返回值

Assuming I have three tables : TableA (key, value) TableB (key, value) TableC (key, value)

假设我有三个表：TableA（键，值）TableB（键，值）TableC（键，值）

and I want to return a value for all keys. If the key exists in TableC return that value else if the key exists in B return that value else return the value from table A

我想为所有键返回一个值。如果TableC中存在键，则返回该值，否则如果B中存在该键则返回该值，否则返回表A中的值

The best I have come up with so far is

到目前为止，我提出的最好的是

SELECT key,Value
FROM TableA
WHERE key NOT IN (SELECT key FROM TableB)
    AND key NOT IN (SELECT key FROM TableC)
UNION
SELECT key,Value
FROM TableB
WHERE key NOT IN (SELECT key FROM TableC)
UNION
SELECT key,Value
FROM TableC

But this seems pretty brute force. Anyone know a better way?

但这似乎是相当野蛮的力量。谁知道更好的方法？

Edit: Here is a more concrete example. Consider TableA as a standard work schedule where the key is a date and the value is the assigned shift. Table B is a statutory holiday calendar that overrides the standard work week. Table C is an exception schedule that is used to override the other two schedules when someone is asked to come in and work either an extra shift or a different shift.

编辑：这是一个更具体的例子。将TableA视为标准工作计划，其中键是日期，值是指定的班次。表B是覆盖标准工作周的法定假日日历。表C是一个例外时间表，用于在有人被要求进入并工作额外班次或不同班次时覆盖其他两个时间表。

7 个解决方案

#1

OK, using your concrete example as a basis, I came up with a solution different from the others posted (although I think I like your solution better). This was tested on MS SQL Server 2005 - changes may be needed for your SQL dialect.

好的，以您的具体示例为基础，我提出了一个与其他人发布的解决方案不同的解决方案（尽管我认为我更喜欢您的解决方案）。这是在MS SQL Server 2005上测试的 - 您的SQL方言可能需要更改。

First, some DDL to set the stage:

首先，一些DDL设置阶段：

CREATE TABLE [dbo].[StandardSchedule](
    [scheduledate] [datetime] NOT NULL,
    [shift] [varchar](25) NOT NULL,
 CONSTRAINT [PK_StandardSchedule] PRIMARY KEY CLUSTERED 
( [scheduledate] ASC ));

CREATE TABLE [dbo].[HolidaySchedule](
    [holidaydate] [datetime] NOT NULL,
    [shift] [varchar](25) NOT NULL,
 CONSTRAINT [PK_HolidaySchedule] PRIMARY KEY CLUSTERED 
( [holidaydate] ASC ));

CREATE TABLE [dbo].[ExceptionSchedule](
    [exceptiondate] [datetime] NOT NULL,
    [shift] [varchar](25) NOT NULL,
 CONSTRAINT [PK_ExceptionDate] PRIMARY KEY CLUSTERED 
( [exceptiondate] ASC ));

INSERT INTO ExceptionSchedule VALUES ('2008.01.06', 'ExceptionShift1');
INSERT INTO ExceptionSchedule VALUES ('2008.01.08', 'ExceptionShift2');
INSERT INTO ExceptionSchedule VALUES ('2008.01.10', 'ExceptionShift3');
INSERT INTO HolidaySchedule VALUES ('2008.01.01', 'HolidayShift1');
INSERT INTO HolidaySchedule VALUES ('2008.01.06', 'HolidayShift2');
INSERT INTO HolidaySchedule VALUES ('2008.01.09', 'HolidayShift3');
INSERT INTO StandardSchedule VALUES ('2008.01.01', 'RegularShift1');
INSERT INTO StandardSchedule VALUES ('2008.01.02', 'RegularShift2');
INSERT INTO StandardSchedule VALUES ('2008.01.03', 'RegularShift3');
INSERT INTO StandardSchedule VALUES ('2008.01.04', 'RegularShift4');
INSERT INTO StandardSchedule VALUES ('2008.01.05', 'RegularShift5');
INSERT INTO StandardSchedule VALUES ('2008.01.07', 'RegularShift6');
INSERT INTO StandardSchedule VALUES ('2008.01.09', 'RegularShift7');
INSERT INTO StandardSchedule VALUES ('2008.01.10', 'RegularShift8');

Using these tables/rows as a basis, this SELECT statement retrieves the desired data:

使用这些表/行作为基础，此SELECT语句将检索所需的数据：

SELECT DISTINCT
    COALESCE(e2.exceptiondate, e.exceptiondate, holidaydate, scheduledate) AS ShiftDate,
    COALESCE(e2.shift, e.shift, h.shift, s.shift) AS Shift
FROM standardschedule s
FULL OUTER JOIN holidayschedule h ON s.scheduledate = h.holidaydate
FULL OUTER JOIN exceptionschedule e ON h.holidaydate = e.exceptiondate
FULL OUTER JOIN exceptionschedule e2 ON s.scheduledate = e2.exceptiondate
ORDER BY shiftdate

#2

Here is an alternate SQL statement:-

这是一个备用SQL语句： -

SELECT
    ALL_KEYS.KEY,
    NVL( TABLEC.VALUE, NVL( TABLEB.VALUE, TABLEA.VALUE)) AS VALUE
FROM
    (SELECT KEY AS KEY FROM TABLEA
     UNION
     SELECT KEY FROM TABLEB
     UNION
     SELECT KEY FROM TABLEC) ALL_KEYS,
     TABLEA,
     TABLEB,
     TABLEC
WHERE
    ALL_KEYS.KEY = TABLEA.KEY(+) AND
    ALL_KEYS.KEY = TABLEB.KEY(+) AND
    ALL_KEYS.KEY = TABLEC.KEY(+);

NB. The NVL() is an Oracle function. If the first parameter is NULL, the second parameter is returned otherwise the first parameter is returned. You didn't say which database you were using but no doubt there are equivalents in everything.

NB。 NVL（）是Oracle函数。如果第一个参数为NULL，则返回第二个参数，否则返回第一个参数。您没有说明您使用的是哪个数据库，但毫无疑问，所有数据库都有相应的数据库。

#3

If you have a number of tables that you want data from, then you are going to have to select from them, there is no other way around it.

如果你有许多你想要数据的表，那么你将不得不从中选择，没有别的办法。

From your SQL, it seems that you could get restuls from tableC that contains keys in tableA and tableB, as you are UNION-ing the restuls of a simple select on tableC (of which there is no where clause). Where you after an exclusive set of keys that do NOT exist in any of the other tables? If so, then you will need to do what you did for the where clause for tableA in the selects for tableB and tableC.

从你的SQL中，似乎你可以从tableC获得包含tableA和tableB中的键的restuls，因为你是在tableC上的一个简单select的restuls（其中没有where子句）。你在任何其他表中不存在的一组独有键之后的位置？如果是这样，那么你需要在tableB和tableC的选择中为tableA的where子句做你所做的。

I hope that makes sense...

我希望这是有道理的...

#4

Here's how I would do it in SQL Server. This solution should generate less logical IO than the original. If the tables were sufficiently huge, I would swap over to #temp tables to enable parallelism.

这是我在SQL Server中如何做到这一点。此解决方案应生成比原始逻辑IO更少的逻辑IO。如果表格足够大，我会交换到#temp表以启用并行性。

DECLARE @MyTable TABLE
(
  Key int PRIMARY KEY,
  Value int
)

    --Grab from TableC
INSERT INTO @MyTable(Key, Value)
SELECT Key, Value
FROM TableC

    --Grab from TableB
INSERT INTO @MyTable(Key, Value)
SELECT Key, Value
FROM TableB
WHERE Key not in (SELECT Key FROM @MyTable)

    --Grab from TableA  
INSERT INTO @MyTable(Key, Value)
SELECT Key, Value
FROM TableA
WHERE Key not in (SELECT Key FROM @MyTable)
    --Pop the result
SELECT Key, Value
FROM @MyTable

This technique mirrors how I would handle 3 lists in C#... by creating a dictionary.

这种技术反映了如何通过创建字典来处理C＃中的3个列表。

#5

Your query looks fine.

您的查询看起来很好。

Alternatively, you can use the query below and filter on the client side. It will be less stressful for the database server.

或者，您可以使用下面的查询并在客户端进行过滤。对数据库服务器来说压力会小一些。

SELECT key, value, 2 AS priority
FROM TableA
UNION
SELECT key, value, 1 AS priority
FROM TableB
UNION
SELECT key, value, 0 AS priority
FROM TableC
ORDER BY key, priority

#6

SELECT isnull( c.key, isnull( b.key, a.key) ) , 
       isnull( c.value, isnull( b.value, a.value ) ) 
FROM   TableA a 
LEFT JOIN TableB b 
ON        a.key = b.key
LEFT JOIN TableC c 
ON        b.key = c.key

#7

Create a master table of all keys, then left join this master table to the three tables and investigate the COALESCE command.

创建所有键的主表，然后将此主表连接到三个表并调查COALESCE命令。

#1