MySQL 查询返回重复结果

Question

对 mySQL 来说还很陌生。尝试让查询正常工作，但不断获得正确的结果，但结果重复。

这是查询

SELECT DISTINCT 
    band.band_name, concert.venue_name 
FROM 
    band, concert 
WHERE 
    concert.date="2012-10-17" AND band_name 
IN 
    (SELECT band.band_name FROM band WHERE band.band_id 
IN 
    (SELECT tour.band_id from tour where tour.tour_name 
IN 
    (SELECT tour_name from concert where concert.date="2012-10-17")));

DDL:

CREATE TABLE band (
    band_id      INT,
    band_name    VARCHAR(50),
    genre        VARCHAR(20),
    PRIMARY KEY (band_id) );

CREATE TABLE musician (
    person_id   INT,
    name        VARCHAR(50),
    band_id     INT, 
    PRIMARY KEY (person_id),
    FOREIGN KEY (band_id) REFERENCES band(band_id) );

CREATE TABLE tour(
    tour_name   VARCHAR(50),
    band_id     INT,
    PRIMARY KEY (tour_name),
    FOREIGN KEY (band_id) REFERENCES band(band_id) );

CREATE TABLE venue(
    venue_name  VARCHAR(30),
    hire_cost   INT,
    PRIMARY KEY (venue_name) );

CREATE TABLE concert(
    concert_id       INT,
    date             DATE,
    tour_name        VARCHAR(50),
    venue_name       VARCHAR(30),
    ticket_sales     INT, 
    PRIMARY KEY      (concert_id),
    FOREIGN KEY      (tour_name) REFERENCES tour(tour_name),
    FOREIGN KEY      (venue_name) REFERENCES venue(venue_name) );

我完全迷路了。任何帮助将不胜感激。

Answer 1

连接表是正确的方法，您不必将所有条件都堆放在 where 子句中:

SELECT DISTINCT 
    b.band_name, c.venue_name 
FROM concert c 
  join venue v on v.venue_name = c.venue_name -- thats how those 2 tables connect
  join tour t on t.tour_name = c.tour_name    -- thats how those 2 tables connect
  join band b on b.band_id = t.band_id        -- thats how those 2 tables connect
WHERE c.date="2012-10-17"             -- and this gives you all the bandnames and  
                            -- venuenames that play on every concert on this date

这样数据库就可以优化您的查询，并且由于表上的联接不需要扫描这么多数据。