MySQL 多连接查询

Question

我有下表，并尝试检索至少一列(lay、blk、pri、ani)应包含“1863”的数据，但结果不符合预期。

这里我发布表格供引用。

DROP TABLE IF EXISTS my_table; 
CREATE TABLE my_table
(id INT NOT NULL AUTO_INCREMENT UNIQUE,
Shot VARCHAR(4),
lay VARCHAR(15) NOT NULL,
lay_status VARCHAR(15) NOT NULL,
blk VARCHAR(10) NOT NULL,
blk_status VARCHAR(15) NOT NULL,
pri VARCHAR(10) NOT NULL,
pri_status VARCHAR(15) NOT NULL,
ani VARCHAR(10) NOT NULL,
ani_status VARCHAR(15) NOT NULL
);

INSERT INTO my_table VALUES
(1,'SH01','1863','yes','1863','yes','P4645','yes','P4557','yes'),
(2,'SH02','1863','yes','P4645','no','P4557','yes','1863','no'),
(3,'SH03','P4645','yes','P4557','yes','1863','yes','1863','yes'),
(4,'SH04','1863','no','P4645','no','P4557','yes','1863','yes'),
(5,'SH05','1863','yes','1863','yes','P4645','yes','P4557','yes'),
(6,'SH06','P4557','yes','P4645','yes','P4645','yes','P4557','yes');

SELECT * FROM my_table;
+----+------+-------+------------+-------+------------+-------+------------+-------+------------+
| id | Shot | lay   | lay_status | blk   | blk_status | pri   | pri_status | ani   | ani_status |
+----+------+-------+------------+-------+------------+-------+------------+-------+------------+
|  1 | SH01 | 1863  | yes        | 1863  | yes        | P4645 | yes        | P4557 | yes        |
|  2 | SH02 | 1863  | yes        | P4645 | no         | P4557 | yes        | 1863  | no         |
|  3 | SH03 | P4645 | yes        | P4557 | yes        | 1863  | yes        | 1863  | yes        |
|  4 | SH04 | 1863  | no         | P4645 | no         | P4557 | yes        | 1863  | yes        |
|  5 | SH05 | 1863  | yes        | 1863  | yes        | P4645 | yes        | P4557 | yes        |
|  6 | SH06 | P4557 | yes        | P4645 | yes        | P4645 | yes        | P4557 | yes        |
+----+------+-------+------------+-------+------------+-------+------------+-------+------------+
6 rows in set (0.00 sec)

我创建了一个查询，其结果如下

SELECT 
    x.Shot,
    IFNULL(J1.Stage,'NA') as Stage1,
    IFNULL(J1.status,'NA') as Status,
    IFNULL(J2.Stage,'NA') as Stage2,
    IFNULL(J2.status,'NA') as Status,
    IFNULL(J3.Stage,'NA') as Stage3,
    IFNULL(J3.status,'NA') as Status,
    IFNULL(J4.Stage,'NA') as Stage4,
    IFNULL(J4.status,'NA') as Status
FROM
    (SELECT 
        Shot
    FROM
        my_table) x
    LEFT JOIN (SELECT 
        Shot,
        lay as Artist,
        lay_status as status,
        'lay' as Stage

    FROM
        my_table where lay = '1863') J1 ON J1.Shot = x.Shot
    LEFT JOIN (SELECT 
        Shot,
        blk as Artist,
        blk_status as status,
        'blk' as Stage

    FROM
        my_table where blk = '1863') J2 ON J2.Shot = x.Shot
    LEFT JOIN (SELECT 
        Shot,
        pri as Artist,
        pri_status as status,
        'pri' as Stage

    FROM
        my_table where pri = '1863') J3 ON J3.Shot = x.Shot
    LEFT JOIN (SELECT 
        Shot,
        ani as Artist,
        ani_status as status,
        'ani' as Stage

    FROM
        my_table where ani = '1863') J4 ON J4.Shot = x.Shot

上述查询的结果是

+------+--------+--------+--------+--------+--------+--------+--------+--------+
| Shot | Stage1 | Status | Stage2 | Status | Stage3 | Status | Stage4 | Status |
+------+--------+--------+--------+--------+--------+--------+--------+--------+
| SH01 | lay    | yes    | blk    | yes    | NA     | NA     | NA     | NA     |
| SH02 | lay    | yes    | NA     | NA     | NA     | NA     | ani    | no     |
| SH03 | NA     | NA     | NA     | NA     | pri    | yes    | ani    | yes    |
| SH04 | lay    | no     | NA     | NA     | NA     | NA     | ani    | yes    |
| SH05 | lay    | yes    | blk    | yes    | NA     | na     | NA     | NA     |
| SH06 | NA     | NA     | NA     | NA     | NA     | NA     | NA     | NA     |
+------+--------+--------+--------+--------+--------+--------+--------+--------+

但是我需要检索其中lay，blk，pri，ani中至少有一个具有“1863”的记录，但是上面的查询返回了带有SH06的记录，其中没有字段(lay，blk，pri ,ani) 有“1863”

所需结果如下

+------+--------+--------+--------+--------+--------+--------+--------+--------+
| Shot | Stage1 | Status | Stage2 | Status | Stage3 | Status | Stage4 | Status |
+------+--------+--------+--------+--------+--------+--------+--------+--------+
| SH01 | lay    | yes    | blk    | yes    | NA     | NA     | NA     | NA     |
| SH02 | lay    | yes    | NA     | NA     | NA     | NA     | ani    | no     |
| SH03 | NA     | NA     | NA     | NA     | pri    | yes    | ani    | yes    |
| SH04 | lay    | no     | NA     | NA     | NA     | NA     | ani    | yes    |
| SH05 | lay    | yes    | blk    | yes    | NA     | NA     | NA     | NA     |
+------+--------+--------+--------+--------+--------+--------+--------+--------+

Answer 1

尝试一下这个，尽管我认为您对状态 3 的预期结果可能是错误的:

SELECT
  Shot,
  CASE lay WHEN 1863 THEN 'lay' ELSE 'NA' END AS 'Stage1',
  CASE WHEN lay=1863 THEN lay_status ELSE 'NA' END AS 'Status-lay',
  CASE blk WHEN 1863 THEN 'blk' ELSE 'NA' END AS 'Stage2',
  CASE WHEN blk=1863 THEN blk_status ELSE 'NA' END AS 'Status-blk',
  CASE pri WHEN 1863 THEN 'pri' ELSE 'NA' END AS 'Stage3',
  CASE WHEN pri=1863 THEN pri_status ELSE 'NA' END AS 'Status-pri',
  CASE ani WHEN 1863 THEN 'ani' ELSE 'NA' END AS 'Stage4',
  CASE WHEN ani=1863 THEN ani_status ELSE 'NA' END AS 'Status-ani'
FROM
  my_table
WHERE
  lay = 1863
OR
  blk = 1863
OR
  pri = 1863
OR
  ani = 1863

这给出了以下结果:

| SHOT | STAGE1 | STATUS-LAY | STAGE2 | STATUS-BLK | STAGE3 | STATUS-PRI | STAGE4 | STATUS-ANI |
------------------------------------------------------------------------------------------------
| SH01 |    lay |        yes |    blk |        yes |     NA |         NA |     NA |         NA |
| SH02 |    lay |        yes |     NA |         NA |     NA |         NA |    ani |         no |
| SH03 |     NA |         NA |     NA |         NA |    pri |        yes |    ani |        yes |
| SH04 |    lay |         no |     NA |         NA |     NA |         NA |    ani |        yes |
| SH05 |    lay |        yes |    blk |        yes |     NA |         NA |     NA |         NA |

http://sqlfiddle.com/#!2/ef7d9/31/0