select * from tb t where not exists(select * from tb where t.快递地区=快递地区 and 产品ID<t.产品ID)
---测试数据--- if object_id('[tb]') is not null drop table [tb] go create table [tb]([订单号] varchar(6),[产品ID] int,[快递地区] varchar(4),[货物数量] int,[快递费] int) insert [tb] select 'A10001',10,'广东',1,25 union all select 'A10001',12,'广东',1,25 union all select 'A10002',10,'上海',2,25 union all select 'A10003',12,'北京',1,35 union all select 'A10003',10,'北京',2,35
---查询--- select 快递地区, sum(货物数量) as 货物数量, 快递费 from tb group by 快递地区, 快递费---结果--- 快递地区 货物数量 快递费 ---- ----------- ----------- 广东 2 25 上海 2 25 北京 3 35(所影响的行数为 3 行)
select 快递地区,count(1),sum(快递费) from tb t where not exists(select * from tb where t.快递地区=快递地区 and 产品ID<t.产品ID) group by 快递地区
if object_id('tb')is not null drop table tb go create table tb([订单号] varchar(10),[产品ID] int,[快递地区] varchar(10),[货物数量] int,[(总)快递费] int) insert tb select 'A10001',10,'广东',1,25 insert tb select 'A10001',12,'广东',1,25 insert tb select 'A10002',10,'上海',2,25 insert tb select 'A10003',12,'北京',1,35 insert tb select 'A10003',10,'北京',2,35 goselect distinct [快递地区], [货物数量]=(select sum([货物数量]) from tb where 订单号=t.订单号), 快递费=[(总)快递费] from tb t /* 快递地区 货物数量 快递费 ---------- ----------- ----------- 北京 3 35 广东 2 25 上海 2 25(3 行受影响) */
select [快递地区], sum(isnull([货物数量],0))as[货物数量], [(总)快递费] from tb group by [快递地区],[(总)快递费]
select 快递地区, sum(货物数量) as 货物数量,max((总)快递费) from tb group by 快递地区
--订单号 产品ID 快递地区 货物数量 (总)快递费 --A10001 10 广东 1 25 --A10001 12 广东 1 25 --A10002 10 上海 2 25 --A10003 12 北京 1 35 --A10003 10 北京 2 35 -- --查询结果 --广东 2 25 --上海 2 25 --北京 3 35DECLARE @T TABLE (订单号 VARCHAR(10), 产品ID INT, 快递地区 NVARCHAR(10), 货物数量 INT, [(总)快递费] INT);INSERT INTO @T SELECT 'A10001' ,10 , '广东' , 1 , 25 UNION ALL SELECT 'A10001' , 12 , '广东' , 1 , 25 UNION ALL SELECT 'A10002' , 10 , '上海' , 2 , 25 UNION ALL SELECT 'A10003' , 12 , '北京' , 1 , 35 UNION ALL SELECT 'A10003', 10 ,'北京' , 2 , 35 SELECT * FROM @TSELECT 快递地区,COUNT(货物数量),MAX([(总)快递费]) FROM @T GROUP BY 快递地区
create table #tb([订单号] varchar(6),[产品ID] int,[快递地区] varchar(4),[货物数量] int,[快递费] int) insert #tb select 'A10001',10,'广东',1,25 union all select 'A10001',12,'广东',1,25 union all select 'A10002',10,'上海',2,25 union all select 'A10003',12,'北京',1,35 union all select 'A10003',10,'北京',2,35select t.快递地区,count(b.[订单号]),t.快递费 from #tb b right join ( select 快递地区,sum(快递费) 快递费 from #tb t where not exists(select * from #tb where t.快递地区=快递地区 and 产品ID<t.产品ID) group by 快递地区 ) t on t.快递地区=b.快递地区 group by t.快递地区,t.快递费
修改下 create table #tb([订单号] varchar(6),[产品ID] int,[快递地区] varchar(4),[货物数量] int,[快递费] int) insert #tb select 'A10001',10,'广东',1,25 union all select 'A10001',12,'广东',1,25 union all select 'A10002',10,'上海',2,25 union all select 'A10003',12,'北京',1,35 union all select 'A10003',10,'北京',2,35select t.快递地区,sum(b.[货物数量]) 数量,t.快递费 from #tb b right join ( select 快递地区,sum(快递费) 快递费 from #tb t where not exists(select * from #tb where t.快递地区=快递地区 and 产品ID<t.产品ID) group by 快递地区 ) t on t.快递地区=b.快递地区 group by t.快递地区,t.快递费 快递地区 数量 快递费 ---- ----------- ----------- 北京 3 35 广东 2 25 上海 2 25(3 行受影响)
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([订单号] varchar(6),[产品ID] int,[快递地区] varchar(4),[货物数量] int,[快递费] int)
insert [tb]
select 'A10001',10,'广东',1,25 union all
select 'A10001',12,'广东',1,25 union all
select 'A10002',10,'上海',2,25 union all
select 'A10003',12,'北京',1,35 union all
select 'A10003',10,'北京',2,35
---查询---
select
快递地区,
sum(货物数量) as 货物数量,
快递费
from
tb
group by
快递地区,
快递费---结果---
快递地区 货物数量 快递费
---- ----------- -----------
广东 2 25
上海 2 25
北京 3 35(所影响的行数为 3 行)
drop table tb
go
create table tb([订单号] varchar(10),[产品ID] int,[快递地区] varchar(10),[货物数量] int,[(总)快递费] int)
insert tb select 'A10001',10,'广东',1,25
insert tb select 'A10001',12,'广东',1,25
insert tb select 'A10002',10,'上海',2,25
insert tb select 'A10003',12,'北京',1,35
insert tb select 'A10003',10,'北京',2,35
goselect distinct
[快递地区],
[货物数量]=(select sum([货物数量]) from tb where 订单号=t.订单号),
快递费=[(总)快递费]
from tb t
/*
快递地区 货物数量 快递费
---------- ----------- -----------
北京 3 35
广东 2 25
上海 2 25(3 行受影响)
*/
sum(isnull([货物数量],0))as[货物数量],
[(总)快递费]
from tb
group by [快递地区],[(总)快递费]
from tb group by 快递地区
--A10001 10 广东 1 25
--A10001 12 广东 1 25
--A10002 10 上海 2 25
--A10003 12 北京 1 35
--A10003 10 北京 2 35
--
--查询结果
--广东 2 25
--上海 2 25
--北京 3 35DECLARE @T TABLE
(订单号 VARCHAR(10),
产品ID INT,
快递地区 NVARCHAR(10),
货物数量 INT,
[(总)快递费] INT);INSERT INTO @T
SELECT 'A10001' ,10 , '广东' , 1 , 25
UNION ALL
SELECT 'A10001' , 12 , '广东' , 1 , 25
UNION ALL
SELECT 'A10002' , 10 , '上海' , 2 , 25
UNION ALL
SELECT 'A10003' , 12 , '北京' , 1 , 35
UNION ALL
SELECT 'A10003', 10 ,'北京' , 2 , 35 SELECT * FROM @TSELECT 快递地区,COUNT(货物数量),MAX([(总)快递费]) FROM @T
GROUP BY 快递地区
insert #tb
select 'A10001',10,'广东',1,25 union all
select 'A10001',12,'广东',1,25 union all
select 'A10002',10,'上海',2,25 union all
select 'A10003',12,'北京',1,35 union all
select 'A10003',10,'北京',2,35select t.快递地区,count(b.[订单号]),t.快递费 from #tb b right join
(
select 快递地区,sum(快递费) 快递费 from #tb t where not exists(select * from #tb where t.快递地区=快递地区 and 产品ID<t.产品ID) group by 快递地区
) t on t.快递地区=b.快递地区 group by t.快递地区,t.快递费
订单号 产品ID 快递地区 货物数量 (总)快递费
A10001 10 广东 1 25
A10001 12 广东 1 25
A10002 10 上海 2 25
A10003 12 北京 1 35
A10003 10 北京 2 35
A10004 10 广东 1 25
A10004 12 广东 1 25
A10005 10 上海 1 15
A10006 12 北京 1 15查询结果
广东 4 50
上海 3 40
北京 4 50是统计产品快递区域的货物量和快递费.本来我是可以直接在快递表查询的.但因为系统的功能需要统计到单件产品的快递区域的统计.所以从订单产品进行查询统计
create table #tb([订单号] varchar(6),[产品ID] int,[快递地区] varchar(4),[货物数量] int,[快递费] int)
insert #tb
select 'A10001',10,'广东',1,25 union all
select 'A10001',12,'广东',1,25 union all
select 'A10002',10,'上海',2,25 union all
select 'A10003',12,'北京',1,35 union all
select 'A10003',10,'北京',2,35select t.快递地区,sum(b.[货物数量]) 数量,t.快递费 from #tb b right join
(
select 快递地区,sum(快递费) 快递费 from #tb t where not exists(select * from #tb where t.快递地区=快递地区 and 产品ID<t.产品ID) group by 快递地区
) t on t.快递地区=b.快递地区 group by t.快递地区,t.快递费
快递地区 数量 快递费
---- ----------- -----------
北京 3 35
广东 2 25
上海 2 25(3 行受影响)