当我在下面的MySQL查询中编写代码时,它为我提供了正确的输出,如后所述,我想完成而不是完成计数。我已经尝试过但是失败了。
http://sqlfiddle.com/#!9/c8dab/1
如何通过扩展到以下查询来获取未完成的计数?
SELECT keyworddefs.name as sprint,
SUM(CASE WHEN bugs.bug_status IN ('CLOSED', 'VERIFIED')
THEN bugs.cf1 + bugs.cf2
ELSE 0 END) completed,
SUM(bugs.cf1 + bugs.cf2) total,
(CASE WHEN SUM(CASE WHEN bugs.bug_status IN ('CLOSED', 'VERIFIED')
THEN bugs.cf1 + bugs.cf2
ELSE 0 END)=SUM(bugs.cf1 + bugs.cf2)
THEN 'Completed'
ELSE 'Not Completed' END) status
FROM bugs
JOIN keywords ON bugs.bug_id = keywords.bug_id
JOIN keyworddefs ON keyworddefs.id = keywords.keywordid
GROUP BY keywords.keywordid
ORDER BY keyworddefs.name DESC;
+--------+------------------------+--------------------+---------------+
| name | completed | total | status |
+--------+------------------------+--------------------+---------------+
| K2 | 14 | 14 | Completed |
| J2 | 16 | 24 | Not Completed |
| J1 | 0 | 5 | Not Completed |
+--------+------------------------+--------------------+---------------+
+---------------+--------+
| status | count |
+------------------------+
| Completed | 1 |
| Not Completed | 2 |
+------------------------+
就像草莓说的那样,给它一个派生的表名。
架构:
-- bugs table1 (master table) :
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
(101, '2016-07-19', 3, 2, 'RESOLVED'),
(102, '2016-07-19', 2, 1, 'VERIFIED'),
(103, '2016-07-19', 2, 1, 'VERIFIED'),
(103, '2016-07-19', 2, 1, 'VERIFIED'),
(1363, '2016-07-19', 2, 1, 'VERIFIED'),
(1352, '2016-07-19', 2, 1, 'VERIFIED'),
(102, '2016-07-19', 2, 1, 'VERIFIED'),
(102, '2016-07-22', 2, 2, 'CLOSED'),
(103, '2016-07-22', 2, 2, 'CLOSED'),
(103, '2016-07-22', 2, 2, 'CLOSED'),
(102, '2016-07-19', 3, 2, 'NEW'),
(102, '2016-07-19', 2, 1, 'REOPENED'),
(102, '2016-07-19', 2, 1, 'CLOSED'),
(102, '2016-07-19', 2, 1, 'VERIFIED'),
(1363, '2016-07-19', 2, 1, 'VERIFIED'),
(1352, '2016-07-19', 2, 1, 'VERIFIED'),
(565, '2016-07-19', 2, 1, 'VERIFIED'),
(398, '2016-07-22', 2, 2, 'CLOSED'),
(565, '2016-07-22', 2, 2, 'CLOSED'),
(9872, '2016-07-22', 2, 2, 'CLOSED');
-- keywords table2 (having keyword ids):
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
(101, '1'), (102, '2'), (103, '3'), (104, '4'), (105, '1'), (106, '1'), (107, '2'), (108, '3'), (109, '4');
-- keyworddefs table3 (having keyword names according to 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, 'K2', 'My K2 item'), (4, 'K2', 'My K2 item');
您的查询:
SELECT keyworddefs.name as sprint,
SUM(CASE WHEN bugs.bug_status IN ('CLOSED', 'VERIFIED')
THEN bugs.cf1 + bugs.cf2
ELSE 0 END) completed_story_points,
SUM(bugs.cf1 + bugs.cf2) total_story_points,
(CASE WHEN SUM(CASE WHEN bugs.bug_status IN ('CLOSED', 'VERIFIED')
THEN bugs.cf1 + bugs.cf2
ELSE 0 END)=SUM(bugs.cf1 + bugs.cf2)
THEN 'Completed'
ELSE 'Not Completed' END) sprint_status
FROM bugs
JOIN keywords ON bugs.bug_id = keywords.bug_id
JOIN keyworddefs ON keyworddefs.id = keywords.keywordid
GROUP BY keywords.keywordid
ORDER BY keyworddefs.name DESC;
您的输出:
+--------+------------------------+--------------------+---------------+
| sprint | completed_story_points | total_story_points | sprint_status |
+--------+------------------------+--------------------+---------------+
| K2 | 14 | 14 | Completed |
| J2 | 16 | 24 | Not Completed |
| J1 | 0 | 5 | Not Completed |
+--------+------------------------+--------------------+---------------+
通缉:
+---------------+--------+
| status | count |
+------------------------+
| Completed | 1 |
| Not Completed | 2 |
+------------------------+
懒惰的方法(意思是,无需过多考虑):
SELECT sprint_status,count(*) AS count
FROM
(
SELECT keyworddefs.name as sprint,
SUM(CASE WHEN bugs.bug_status IN ('CLOSED', 'VERIFIED')
THEN bugs.cf1 + bugs.cf2
ELSE 0 END) completed_story_points,
SUM(bugs.cf1 + bugs.cf2) total_story_points,
(CASE WHEN SUM(CASE WHEN bugs.bug_status IN ('CLOSED', 'VERIFIED')
THEN bugs.cf1 + bugs.cf2
ELSE 0 END)=SUM(bugs.cf1 + bugs.cf2)
THEN 'Completed'
ELSE 'Not Completed' END) sprint_status
FROM bugs
JOIN keywords ON bugs.bug_id = keywords.bug_id
JOIN keyworddefs ON keyworddefs.id = keywords.keywordid
GROUP BY keywords.keywordid
) xDerived
GROUP BY sprint_status
ORDER BY sprint_status;
输出:
+---------------+-------+
| sprint_status | count |
+---------------+-------+
| Completed | 1 |
| Not Completed | 2 |
+---------------+-------+
随便点什么
每个派生表都需要一个名称。上面包装的块是一个。所以我们选择了这个名字xDerived
。此后未按名称使用(尽管可以使用)。但是它仍然需要一个名称,否则会发生错误。
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句