mysql - 在 MySQL 查询中合并超过 1 个 SELECT 语句

Question

除了使用 UNION 或 UNION ALL 之外，还有其他方法可以在 MySQL 查询中组合 2 个以上的 SELECT 语句吗？我已经尝试使用 UNION 和 UNION ALL 但我的查询加载速度太慢。这是我的查询:

SELECT 
    'AVAILABLE' AS STATUS, 
    count(id_status) as BIL
FROM 
    book_records AS b, book_class AS c
WHERE 
    b.id_book = c.id_book AND 
    id_status IN ( 1 ) AND class_desc =  'NOVEL'
UNION
SELECT 
    'WAITING' AS STATUS, 
     count(id_status) as BIL
FROM 
    book_records AS b, book_class AS c
WHERE 
    b.id_book = c.id_book AND 
    id_status IN ( 2,3,5 ) AND 
    class_desc =  'NOVEL'
UNION
SELECT 
    'DAMAGED' AS STATUS, 
    count(id_status) as BIL
FROM 
    book_records AS b, book_class AS c
WHERE 
    b.id_book = c.id_book AND 
    id_status NOT IN ( 1,2,3,5 ) AND 
    class_desc =  'NOVEL'

结果:

STATUS      BIL
----------------
AVAILABLE   5
WAITING     25
DAMAGED     0

有人可以告诉我如何解决问题吗？

Answer 1

您可以通过表格一次获得结果，而不是通过三次。

我个人的偏好是返回一个稍微不同的结果集，包含三个聚合的单行:

SELECT SUM(IF(b.id_status IN ( 1 ))) AS `AVAILABLE`
     , SUM(IF(b.id_status IN ( 2,3,5 ))) AS `WAITING`
     , SUM(IF(b.id_status NOT IN ( 1,2,3,5 ))) AS `DAMAGED`
  FROM book_records b
  JOIN book_class c
    ON c.id_book = p.id_book
   AND c.class_desc = 'NOVEL'
 WHERE b.id_status IS NOT NULL

要将该结果转换为当前查询返回的三行，我们可以生成您想要返回的三行(内联 View 别名为 s)，然后匹配上面查询中的计数(内联 View 别名为 t) 。我们将执行一个 CROSS JOIN(t 中只有一行)，并使用 CASE 表达式来选择要在哪一行上返回的计数...

SELECT s.status
     , CASE WHEN s.status = 'AVAILABLE' THEN t.count_available
            WHEN s.status = 'WAITING'   THEN t.count_waiting
            WHEN s.status = 'DAMAGED'   THEN t.count_damaged
       END AS BIL
  FROM ( SELECT 'AVAILABLE' AS status, 1 AS seq
          UNION ALL
         SELECT 'WAITING', 2
          UNION ALL
         SELECT 'DAMAGED', 3
       ) s
 CROSS
  JOIN ( SELECT IFNULL(SUM(IF(b.id_status IN ( 1 ))),0) AS count_available
              , IFNULL(SUM(IF(b.id_status IN ( 2,3,5 ))),0) AS count_waiting
              , IFNULL(SUM(IF(b.id_status NOT IN ( 1,2,3,5 ))),0) AS count_damaged
           FROM book_records b
           JOIN book_class c
             ON c.id_book = p.id_book
            AND c.class_desc = 'NOVEL'
          WHERE b.id_status IS NOT NULL
       ) t
 ORDER BY s.seq

<小时/>

(我不知道您的查询是如何运行的，因为它引用了 k.id_book 但没有 k 行源。我假设这个确实应该是 c.id_book; 我没有看到 b 和 c 之间有任何连接条件，并且我不认为您打算进行交叉连接。我将假设 b 和 c 之间存在一对多关系，a book_record 可以有零个、一个或多个 book_class，但您只对特定类的 book_record 感兴趣。看起来您的意思是对每个查询执行 GROUP BY 来获取每个状态的计数...)

此外，最佳实践是限定引用多个行源的查询中的所有列引用。

为了获取您返回的结果集，我会做这样的事情，作为一次遍历表:

SELECT CASE WHEN b.id_status IN ( 1 ) THEN 'AVAILABLE'
            WHEN b.id_status IN ( 2,3,5 ) THEN 'WAITING'
            ELSE 'DAMAGED' // any non-NULL status that is not 1,2,3,5
       END AS status
     , COUNT(b.id_status) AS BIL
  FROM book_records b
  JOIN book_class c
    ON c.id_book = p.id_book
   AND c.class_desc = 'NOVEL'
 WHERE b.id_status IS NOT NULL
 GROUP BY status

如果 id_status 保证不为 NULL，则可以省略该 WHERE 子句。

(不言而喻，book_class 上的适当索引可以是 id_book 的前导列并包含 book_class，也可能是 (book_class, id_book) ，会加速连接操作。我在这里假设 id_book 已经是 book_record 上的唯一索引。理想情况下，会有覆盖索引 ON book_records (id_book,id_status ) 也是如此。)

要获得返回的相同结果集需要更多工作(保证三行和零计数)，但它仍然比运行三个查询更有效:

SELECT s.status AS STATUS
     , IFNULL(t.BIL,0) AS BIL
  FROM ( SELECT 'AVAILABLE' AS status, 1 AS seq
          UNION ALL
         SELECT 'WAITING', 2
          UNION ALL
         SELECT 'DAMAGED', 3
       ) s
  LEFT
  JOIN ( SELECT CASE WHEN b.id_status IN ( 1 ) THEN 'AVAILABLE'
                     WHEN b.id_status IN ( 2,3,5 ) THEN 'WAITING'
                     ELSE 'DAMAGED' // any non-NULL status that is not 1,2,3,5
                END AS status
              , COUNT(b.id_status) AS BIL
          FROM book_records b
          JOIN book_class c
            ON c.id_book = p.id_book
           AND c.class_desc = 'NOVEL'
         WHERE b.id_status IS NOT NULL
         GROUP BY status
       ) t
    ON t.status = s.status
 ORDER BY s.seq