sql - MySql 查询同一张表以收集最新和年初至今的信息

Question

虽然我确信有一个简单的答案，但我已经为这个问题苦苦挣扎了几个小时。我正在尝试从同一张表中收集月度信息和年初至今的信息。我还加入了第二个表来收集组名。

费用表:

+-----------------+---------------+------+-----+---------+----------------+
| Field           | Type          | Null | Key | Default | Extra          |
+-----------------+---------------+------+-----+---------+----------------+
| id              | int(5)        | NO   | PRI | NULL    | auto_increment |
| account         | char(14)      | NO   |     | NULL    |                |
| batch           | int(5)        | NO   |     | NULL    |                |
| date            | date          | NO   |     | NULL    |                |
| description     | varchar(50)   | NO   |     | NULL    |                |
| debit           | decimal(10,2) | NO   |     | NULL    |                |
| credit          | decimal(10,2) | NO   |     | NULL    |                |
| account_data_id | varchar(14)   | NO   |     | NULL    |                |
+-----------------+---------------+------+-----+---------+----------------+

account_data 表:

+--------------+-------------+------+-----+---------+----------------+
| Field        | Type        | Null | Key | Default | Extra          |
+--------------+-------------+------+-----+---------+----------------+
| id           | int(5)      | NO   | PRI | NULL    | auto_increment |
| account_code | varchar(14) | NO   |     | NULL    |                |
| group_name   | varchar(30) | NO   |     | NULL    |                |
+--------------+-------------+------+-----+---------+----------------+

我可以很容易地得出月度或年初至今的信息，但无论我做什么，我都无法同时拥有两者。以下是我能想到的最接近的结果，但它需要很长时间才能执行，而且结果不是预期的那样:

SELECT account_data.group_name, sum(m.debit)- sum(m.credit) AS month, sum(y.debit)- SUM(y.credit) AS year
FROM account_data
INNER JOIN expense m ON m.account_data_id = account_data.id
AND MONTH(m.`date`) IN (7,8,9,10,11,12)
LEFT JOIN expense y ON y.account_data_id = account_data.id
AND MONTH(y.`date`) IN (7)
GROUP BY account_data.group_name

这就是我想要完成的:

+--------------+----------+---------+
| group_name   | month    | year    | 
+--------------+----------+---------+
| Payroll      | 10,000   | 50,000  |
| Payroll Tax  | 1,000    | 5,000   |
| Benefits     | 500      | 1,000   |  
+--------------+----------+---------+

非常感谢任何帮助。我是新来的，我希望我遵守了所有规则并向你们中的任何人提供了足够的信息来提供帮助，但如果没有，请告诉我，我会提供更多。

@philwinkle - 你的解决方案，适当修改:

SELECT ad.group_name,
IF(MONTH(e.date) IN (7,8,9,10,11,12), SUM(e.debit) - SUM(e.credit),'' ),
IF(MONTH(e.date) = 7, SUM(e.debit) - SUM(e.credit),'' )
FROM account_data ad
LEFT JOIN expense_2011 e ON e.account_data_id = ad.id
WHERE e.account_data_id > 7
GROUP BY ad.group_name

Answer 1

这里的解决方案是使用条件求和...我将放置伪代码，当我 100% 肯定以下解决方案有效并有效时，我将进行编辑:

SELECT ad.group_name,
(if(e.account_data_id IN (7,8,9,10,11,12),sum(e.debit)- sum(e.credit)) AS month,
(if(e.account_data_id=7,sum(e.debit)- sum(e.credit)) AS year
FROM account_data ad
LEFT JOIN expense e ON e.account_data_id = ad.id
WHERE e.account_data_id > 7
GROUP BY account_data.group_name