在一个选择中有两个不同的分组级别
我有这个：
SELECT
invoice_number, invoice_year, invoice_month, invoice_amount,
payment_year, payment_month, payment_amount
FROM payments_table结果：
所以我有四张发票。从2015/01年度开始的两张发票加起来是900张，2015/02年度的2张发票加起来是950张。
我想要这个结果：
因此，我只想用invoice_amount一次对invoice_year和invoice_month的invoice_number进行求和。我想总结一下payment_amount，invoice_year，invoice_month，payment_year和payment_month。
如果我使用GROUP BY invoice_year, invoice_month, payment_year, payment_month，我为SUM(payment_amount)得到了正确的金额，但为SUM(invoice_amount)得到了错误的金额。
有什么建议吗？
回答 4
Stack Overflow用户
发布于 2015-12-03 13:31:34
我自己找到了解决办法。谢谢大家和我一起思考：
select b.invoice_year
, b.invoice_month
, b.invoice_amount
, c.payment_year
, c.payment_month
, c.payment_amount
from (
select a.invoice_year
, a.invoice_month
, sum(a.invoice_amount) as invoice_amount
from (
select distinct invoice_number
, invoice_year
, invoice_month
, invoice_amount
from payments
) a
group by a.invoice_year
, a.invoice_month
) b
inner join (
select a.invoice_year
, a.invoice_month
, a.payment_year
, a.payment_month
, sum(a.payment_amount) as payment_amount
from (
select invoice_year
, invoice_month
, payment_year
, payment_month
, payment_amount
from payments
) a
group by a.invoice_year
, a.invoice_month
, a.payment_year
, a.payment_month
) as c
on c.invoice_year = b.invoice_year
and c.invoice_month = b.invoice_month这正是我想要的结果。
Stack Overflow用户
发布于 2015-12-03 04:30:09
您需要的查询是：
select a.invoice_year, a.invoice_month, a.payment_year, a.payment_month,
SUM(payment_amount), b.sumup
from payments_table a
inner join
(select invoice_year, invoice_month, sum(payment_amount) sumup
from payments_table
group by invoice_year, invoice_month) b
ON (a.invoice_year = b.invoice_year
and a.invoice_month = b.invoice_month )
GROUP BY a.invoice_year, a.invoice_month, a.payment_year, a.payment_month 但是让我说，对于你提供的样本数据，invoice_year and invoice_month的总数是900，而不是950。
在小提琴上看到它：http://sqlfiddle.com/#!9/46249/4
请注意，我在MySql中做了小提琴，但是对于SQLServer来说应该是一样的，因为没有特定的函数或语法，只有普通的SQL。我之所以在Mysql中这样做，是因为有时带有SQLFiddle的SQLServer会变得不稳定。
编辑
结果是，我总结了错误的字段并缺少了一列，所以正确的查询应该是：
select a.invoice_year, a.invoice_month,
b.incount,
SUM(payment_amount) invoice_amount,
a.payment_year,
a.payment_month,
b.payment__amount
from payments_table a
inner join
(select invoice_year, invoice_month,
count(distinct invoice_amount) incount,
sum(distinct invoice_amount) payment__amount
from payments_table
group by invoice_year, invoice_month) b
ON ( a.invoice_year = b.invoice_year
and a.invoice_month = b.invoice_month )
GROUP BY a.invoice_year, a.invoice_month, a.payment_year, a.payment_month 这将给出你想要的结果。请看这里：http://sqlfiddle.com/#!9/46249/10
Stack Overflow用户
发布于 2015-12-03 04:37:41
在这个sql，http://sqlfiddle.com/#!6/7d789/27中，我分解了您的查询的组件，并从小部件中进行了一个完整的查询，以得到您想要的。
https://stackoverflow.com/questions/34057268
复制相似问题
腾讯云开发者
