代码 名称 型号 数量 金额
001.01 a a1 5 100
001.02 b b1 3 200
002.01 c c1 2 295
002.02 d d1 2 235按代码的上一级汇总 ,名称,型号取价钱最大的值,数量,金额取合计想要结果:
001 b b1 8 300
002 c c1 4 520请教要怎么写?主要就是不会取汇总行的名称,型号,先谢谢了!
001.01 a a1 5 100
001.02 b b1 3 200
002.01 c c1 2 295
002.02 d d1 2 235按代码的上一级汇总 ,名称,型号取价钱最大的值,数量,金额取合计想要结果:
001 b b1 8 300
002 c c1 4 520请教要怎么写?主要就是不会取汇总行的名称,型号,先谢谢了!
from tb
group by left(代码,3),名称, 型号,数量
insert into tb values('001.01' , 'a' , 'a1' , 5 , 100 )
insert into tb values('001.02' , 'b' , 'b1' , 3 , 200 )
insert into tb values('002.01' , 'c' , 'c1' , 2 , 295 )
insert into tb values('002.02' , 'd' , 'd1' , 2 , 235 )
goselect m.* , n.数量 , n.金额 from
(select 代码 = left(代码,charindex('.',代码) - 1) ,名称 , 型号 from tb t where charindex('.',代码) > 0 and 金额 = (select max(金额) from tb where charindex('.',代码) > 0 and left(代码,charindex('.',代码)-1) = left(t.代码,charindex('.',t.代码) - 1))) m,
(select left(代码,charindex('.',代码) - 1) 代码, sum(数量) 数量 , sum(金额) 金额 from tb where charindex('.',代码) > 0 group by left(代码,charindex('.',代码) - 1) ) n
where m.代码 = n.代码drop table tb/*
代码 名称 型号 数量 金额
---------- ---------- ---------- ----------- -----------
001 b b1 8 300
002 c c1 4 530(所影响的行数为 2 行)*/
-- Author : liangCK 梁爱兰
-- Comment: 小梁 爱 兰儿
-- Date : 2009-11-18 18:16:12
-------------------------------------
--> 生成测试数据: @tb
DECLARE @tb TABLE (代码 varchar(20),名称 varchar(1),型号 varchar(2),数量 int,金额 int)
INSERT INTO @tb
SELECT '001.01','a','a1',5,100 UNION ALL
SELECT '001.02','b','b1',3,200 UNION ALL
SELECT '002.01','c','c1',2,295 UNION ALL
SELECT '002.02','d','d1',2,235--SQL查询如下:SELECT LEFT(A.代码,3) AS 代码,A.名称,A.型号,
SUM(B.数量) AS 数量,SUM(B.金额) AS 金额
FROM (SELECT * FROM @tb AS A
WHERE NOT EXISTS(SELECT * FROM @tb WHERE LEFT(代码,3)=LEFT(A.代码,3)
AND 代码 > A.代码)) AS A
JOIN @tb AS B
ON LEFT(A.代码,3) = LEFT(B.代码,3)
GROUP BY LEFT(A.代码,3),A.名称,A.型号/*
代码 名称 型号 数量 金额
------ ---- ---- ----------- -----------
001 b b1 8 300
002 d d1 4 530(2 行受影响)
*/
select left(代码,3), 名称, 型号, sum(数量), sum(金额)
from 表
group by left(代码,3), 名称, 型号
-- Author : liangCK 梁爱兰
-- Comment: 小梁 爱 兰儿
-- Date : 2009-11-18 18:16:12
-------------------------------------
--> 生成测试数据: @tb
DECLARE @tb TABLE (代码 varchar(20),名称 varchar(1),型号 varchar(2),数量 int,金额 int)
INSERT INTO @tb
SELECT '001.01','a','a1',5,100 UNION ALL
SELECT '001.02','b','b1',3,200 UNION ALL
SELECT '002.01','c','c1',2,295 UNION ALL
SELECT '002.02','d','d1',2,235--SQL查询如下:SELECT LEFT(A.代码,3) AS 代码,A.名称,A.型号,
SUM(B.数量) AS 数量,SUM(B.金额) AS 金额
FROM (SELECT * FROM @tb AS A
WHERE NOT EXISTS(SELECT * FROM @tb WHERE LEFT(代码,3)=LEFT(A.代码,3)
AND 数量*金额 > A.数量*A.金额)) AS A
JOIN @tb AS B
ON LEFT(A.代码,3) = LEFT(B.代码,3)
GROUP BY LEFT(A.代码,3),A.名称,A.型号/*
代码 名称 型号 数量 金额
------ ---- ---- ----------- -----------
001 b b1 8 300
002 c c1 4 530(2 行受影响)
*/
select left(代码,3),
名称=(select top 1 名称 from @tb where left(代码,3)=left(a.代码,3) order by 金额 desc),
型号=(select top 1 型号 from @tb where left(代码,3)=left(a.代码,3) order by 金额 desc),
sum(数量), sum(金额)
from @tb a
group by left(代码,3)
declare @tb table([代码] varchar(20),[名称] varchar(1),[型号] varchar(2),[数量] int,[金额] int)
insert @tb
select '001.01','a','a1',5,100 union all
select '001.02','b','b1',3,200 union all
select '002.01','c','c1',2,295 union all
select '002.02','d','d1',2,235;with cte as
(
select left([代码],3) as [代码], [名称],[型号],[数量],[金额]
,row_number() over (partition by left([代码],3) order by [金额] desc) as id
from @tb
)
select left(b.[代码],3),c.[名称],c.[型号],sum(b.[数量]),sum(b.[金额])
from @tb b inner join cte c on left(b.[代码],3) = c.[代码]
where c.id=1
group by left(b.[代码],3),c.[名称],c.[型号]
/*
名称 型号
------ ---- ---- ----------- -----------
001 b b1 8 300
002 c c1 4 530(2 row(s) affected)
declare @table table([代码] varchar(10),[名称] varchar(1),[型号] varchar(2),[数量] int,[金额] int)
insert @table
select '001.01','a','a1',5,100 union all
select '001.02','b','b1',3,200 union all
select '002.01','c','c1',2,295 union all
select '002.02','d','d1',2,235select substring(代码,1,charindex('.',代码)-1) as 代码,
名称,
型号,
sum(数量) as 数量,
sum(金额) as 金额
from @table t
where not EXISTS (select * from @table
where substring(代码,1,charindex('.',代码)-1) = substring(t.代码,1,charindex('.',t.代码)-1) and 金额 > t.金额)
group by substring(代码,1,charindex('.',代码)-1),名称,型号
--结果
---------------------------
001 b b1 3 200
002 c c1 2 295
--> 测试数据:[TB]
if object_id('[TB]') is not null drop table [TB]
create table [TB]([代码] varchar(10),[名称] varchar(1),[型号] varchar(2),[数量] int,[金额] int)
insert [TB]
select '001.01','a','a1',5,100 union all
select '001.02','b','b1',3,200 union all
select '002.01','c','c1',2,295 union all
select '002.02','d','d1',2,235
select t.代码,名称,型号,t.数量,t.金额
from (select 代码=left(代码,charindex('.',代码)-1),数量=sum(数量),金额=sum(金额)from [TB] group by left(代码,charindex('.',代码)-1))t
join TB A on t.代码=left(A.代码,charindex('.',A.代码)-1)
where not exists(select 1 from TB where t.代码=left(代码,charindex('.',代码)-1)and A.金额<金额)/*
代码 名称 型号 数量 金额
---------- ---- ---- ----------- -----------
001 b b1 8 300
002 c c1 4 530(所影响的行数为 2 行)*/drop table TB
即:
代码 名称 型号 数量 金额
001.01 a a1 5 100
001.02 b b1 3 200
001.02 b b1 3 200
002.01 c c1 2 295
002.02 d d1 2 235
k.代码,k.名称,k.型号,j.数量,j.金额
from
(select 代码=left(代码,3),名称,型号 from @tb where 金额 in(select max(金额)
from @tb a where a.代码=代码 group by left(代码,3))) as k
join
(select 代码=left(代码,3),数量=sum(数量),金额=sum(金额) from @tb group by left(代码,3)) as j
on k.代码=j.代码