如何编写 SQL 查询以生成报告
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13052209/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to write a SQL query to generate a report
提问by jg8273
I need to generate a report based on the following tables:
我需要根据下表生成报告：
CALLS_FOR_PROPOSALS
ID|Name
--+------
1|Call 1
2|Call 2
3|Call 3
PROPOSALS
ID|Call ID|Title
--+-------+----------
1| 1|Proposal 1
2| 2|Proposal 2
3| 2|Proposal 3
PROPOSAL_STATUSES
ID|Proposal ID|Status ID
--+-----------+---------
1| 1| 1
2| 2| 1
3| 3| 1
4| 3| 2
STATUSES
ID|NAME
--+------------
1|Not Reviewed
2|Processing
3|Accepted
4|Rejected
With this sample data, there are 3 Calls for Proposals. There are three Proposals; one for Call 1, and two for Call 2. (Call 3 does not have any proposals.) Each proposal has at least one status assigned to it. When a row is inserted into the PROPOSALS table, a corresponding row is inserted into PROPOSAL_STATUSES, giving the Proposal an initial default status of 1 (Not Reviewed). Each time the status is changed, a new row is inserted into the PROPOSAL_STATUSES table, so that the history of status changes is preserved. I need to generate a report that shows for each Call, the number of Proposals submitted, and the number of Proposals that have had more than one status (i.e. the status has been changed from the default at least once.) For the sample data above, the results would look like this:
使用此示例数据，有 3 个提案征集。共有三个提案；一个用于呼叫 1，两个用于呼叫 2。（呼叫 3 没有任何提案。）每个提案至少有一个分配给它的状态。当一行被插入到 PROPOSALS 表中时，相应的行被插入到 PROPOSAL_STATUSES 中，从而使提案的初始默认状态为 1（未）。每次状态更改时，都会在 PROPOSAL_STATUSES 表中插入一个新行，以便保留状态更改的历史记录。我需要生成一份报告，显示每个呼叫、提交的提案数量以及具有多个状态的提案数量（即状态已从默认状态更改至少一次。）对于上面的示例数据，结果将如下所示：
Call Name|Proposals Submitted|Proposals Reviewed|
---------+-------------------+------------------+
Call 1 | 1| 0|
Call 2 | 2| 1|
Call 3 | 0| 0|
How would I write the SQL query to generate this report based on the above table structure? Thanks for your help.
我将如何编写 SQL 查询以根据上述表结构生成此报告？谢谢你的帮助。
采纳答案by Rapha?l Althaus
something like that should do the trick : Demo
这样的事情应该可以解决问题：Demo
SELECT Name as 'Call name',
submitted as 'Proposals Submitted',
SUM(CASE WHEN maxStatus > 1 THEN 1 ELSE 0 END) as 'Proposals Reviewed'
FROM
(SELECT cfp.Name,
sum(case when ps.Status_ID = 1 then 1 else 0 end) as submitted,
MAX(ps.Status_ID) as maxStatus
FROM CALLS_FOR_PROPOSALS cfp
LEFT JOIN PROPOSALS p on cfp.ID = p.CALL_ID
LEFT JOIN PROPOSAL_STATUSES ps on ps.PROPOSAL_ID = p.ID
GROUP BY cfp.Name) AS s
GROUP BY Name, submitted
