通过X参数得到特定条件下两列的小计和全组的另一列的小计

Question

我想按关键字名称分组，获得cf1+cf2的总和（其中bug_status=CLOSED或RESOLVED），并获得总和（与错误状态无关）。输出将有3列，如前所述。

已尝试查询，但没有成功：

SELECT keyworddefs.name as keyword, IFNULL(SUM(bugs.cf1 +  bugs.cf2),0) as completed, (SELECT IFNULL(SUM(bugs.cf1 +  bugs.cf2) ,0) FROM bugs, keywords, keyworddefs WHERE (keywords.bug_id = bugs .bug_id) AND (keyworddefs.id=keywords.keywordid) AND (keyworddefs.name LIKE 'K%')) as total FROM bugs, keywords, keyworddefs WHERE (keywords.bug_id = bugs .bug_id) AND (keyworddefs.id=keywords.keywordid) AND (bugs.bug_status  = 'VERIFIED' OR bugs.bug_status  = 'CLOSED') GROUP BY keyworddefs.name DESC;

这是格式化的查询。

SELECT keyworddefs.name as keyword,
       IFNULL(SUM(bugs.cf1 +  bugs.cf2),0) as completed,
       (SELECT IFNULL(SUM(bugs.cf1 +  bugs.cf2) ,0) 
          FROM bugs, keywords, keyworddefs
         WHERE (keywords.bug_id = bugs .bug_id)
           AND (keyworddefs.id=keywords.keywordid)
           AND (keyworddefs.name LIKE 'K%')) as total
  FROM bugs, keywords, keyworddefs
 WHERE (keywords.bug_id = bugs .bug_id)
   AND (keyworddefs.id=keywords.keywordid)
   AND (bugs.bug_status  = 'VERIFIED' OR bugs.bug_status  = 'CLOSED')
 GROUP BY keyworddefs.name DESC;

SQL Fiddle：

http://sqlfiddle.com/#！9/a11b4/7

预期：

Matching records:
cf1+cf2    bugid, keyword     bug_status
5          (102, 'K1')        CLOSED
3          (565, 'K2')        CLOSED
3          (1352, 'K1')       VERIFIED
4          (13634, 'K1')      NEW
# Query output should be:
keyword     completed    total
K1          8            12
K2          3            3         

DDL:

bugs表1（主表）：

CREATE TABLE `bugs` (
     `bug_id` int(11) NOT NULL,
     `bug_date` date NOT NULL,
     `cf1` int(11) NOT NULL,
     `cf2` int(11) NOT NULL,
     `bug_status` varchar(200) NOT NULL)
 ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO `bugs` (`bug_id`, `bug_date`, `cf1`, `cf2`, `bug_status`) VALUES 
     (102,   '2016-07-19', 2, 1, 'CLOSED'), 
     (72123, '2016-07-19', 2, 1, 'VERIFIED'),
     (57234, '2016-07-19', 2, 1, 'VERIFIED'),
     (1352,  '2016-07-19', 2, 1, 'VERIFIED'),
     (565,   '2016-07-19', 2, 1, 'CLOSED'),
     (13634, '2016-07-22', 2, 2, 'NEW');

关键字表2（具有关键字id）：

CREATE TABLE `keywords` (
   `bug_id` int(11) NOT NULL,
   `keywordid` varchar(11) NOT NULL)
  ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO `keywords` (`bug_id`, `keywordid`) VALUES 
   (102, '3'), 
   (565, '4'), 
   (398, '1'), 
   (565, '2'), 
   (1352, '1'), 
   (57234, '2'), 
   (1363, '1'), 
   (72123, '2'), 
   (13634, '3');

keyworddefs表3（具有根据keywordid的关键字名称）：

CREATE TABLE `keyworddefs` (
    `id` int(11) NOT NULL,
    `name` varchar(200) NOT NULL,
    `description` varchar(200) NOT NULL) 
 ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO `keyworddefs` (`id`, `name`, `description`) VALUES 
     (1, 'J1', 'My J1 item'),
     (2, 'J2', 'My J2 item'),
     (3, 'K1', 'My K1 item'),
     (4, 'K2', 'My K2 item');

如何获得预期的输出？

Answer 1

在我看来，这太复杂了。

首先，您应该加入keywords和keyworddefs表ON keywords.keywordid = keyworddefs.name。您正在使用keyworddefs.id。这是一个数字。所以，你在20世纪90年代早期的老式逗号联接不会产生任何结果。

另一方面，您不需要加入keyworddefs表即可获得结果。

CCD_ 6运算很少产生CCD_ 7结果。因此，您应该将条件句放在SUM()的括号内，而不是放在括号外。

最后，您需要一个包含两个SUM()聚合的GROUP BY操作。一个应该以bug_status为条件，另一个不应该。

http://sqlfiddle.com/#！9/11b4/11/0

这样的东西应该行得通。

SELECT keywords.keywordid, 
       SUM(CASE WHEN bugs.bug_status IN ('CLOSED', 'RESOLVED')
                THEN bugs.cf1 + bugs.cf2
                ELSE 0 END)                                   completed,
       SUM(bugs.cf1 + bugs.cf2)                               total
  FROM bugs
  JOIN keywords ON bugs.bug_id = keywords.bug_id
 GROUP BY keywords.keywordid
 ORDER BY keywords.keywordid

如果您需要通过keywords.keywordid LIKE 'K%'筛选结果，您可以添加一个where子句。

Answer 2

从Ollie的注释中扩展查询，只需几处更改即可正常工作。

非常感谢奥利！

SELECT keyworddefs.name, 
SUM(CASE WHEN bugs.bug_status IN ('CLOSED', 'VERIFIED') THEN bugs.cf1 + bugs.cf2 ELSE 0 END) completed,
SUM(bugs.cf1 + bugs.cf2) total
FROM bugs
JOIN keywords ON bugs.bug_id = keywords.bug_id
JOIN keyworddefs ON keyworddefs.id = keywords.keywordid
WHERE keyworddefs.name LIKE 'K%' 
GROUP BY keywords.keywordid
ORDER BY keyworddefs.name DESC;