select 序号, 商品, 点名称, 折扣, sum(销售金额) 销售金额 from 表A group by 序号, 商品, 点名称, 折扣 order by 序号, 商品, 点名称, 折扣
Select * Into # From ( select 1 As Id ,'电磁阀' As Cp,'北京店' As dm, 0.8 As zq , 10000 As Je Union All select 2 As Id ,'电磁阀' As Cp,'南京店' As dm, 0.85 As zq , 9000 As Je Union All select 3 As Id ,'电磁阀' As Cp,'北京店' As dm, 0.9 As zq , 8000 As Je Union All select 4 As Id ,'电磁阀' As Cp,'南京店' As dm, 0.85 As zq , 9000 As Je Union All select 5 As Id ,'电磁阀' As Cp,'上海店' As dm, 0.7 As zq , 30000 As Je Union All select 6 As Id ,'电磁阀' As Cp,'南京店' As dm, 0.9 As zq , 40000 As Je Union All select 7 As Id ,'电磁阀' As Cp,'广州店' As dm, 1.0 As zq , 500000 As Je Union All select 8 As Id ,'电磁阀' As Cp,'上海2店' As dm, 0.9 As zq , 40000 As Je ) As T;With Cte As ( Select Cp,Dm From # Group By Cp,Dm Having Count(1)>1 ) Select Distinct Cp,Dm,Zq,Je From # A Where Exists (Select 1 From Cte B Where A.Cp=B.Cp And A.Dm=B.Dm) Order By Cp,Dm,ZqDrop Table #
select 序号, 商品, 点名称, 折扣, sum(销售金额) 销售金额
from 表A
group by 序号, 商品, 点名称, 折扣
order by 序号, 商品, 点名称, 折扣
(
select 1 As Id ,'电磁阀' As Cp,'北京店' As dm, 0.8 As zq , 10000 As Je Union All
select 2 As Id ,'电磁阀' As Cp,'南京店' As dm, 0.85 As zq , 9000 As Je Union All
select 3 As Id ,'电磁阀' As Cp,'北京店' As dm, 0.9 As zq , 8000 As Je Union All
select 4 As Id ,'电磁阀' As Cp,'南京店' As dm, 0.85 As zq , 9000 As Je Union All
select 5 As Id ,'电磁阀' As Cp,'上海店' As dm, 0.7 As zq , 30000 As Je Union All
select 6 As Id ,'电磁阀' As Cp,'南京店' As dm, 0.9 As zq , 40000 As Je Union All
select 7 As Id ,'电磁阀' As Cp,'广州店' As dm, 1.0 As zq , 500000 As Je Union All
select 8 As Id ,'电磁阀' As Cp,'上海2店' As dm, 0.9 As zq , 40000 As Je
) As T;With Cte As
(
Select Cp,Dm From # Group By Cp,Dm Having Count(1)>1
)
Select Distinct Cp,Dm,Zq,Je From # A
Where Exists (Select 1 From Cte B Where A.Cp=B.Cp And A.Dm=B.Dm)
Order By Cp,Dm,ZqDrop Table #