with cte as ( select productid,price,sum(count) as 数量,sum(Freight) as 邮费,sum(paymoney) as 总金额, (select sum(paymoney) from orderinfo where productid=t.productid and payway=1) as 支付宝, (select sum(paymoney) from orderinfo where productid=t.productid and payway=2) as 财付通, (select sum(paymoney) from orderinfo where productid=t.productid and payway=3) as 快钱 from orderinfo t)select b.productid,b.productName,b.showdate,b.enddate,a.price as 单价,a.数量, a.邮费, a. 总金额,a.支付宝,a.财付通,a.快钱 from cte a left join products b on a.productid=b.productid
with cte as ( select productid,price,sum(count) as 数量,sum(Freight) as 邮费,sum(paymoney) as 总金额, (select sum(paymoney) from orderinfo where productid=t.productid and payway=1) as 支付宝, (select sum(paymoney) from orderinfo where productid=t.productid and payway=2) as 财付通, (select sum(paymoney) from orderinfo where productid=t.productid and payway=3) as 快钱 from orderinfo t where productid=280)select b.productid,b.productName,b.showdate,b.enddate,a.price as 单价,a.数量, a.邮费, a.支付宝,a.财付通,a.快钱,a. 总金额 from cte a left join products b on a.productid=b.productid
select b.productid,b.productName,b.showdate,b.enddate, a.price as 单价, sum(a.count) as 数量,sum(a.Freight) as 邮费,sum(a.paymoney) as 总收入, sum(case when payway=1 then paymoney else 0 end) as 支付宝 from orderinfo a left join products b on a.productid=b.productid where a.productid=280 group by a.price,b.productid,b.productName,b.showdate,b.enddate
with cte as ( select productid,price,sum(count) as 数量,sum(Freight) as 邮费,sum(paymoney) as 总金额, sum(case when payway=1 then paymoney else 0 end) as 支付宝, sum(case when payway=2 then paymoney else 0 end) as 财付通, sum(case when payway=3 then paymoney else 0 end) as 快钱 from orderinfo t where productid=280)select b.productid,b.productName,b.showdate,b.enddate,a.price as 单价,a.数量, a.邮费, a.支付宝,a.财付通,a.快钱,a. 总金额 from cte a left join products b on a.productid=b.productid
消息 8120,级别 16,状态 1,第 1 行 选择列表中的列 'orderinfo.ProductID' 无效,因为该列没有包含在聚合函数或 GROUP BY 子句中。
280 仅需49元.. 2010-10-31 2010-11-30 49.0 53 408 1897.0 500.0 500.0 2897.0
select productid,price,sum(count) as 数量,sum(Freight) as 邮费,sum(paymoney) as 总金额,
(select sum(paymoney) from orderinfo where productid=t.productid and payway=1) as 支付宝,
(select sum(paymoney) from orderinfo where productid=t.productid and payway=2) as 财付通,
(select sum(paymoney) from orderinfo where productid=t.productid and payway=3) as 快钱
from orderinfo t)select b.productid,b.productName,b.showdate,b.enddate,a.price as 单价,a.数量, a.邮费,
a. 总金额,a.支付宝,a.财付通,a.快钱
from cte a
left join products b on a.productid=b.productid
with cte as (
select productid,price,sum(count) as 数量,sum(Freight) as 邮费,sum(paymoney) as 总金额,
(select sum(paymoney) from orderinfo where productid=t.productid and payway=1) as 支付宝,
(select sum(paymoney) from orderinfo where productid=t.productid and payway=2) as 财付通,
(select sum(paymoney) from orderinfo where productid=t.productid and payway=3) as 快钱
from orderinfo t
where productid=280)select b.productid,b.productName,b.showdate,b.enddate,a.price as 单价,a.数量, a.邮费,
a.支付宝,a.财付通,a.快钱,a. 总金额
from cte a
left join products b on a.productid=b.productid
sum(a.count) as 数量,sum(a.Freight) as 邮费,sum(a.paymoney) as 总收入,
sum(case when payway=1 then paymoney else 0 end) as 支付宝
from orderinfo a
left join products b on a.productid=b.productid
where a.productid=280
group by a.price,b.productid,b.productName,b.showdate,b.enddate
with cte as (
select productid,price,sum(count) as 数量,sum(Freight) as 邮费,sum(paymoney) as 总金额,
sum(case when payway=1 then paymoney else 0 end) as 支付宝,
sum(case when payway=2 then paymoney else 0 end) as 财付通,
sum(case when payway=3 then paymoney else 0 end) as 快钱
from orderinfo t
where productid=280)select b.productid,b.productName,b.showdate,b.enddate,a.price as 单价,a.数量, a.邮费,
a.支付宝,a.财付通,a.快钱,a. 总金额
from cte a
left join products b on a.productid=b.productid
消息 8120,级别 16,状态 1,第 1 行
选择列表中的列 'orderinfo.ProductID' 无效,因为该列没有包含在聚合函数或 GROUP BY 子句中。