代码之家 › 专栏 › 技术社区 › Darrel Miller

基于优先级从不同表返回值

sql

Darrel Miller · 技术社区 · 16 年前

假设我有三张桌子: 表A(键,值) 表B(键,值) 表C(键,值)

我想返回所有键的值。如果键存在于表C中,则返回该值;如果键存在于B中,则返回该值;否则返回表A中的值。

到目前为止我想到的最好的办法是

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

但这似乎相当残忍。有人知道更好的方法吗?

编辑:这里有一个更具体的例子。将表A视为标准工作计划,其中键是日期,值是指定的班次。表B是替代标准工作周的法定假日日历。表C是一个例外的日程表,当有人被要求进来工作时,它用来覆盖另外两个日程表,不管是额外的班次还是不同的班次。

7 回复 | 直到 16 年前

JeremyDWill 16 年前

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

首先,一些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');

使用这些表/行作为基础,此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

WW. 16 年前

下面是一个备用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(+);

铌。nvl()是一个Oracle函数。如果第一个参数为空,则返回第二个参数,否则返回第一个参数。您没有说您使用的是哪个数据库,但毫无疑问,在所有数据库中都有等价的数据库。

Mark 16 年前

如果有许多表需要数据,那么必须从中进行选择,这是没有其他方法的。

从您的SQL中,似乎可以从包含表A和表B中的键的表C中获得restuls,因为您正在联合表C上简单select的restuls(其中没有where子句)。在其他任何表中都不存在的一组排他键之后,您在哪里?如果是这样,那么您将需要为表B和表C的selects中的表A的where子句做些什么。

我希望这有道理…

Amy B 16 年前

下面是我在SQL Server中的方法。此解决方案生成的逻辑IO应比原始IO少。如果表足够大,我将切换到临时表以启用并行性。

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

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

Alexander Kojevnikov 16 年前

您的查询看起来不错。

或者,您可以使用下面的查询并在客户端进行筛选。对于数据库服务器来说,压力会更小。

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

AJ. 16 年前

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

Unsliced 16 年前

创建所有键的主表,然后左键将此主表联接到三个表,并研究 COALESCE 命令。