时间戳

首页 > 解决方案 > Oracle SQL - 连接 3 个表并划分列表而不重叠的 2 个查询

sql - Oracle SQL - 连接 3 个表并划分列表而不重叠的 2 个查询

问题描述

我有 3 张桌子

表 1是有资格参加此计划的用户列表。它有 CUSTOMER_ID 字段。该列表每个用户有多个条目，所有这些条目都需要包含在下面描述的最终列表中。

表 2 (b) 列出了大多数（但不是全部）用户以及联系他们的最佳时间。它具有以下字段：

表 3是具有以下字段的所有用户的主列表：

我需要将表 1 分为 2 个列表：

列表 1需要是表 1 中的所有用户（以及每个用户的所有行）：

（即，如果今天是星期一，则包括表 1 中的所有用户，其值在表 2 的星期一字段中的值介于 6 和 23 之间）

这就是我列出的似乎可行的清单一：

SELECT b.USER_ID, b.monday, b.tuesday, b.wednesday, b.thursday, 
b.friday, b.saturday, b.sunday FROM $A$ a, $C$ c, $B$ b 
WHERE ( 
TO_CHAR(sysdate, 'D') = 1 AND (b.sunday >= 6 AND b.sunday <= 23) OR 
TO_CHAR(sysdate, 'D') = 2 AND (b.monday >= 6 AND b.monday <= 23) OR 
TO_CHAR(sysdate, 'D') = 3 AND (b.tuesday >= 6 AND b.tuesday <= 23) OR 
TO_CHAR(sysdate, 'D') = 4 AND (b.wednesday >= 6 AND b.wednesday <= 23) OR 
TO_CHAR(sysdate, 'D') = 5 AND (b.thursday >= 6 AND b.thursday <= 23) OR 
TO_CHAR(sysdate, 'D') = 6 AND (b.friday >= 6 AND b.friday <= 23) OR 
TO_CHAR(sysdate, 'D') = 7 AND (b.saturday >= 6 AND b.saturday <= 23) ) 
AND (c.USER_ID = b.USER_ID AND a.CUSTOMER_ID_ = c.CUSTOMER_ID_)

清单 2应包括表 1 中的所有用户（以及每个用户的所有行），他们：

我对列表 2 有 2 个查询，但我不确定如何组合它们。

我在这 3 个查询中获得的记录数超过了原始列表，因此肯定存在一些重叠。

谁能提供有关如何编写第二个查询的见解？

标签： sqloracle

解决方案

SQL小提琴

Oracle 11g R2 模式设置：

CREATE TABLE table1 ( CUSTOMER_ID ) AS
SELECT 1 FROM DUAL UNION ALL
SELECT 2 FROM DUAL;

CREATE TABLE table2 (
  USER_ID,
  MONDAY,
  TUESDAY,
  WEDNESDAY,
  THURSDAY,
  FRIDAY,
  SATURDAY,
  SUNDAY
) AS
SELECT 1, 2, 3, 4, 5, 6, 7, 8 FROM DUAL;

CREATE TABLE table3 ( USER_ID, CUSTOMER_ID ) As
SELECT 1, 1 FROM DUAL UNION ALL
SELECT 2, 2 FROM DUAL;

查询 1：

SELECT b.USER_ID, 
       b.monday,
       b.tuesday,
       b.wednesday,
       b.thursday, 
       b.friday,
       b.saturday,
       b.sunday
FROM   table1 a
       INNER JOIN table3 c
       ON ( a.CUSTOMER_ID = c.CUSTOMER_ID )
       INNER JOIN table2 b 
       ON ( c.USER_ID = b.USER_ID )
WHERE  CASE TRUNC( SYSDATE ) - TRUNC( SYSDATE, 'IW' )
       WHEN 0 THEN b.Monday
       WHEN 1 THEN b.Tuesday
       WHEN 2 THEN b.Wednesday
       WHEN 3 THEN b.Thursday
       WHEN 4 THEN b.Friday
       WHEN 5 THEN b.Saturday
       WHEN 6 THEN b.Sunday
       END                      BETWEEN 6 AND 23

结果：

| USER_ID | MONDAY | TUESDAY | WEDNESDAY | THURSDAY | FRIDAY | SATURDAY | SUNDAY |
|---------|--------|---------|-----------|----------|--------|----------|--------|
|       1 |      2 |       3 |         4 |        5 |      6 |        7 |      8 |

查询 2：您似乎只想要一个LEFT OUTER JOIN.

SELECT c.USER_ID, 
       b.monday,
       b.tuesday,
       b.wednesday,
       b.thursday, 
       b.friday,
       b.saturday,
       b.sunday
FROM   table1 a
       INNER JOIN table3 c
       ON ( a.CUSTOMER_ID = c.CUSTOMER_ID )
       LEFT OUTER JOIN table2 b 
       ON ( c.USER_ID = b.USER_ID )
WHERE  b.USER_ID IS NULL
OR     NOT (
         CASE TRUNC( SYSDATE ) - TRUNC( SYSDATE, 'IW' )
         WHEN 0 THEN b.Monday
         WHEN 1 THEN b.Tuesday
         WHEN 2 THEN b.Wednesday
         WHEN 3 THEN b.Thursday
         WHEN 4 THEN b.Friday
         WHEN 5 THEN b.Saturday
         WHEN 6 THEN b.Sunday
         END                      BETWEEN 6 AND 23
       )
OR     CASE TRUNC( SYSDATE ) - TRUNC( SYSDATE, 'IW' )
       WHEN 0 THEN b.Monday
       WHEN 1 THEN b.Tuesday
       WHEN 2 THEN b.Wednesday
       WHEN 3 THEN b.Thursday
       WHEN 4 THEN b.Friday
       WHEN 5 THEN b.Saturday
       WHEN 6 THEN b.Sunday
       END                        IS NULL

结果：

| USER_ID | MONDAY | TUESDAY | WEDNESDAY | THURSDAY | FRIDAY | SATURDAY | SUNDAY |
|---------|--------|---------|-----------|----------|--------|----------|--------|
|       2 | (null) |  (null) |    (null) |   (null) | (null) |   (null) | (null) |

推荐阅读