orders 表字段:
orderID 订单ID
ServerID 客服编号
OrderType 订单类型 ( 1 和 2 两种)
OrderItems字段ID
orderID 订单表ID外键
productID 产品ID
saleprice
quantity想要统计信息列表 如下:
(OrderType 1)数量 (OrderType1)金额 OrderType 2数量 (OrderType2)金额 总的订单数量 订单总金额
serverId 1
serverId 2
serverId 3
serverId 4
orderID 订单ID
ServerID 客服编号
OrderType 订单类型 ( 1 和 2 两种)
OrderItems字段ID
orderID 订单表ID外键
productID 产品ID
saleprice
quantity想要统计信息列表 如下:
(OrderType 1)数量 (OrderType1)金额 OrderType 2数量 (OrderType2)金额 总的订单数量 订单总金额
serverId 1
serverId 2
serverId 3
serverId 4
select (case when ordertype=1 then 1 else 0 end)数量1,
(case when ordertype=1 then 订单金额 else 0 end)金额1,
(case when ordertype=2 then 1 else 0 end)数量2,
(case when ordertype=2 then 订单金额 else 0 end)金额2,
count(*) 总订单数量1,
sum(订单金额) 订单总金额
from orders
quantity)
这个得出
saleprice 售价
quantity 数量
sum(case when a.ordertype=1 then b.saleprice*b.quantity else 0 end)金额1,
sum(case when a.ordertype=2 then b.quantity else 0 end)数量2,
sum(case when a.ordertype=2 then b.saleprice*b.quantity else 0 end)金额2,
sum(b.quantity) 总订单数量1,
sum(b.saleprice*b.quantity) 订单总金额
from orders a inner join orderitems on a.orderid=b.orderid
select sum(case when a.ordertype=1 then b.quantity else 0 end)数量1,
sum(case when a.ordertype=1 then b.saleprice*b.quantity else 0 end)金额1,
sum(case when a.ordertype=2 then b.quantity else 0 end)数量2,
sum(case when a.ordertype=2 then b.saleprice*b.quantity else 0 end)金额2,
sum(b.quantity) 总订单数量1,
sum(b.saleprice*b.quantity) 订单总金额
from orders a inner join orderitems b on a.orderid=b.orderid
select a.ServerID,
sum(case when a.ordertype=1 then b.quantity else 0 end)数量1,
sum(case when a.ordertype=1 then b.saleprice*b.quantity else 0 end)金额1,
sum(case when a.ordertype=2 then b.quantity else 0 end)数量2,
sum(case when a.ordertype=2 then b.saleprice*b.quantity else 0 end)金额2,
sum(b.quantity) 总订单数量1,
sum(b.saleprice*b.quantity) 订单总金额
from orders a inner join orderitems on a.orderid=b.orderid
group by a.serverid
select a.ServerID,
sum(case when a.ordertype=1 then b.quantity else 0 end)数量1,
sum(case when a.ordertype=1 then b.saleprice*b.quantity else 0 end)金额1,
sum(case when a.ordertype=2 then b.quantity else 0 end)数量2,
sum(case when a.ordertype=2 then b.saleprice*b.quantity else 0 end)金额2,
sum(b.quantity) 总订单数量1,
sum(b.saleprice*b.quantity) 订单总金额
from orders a inner join orderitems b on a.orderid=b.orderid
group by a.serverid