SQL复杂查询分组 [英] SQL complex group by query

问题描述

我有一个名为

tpayment_order 

的表，如下所示，

as following,

ID    date created    order_type    order_values
1      2011              C            250
2      2012              T            260
3      2011              C            150
4      2010              P            650
5      2012              C            850

等等...

运行查询后-

so on...

after running query --

select order_type, sum(order_value)as Total 
from tpayment_order
group by order_type

我知道了-

I got --

order_type     Total

    C         58020.02
    P         46673.59
    T          75.17

是否可以编写查询，使查询结果类似于-

Is it possible to write query which will give the result like -

date_created    order_type         total
      2010           C                20454.34
                     P                3435.76
                     T                5675.676
      2011           C                20454.34
                     P                3435.76
                     T                5675.676
     2012            C                20454.34
                     P                3435.76
                     T                5675.676

有任何等效的方法来获取这样的输出数据吗?

Any equivalent appoach to get output data like this ?

推荐答案

尝试以下方法:

Try This:

select date_created,order_type,sum(order_value)
from tpayment_order
group by date_created,order_type

试试看，作为替代

Hi,

try this, as a alternative

if object_id('tpayment_order') is Not Null drop view tpayment_order;
GO
create view tpayment_order( ID, date_created, order_type, order_value ) 
as
select           1, 2011, 'C', 250
union all select 2, 2012, 'T', 260
union all select 3, 2011, 'C', 150
union all select 4, 2010, 'P', 650
union all select 5, 2012, 'C', 850
GO
select * from tpayment_order order by date_created, order_type

select date_created = isNull( cast( date_created as varchar(7) ) , case when grouping(date_created) = 0 then 'UNKNOWN' else '' end)
,      order_type = isNull( cast( order_type as varchar(7) ), case when grouping(order_type) = 0 then 'UNKNOWN' else '' end )
,      order_value = sum(order_value)
from tpayment_order
GROUP BY Cube(date_created, order_type);

谢谢的
所以说我们所有人！

thank''s
So say we all!

这篇关于SQL复杂查询分组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！