代码之家  ›  专栏  ›  技术社区  ›  Georg Schölly Crazy Developer

如何执行删除值的联接?

  •  3
  • Georg Schölly Crazy Developer  · 技术社区  · 16 年前
    Customers            Holidays
    
    id | name            customer_id | start  | end
    ---+------           ------------+--------+------
     1 | Peter           1           |   5    | 10
     2 | Simon           1           |  15    | 20
     3 | Mary            2           |   5    | 20
    
    

    什么是有效的SQL查询 没有 例如日期=12

    • 玛丽

    通过简单的SQL连接,这是可以管理的,还是我需要使用子查询?

    5 回复  |  直到 16 年前
        1
  •  6
  •   Mark Byers    16 年前

    首先创建一个查询,查找与您想要的相反的内容:在特定日期有假期的客户:

    SELECT DISTINCT name
    FROM Customers
    JOIN Holidays
    ON id = customer_id
    WHERE start <= 12 AND end >= 12
    

    Simon
    

    然后将此结果联接回customer表,并选择联接失败的结果:

    SELECT name
    FROM Customers
    LEFT JOIN (
        SELECT DISTINCT id
        FROM Customers
        JOIN Holidays
        ON id = customer_id
        WHERE start <= 12 AND end >= 12
    ) AS T1
    ON Customers.id = T1.id
    WHERE T1.id IS NULL
    

    结果:

    Peter
    Mary
    

    请注意,连接并不是这里唯一的选择。你也可以用 NOT EXISTS , NOT IN EXCEPT

    CREATE TABLE Holidays (customer_id INT NOT NULL, start INT NOT NULL, end INT NOT NULL);
    INSERT INTO Holidays (customer_id, start, end) VALUES
    (1, 5, 10),
    (1, 15, 20),
    (2, 5, 20);
    
    CREATE TABLE Customers (id INT NOT NULL, name NVARCHAR(100) NOT NULL);
    INSERT INTO Customers (id, name) VALUES
    (1, 'Peter'),
    (2, 'Simon'),
    (3, 'Mary');
    
        2
  •  1
  •   Jaxidian    16 年前

    declare @myStart int
    declare @myEnd int
    SET @myStart = 11
    SET @myEnd = 14
    
    SELECT c.name
    FROM Customers c
    INNER JOIN Holidays h
        ON c.id=h.customer_id
    WHERE
    (@myStart BETWEEN h.start AND h.end) OR
    (@myEnd BETWEEN h.start AND h.end) OR
    (@myStart < h.start AND @myEnd > h.end)
    
        3
  •  1
  •   wonde    15 年前

      -- Get customers only from Customers table
      SELECT name FROM Customers 
      WHERE id NOT IN ( -- Get the intersection between the two tables
                        SELECT id FROM Customers JOIN 
                        Holidays ON Customers.Id = Holidays.customer_id
                        WHERE Holidays.start=12 AND Holidays.end = 12)
    
        4
  •  0
  •   Neil N HLGEM    16 年前

    SELECT
        Name
    FROM
        Customers c
        JOIN Holidays h on h.Customer_id = c.ID 
    WHERE
        Start >= @Start
        OR End <= @End
    
        5
  •  0
  •   Björn    16 年前
    select
        c.name
    from
        Customers c
        left outer join
            Holidays h
        on
            h.customer_id=c.id
    where
        h.start <= 11
        and
        h.end >= 14
    group by
        c.id