62,073
社区成员
发帖
与我相关
我的任务
分享
select a.*,b.订货数
from
(
select
下单人,
订单数=COUNT(1),
成功订单数=sum(case 订单状态 when 1 then 1 else 0 end),
成功金额=sum(case 订单状态 when 1 then (总价格-运费) end),
拒收单数=sum(case 订单状态 when 2 then 1 else 0 end),
拒收金额=sum(case 订单状态 when 2 then (总价格-运费) end),
订货金额=sum(总价格-运费)
from @订单表 group by 下单人
) a
join
(
select
下单人,
订货数=sum(普通价格数量+优惠价格数量)
from @订单产品表 a join @订单表 b on a.订单号=b.订单号
GROUP BY 下单人
) b
on a.下单人=b.下单人
--> Test Data: @t1
declare @t1 table ([订单号] int,[下单人] varchar(5),[订单状态] int,[下单时间] datetime,[总价格] int,[运费] int)
insert into @t1
select 1111,'js001',1,'2008-9-23 17:40:11',3333,33 union all
select 2222,'js001',2,'2008-9-23 17:40:51',22,0
--> Test Data: @t2
declare @t2 table ([订单号] int,[产品名称] varchar(8),[普通价格数量] int,[优惠价格数量] int)
insert into @t2
select 1111,'诺基亚',11,1 union all
select 1111,'摩托若啦',2,2 union all
select 2222,'诺基亚',11,1 union all
select 2222,'摩托若啦',2,2
--Code
select a.*,b.订货数
from
(
select
下单人,
订单数=COUNT(1),
成功订单数=sum(case 订单状态 when 1 then 1 else 0 end),
成功金额=sum(case 订单状态 when 1 then (总价格-运费) end),
拒收单数=sum(case 订单状态 when 2 then 1 else 0 end),
拒收金额=sum(case 订单状态 when 2 then (总价格-运费) end),
订货金额=sum(总价格-运费)
from @t1 group by 下单人
) a
join
(
select
下单人=(select distinct 下单人 from @t1 where 订单号 in (select distinct 订单号 from @t2 )),
订货数=sum(普通价格数量+优惠价格数量)
from @t2 t
) b
on a.下单人=b.下单人
--Drop
--Result
/*
下单人 订单数 成功订单数 成功金额 拒收单数 拒收金额 订货金额 订货数
----- ----------- ----------- ----------- ----------- ----------- ----------- -----------
js001 2 1 3300 1 22 3322 32
*/
select distinct 下单人,(select count(*) from table_1 where 下单人='js001') as 订单数,
(select sum(普通价格数量)+sum(优惠价格数量) from table_2 where 订单号 in (select 订单号 from table_1 where 下单人='js001')) as 订货数,
(select sum(总价格)-sum(运费) from table_1 where 下单人='js001') as 订货金额,
(select count(*) from table_1 where 下单人='js001' and 订单状态=1) as 成功订单数,
(select sum(总价格)-sum(运费) from table_1 where 下单人='js001' and 订单状态=1) as 成功金额,
(select count(*) from table_1 where 下单人='js001' and 订单状态=2) as 拒收单数,
(select sum(总价格)-sum(运费) from table_1 where 下单人='js001' and 订单状态=2) as 拒收金额
from table_1