不要用循环实现行汇总

Question

根据 物品、物品料号、单价 进行汇总，金额大于1000000就在另一行。
同时记录该笔汇总的明细ID号。金额=单价*数量ID 物品 物品料号          单价 数量
1 泵总成 9684544580 99 100000
2 泵总成 9684544580 98 100000
3 泵总成1 9684544580 97 100000
4 泵总成1 9684544580 100 100000
5 泵总成2 9684544580 50 100000
6 泵总成2 9684544580 49 100000
7 泵总成2 9684544580 3 100000


结果
物品 物品料号          金额 ID2
泵总成 9684544580 990000 1
泵总成 9684544580 980000 2
泵总成1 9684544580 970000 3
泵总成1 9684544580 1000000 4
泵总成2 9684544580 990000 5、6
泵总成2 9684544580 30000 7
大概的情况就是这样，之前用循环数据量很大就很慢。麻烦高手指点了！谢谢

Answer 1

declare @t table(id int,物品 nvarchar(100),物品料号 nvarchar(100),单价 int ,数量 int);
insert into @t 
select 1,'泵总成', '9684544580' ,99 ,100000 union all 
select 2,'泵总成', '9684544580' ,98 ,100000 union all
select 3,'泵总成1','9684544580' ,97 ,100000 union all
select 4,'泵总成1','9684544580' ,100,100000 union all
select 5,'泵总成2','9684544580' ,50 ,100000 union all
select 6,'泵总成2','9684544580' ,49 ,100000 union all
select 7,'泵总成2','9684544580' ,3 ,100000 
select * from @t;-- 等高手。

Answer 2

if object_id('[TB]') is not null drop table [TB]
go
create table [TB] (ID int,物品 nvarchar(8),物品料号 bigint,单价 int,数量 int)
insert into [TB]
select 1,'泵总成',9684544580,99,100000 union all
select 2,'泵总成',9684544580,98,100000 union all
select 3,'泵总成1',9684544580,97,100000 union all
select 4,'泵总成1',9684544580,100,100000 union all
select 5,'泵总成2',9684544580,50,100000 union all
select 6,'泵总成2',9684544580,49,100000 union all
select 7,'泵总成2',9684544580,3,100000select * from [TB]
SELECT  物品 ,
        物品料号 ,
        SUM(单价 * 数量) AS 总金额 ,
        [ID] = CASE WHEN SUM(单价 * 数量) > 1000000
                    THEN STUFF(( SELECT ',' + CONVERT(VARCHAR, id)
                                 FROM   tb t
                                 WHERE  物品 = tb.物品
                                        AND 物品料号 = tb.物品料号
                               FOR
                                 XML PATH('')
                               ), 1, 1, '')
               END
FROM    tb
GROUP BY 物品 ,
        物品料号
        
        
/*
物品 物品料号 总金额 ID
泵总成 9684544580 19700000 1,2
泵总成1 9684544580 19700000 3,4
泵总成2 9684544580 10200000 5,6,7*/

Answer 3

看明白了，金额大于1000000的不汇总
select 物品, 物品料号 ,单价* 数量 as 金额 ,id as ID2
from @t where 单价* 数量>1000000
union all
--行转列一下id2
select 物品, 物品料号 ,单价* 数量 as 金额 ,dbo.f_行转列(..) as ID2
from @t where 单价* 数量<=1000000
...

Answer 4

对了！单价不一样的还不能合并。第5条+第6条=990000
如果再加上第7条的话就>1000000l 

Answer 5

use dbx;
godeclare @t table(id int,物品 nvarchar(100),物品料号 nvarchar(100),单价 int ,数量 int);
insert into @t 
select 1,'泵总成', '9684544580' ,99 ,100000 union all 
select 2,'泵总成', '9684544580' ,98 ,100000 union all
select 3,'泵总成1','9684544580' ,97 ,100000 union all
select 4,'泵总成1','9684544580' ,100,100000 union all
select 5,'泵总成2','9684544580' ,50 ,100000 union all
select 6,'泵总成2','9684544580' ,49 ,100000 union all
select 7,'泵总成2','9684544580' ,3 ,100000 union all
select 8,'泵总成2','9684544580' ,3  ,100000 union all
select 9,'泵总成2','9684544580' ,3  ,100000 union all
select 10,'泵总成2','9684544580' ,3  ,100000 union all
select 11,'泵总成2','9684544580' ,99  ,100000 ;
--select * from @t;
--
-- 这里假设id都是按物品排序id，如果没有则先排序。
create table #t(id int,物品 nvarchar(100),物品料号 nvarchar(100),单价 int ,数量 int);
declare @i int ,@c int;
declare @p nvarchar(100);
declare @price int;select @i=0,@c=COUNT(1) from @t;
while @i<=@c
begin
select @p=物品,@price=单价 from @t where id=@i;
if exists(select 1 from #t where 物品=@p)
if (select top 1 单价+@price from #t order by id desc )<=100
update #t set 单价=单价+@price where 物品=@p and id=(select MAX(id) from #t);
else 
insert into #t select * from @t where id=@i;
else
insert into #t select * from @t where id=@i;

set @i=@i+1;
end
select * from #t;
--drop table #t;
/*
id      物品           物品料号                      单价          数量
------- ------------ ------------------------- ----------- -----------
1       泵总成          9684544580                99          100000
2       泵总成          9684544580                98          100000
3       泵总成1         9684544580                97          100000
4       泵总成1         9684544580                100         100000
5       泵总成2         9684544580                99          100000
7       泵总成2         9684544580                12          100000
11      泵总成2         9684544580                99          100000
*/--这样效率也不高。

Answer 6

declare @t table(id int,物品 nvarchar(100),物品料号 nvarchar(100),单价 int ,数量 int);
insert into @t 
select 1,'泵总成', '9684544580' ,99 ,100000 union all 
select 2,'泵总成', '9684544580' ,98 ,100000 union all
select 3,'泵总成1','9684544580' ,97 ,100000 union all
select 4,'泵总成1','9684544580' ,100,100000 union all
select 5,'泵总成2','9684544580' ,50 ,100000 union all
select 6,'泵总成2','9684544580' ,49 ,100000 union all
select 7,'泵总成2','9684544580' ,3 ,100000 union all
select 8,'泵总成2','9684544580' ,3  ,100000 union all
select 9,'泵总成2','9684544580' ,3  ,100000 union all
select 10,'泵总成2','9684544580' ,3  ,100000 union all
select 11,'泵总成2','9684544580' ,99  ,100000 ;
--select * from @t;
--
-- 这里假设id都是按物品排序id，如果没有则先排序。
create table #t(id int,物品 nvarchar(100),物品料号 nvarchar(100),单价 int ,数量 int);
declare @i int ,@c int;
declare @p nvarchar(100);
declare @price int;select @i=0,@c=COUNT(1) from @t;
while @i<=@c
begin
select @p=物品,@price=单价 from @t where id=@i;
if exists(select 1 from #t where 物品=@p) and (select top 1 单价+@price from #t order by id desc )<=100
update #t set 单价=单价+@price where 物品=@p and id=(select MAX(id) from #t);
else
insert into #t select * from @t where id=@i;

set @i=@i+1;
end
select * from #t;

Answer 7

没有找到好办法。单层循环的我自己能做！跟jinfengyiye的差不多的！所有不能给分了。抱歉

Answer 8

use Tempdb
go
--> --> 
 
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([ID] int,[物品] nvarchar(50),[物品料号] nvarchar(50),[单价] int,[数量] int)
Insert #T
select 1,N'泵总成',9684544580,99,100000 union all
select 2,N'泵总成',9684544580,98,100000 union all
select 3,N'泵总成1',9684544580,97,100000 union all
select 4,N'泵总成1',9684544580,100,100000 union all
select 5,N'泵总成2',9684544580,50,100000 union all
select 6,N'泵总成2',9684544580,49,100000 union all
select 7,N'泵总成2',9684544580,3,100000
Go;WITH Cte
AS
(
Select 
*,
Total=a.[单价]*a.数量,
IDs=CAST(a.ID AS VARCHAR(100)),
lev=1 
from #T AS a
WHERE NOT EXISTS(SELECT 1 FROM #T WHERE [物品]=a.[物品] AND [物品料号]=a.[物品料号] AND ID<a.ID)
UNION ALL
SELECT 
a.*,
Total=CASE WHEN b.Total+a.[单价]*a.数量>10000000 THEN a.[单价]*a.数量 ELSE b.Total+a.[单价]*a.数量 end,
IDs=CASE WHEN b.Total+a.[单价]*a.数量>10000000 THEN CAST(a.ID AS VARCHAR(100)) ELSE CAST(b.IDs+'、'+RTRIM(a.ID) AS VARCHAR(100)) END,
lev=CASE WHEN b.Total+a.[单价]*a.数量>10000000 THEN b.lev+1 ELSE b.lev END
FROM #T AS a 
INNER JOIN Cte AS b ON a.ID>b.ID  AND b.[物品]=a.[物品] AND b.[物品料号]=a.[物品料号] 
WHERE NOT EXISTS(SELECT 1 FROM #T WHERE [物品]=a.[物品] AND [物品料号]=a.[物品料号] AND ID<a.ID AND ID>b.ID)
)
SELECT 
[物品],[物品料号],[单价],[数量],IDs AS ID2
FROM Cte AS a
WHERE  NOT EXISTS(SELECT 1 FROM Cte WHERE [物品]=a.[物品] AND [物品料号]=a.[物品料号] AND lev=a.lev AND ID>a.ID)
ORDER BY ID
/*
物品 物品料号 单价 数量 ID2
泵总成 9684544580 99 100000 1
泵总成 9684544580 98 100000 2
泵总成1 9684544580 97 100000 3
泵总成1 9684544580 100 100000 4
泵总成2 9684544580 49 100000 5、6
泵总成2 9684544580 3 100000 7
*/