mysql - 将对同一张表的查询合并为一个查询

Question

我正在尝试从我的 MySQL 表中获取一些统计信息，并希望将其保留为单个查询。只是不知道如何(或者甚至可能)。我有这两个问题:

SELECT COUNT(*) AS `accept` 
FROM `status` 
WHERE `groupID` IN (98779,98780) 
  AND `group` = 'order' 
  AND `status` = 'accept'

和

SELECT COUNT(*) AS `price` 
FROM `status` 
WHERE `groupID` IN (98779, 98780) 
  AND `group` = 'quotation' 
  AND (`status` = 'final' OR `status` = 'manualprice')

我最好的建议是像这样将它们组合在一个 SELECT 中:

SELECT
    (SELECT COUNT(*) AS `accept` 
     FROM `status`
     WHERE `groupID` IN (98779, 98780) AND `group` = 'order' AND `status` = 'accept') AS accept,
    (SELECT COUNT(*) AS `price` 
     FROM `status` 
     WHERE `groupID` IN (98779, 98780) AND `group` = 'quotation' AND (`status` = 'final' OR `status` = 'manualprice')) AS price

有没有更好的办法？

Answer 1

还有另一种方法，

SELECT  SUM(`group` = 'order' AND `status` = 'accept') AS `ACCEPT`,
        SUM(`group` = 'quotation' AND (`status` = 'final' OR `status` = 'manualprice')) AS `PRICE`
FROM    `status`
WHERE   `groupID` IN (98779, 98780) AND
        `group` IN ('order', 'quotation') AND
        `status` IN ('accept', 'final', 'manualprice')