select b.[name],sum(a.price) from bill a join accounttype b on a.type_id=b.type_id group by b.type_id,a.[name] union all select b.[name],sum(a.[money]) from pay a join accounttype b on a.type_id=b.type_id group by b.type_id,a.[name]
select b.[name] 类型名称,sum(a.price)*sum(a.amount) 金额 from bill a join accounttype b on a.type_id=b.type_id group by b.type_id,a.[name] union all select b.[name],sum(a.[money]) from pay a join accounttype b on a.type_id=b.type_id group by b.type_id,a.[name]
这样也不敢说科学不科学(因为俺就不科学:)),不过至少出结果还是应该不会太难滴~ *^_^* SELECT Code.Name AS "类型名称" , SUM(bill.foodname*bill.amount) as "金额" FROM bill RIGHT.JOIN accounttype as "Code" ON bill.type_id = Code.type_id --需要指定客人时,就加这一行 WHERE Code.guest_id='...' ------------------------------ GROUP BY Code.type_id 内存不够,SQL Server让我删了,没调试,出错的话8好意思:P
select b.[name],sum(a.price) from bill a join accounttype b on a.type_id=b.type_id group by b.type_id,a.[name] union all select b.[name],sum(a.[money]) from pay a join accounttype b on a.type_id=b.type_id group by b.type_id,a.[name]
select b.[name] 类型名称,sum(a.price)*sum(a.amount) 金额 from bill a join accounttype b on a.type_id=b.type_id where a.guest_id='参数' group by b.type_id,a.[name] union all select b.[name],sum(a.[money]) from pay a join accounttype b on a.type_id=b.type_id where a.guest_id='参数' group by b.type_id,a.[name] 就可以了
union all
select b.[name],sum(a.[money]) from pay a join accounttype b on a.type_id=b.type_id group by b.type_id,a.[name]
union all
select b.[name],sum(a.[money]) from pay a join accounttype b on a.type_id=b.type_id group by b.type_id,a.[name]
*^_^*
SELECT Code.Name AS "类型名称"
, SUM(bill.foodname*bill.amount) as "金额"
FROM bill
RIGHT.JOIN accounttype as "Code" ON bill.type_id = Code.type_id
--需要指定客人时,就加这一行
WHERE Code.guest_id='...'
------------------------------
GROUP BY Code.type_id
内存不够,SQL Server让我删了,没调试,出错的话8好意思:P
你用UION ALL的意思是不是把明细和合计写在一张单上?
union all
select b.[name],sum(a.[money]) from pay a join accounttype b on a.type_id=b.type_id group by b.type_id,a.[name]
from bill a join accounttype b on a.type_id=b.type_id where a.guest_id='参数' group by b.type_id,a.[name]
union all
select b.[name],sum(a.[money]) from pay a join accounttype b on a.type_id=b.type_id where a.guest_id='参数' group by b.type_id,a.[name]
就可以了