根据已查找出的临时表，按某字段分组插入到另一张表

Question

本帖最后由 u011298504 于 2014-09-27 08:20:32 编辑

Answer 1

你试试看看行不行
with t as(
select 1 SupplyID,'D_1' SupplyDeptID union all 
select 2 SupplyID,'D_2' SupplyDeptID union all 
select 3 SupplyID,'D_2' SupplyDeptID union all 
select 4 SupplyID,'D_2' SupplyDeptID union all 
select 5 SupplyID,'D_2' SupplyDeptID union all 
select 6 SupplyID,'D_2' SupplyDeptID union all 
select 7 SupplyID,'D_2' SupplyDeptID union all 
select 8 SupplyID,'D_2' SupplyDeptID union all 
select 9 SupplyID,'D_2' SupplyDeptID union all 
select 10 SupplyID,'D_2' SupplyDeptID union all 
select 11 SupplyID,'D_2' SupplyDeptID union all 
select 12 SupplyID,'D_2' SupplyDeptID union all 
select 13 SupplyID,'D_2' SupplyDeptID union all 
select 14 SupplyID,'D_2' SupplyDeptID union all 
select 15 SupplyID,'D_3' SupplyDeptID union all 
select 16 SupplyID,'D_3' SupplyDeptID  ) ,
t1(groupid,SupplyID,SupplyDeptID) as(
select  ROW_NUMBER()over(partition by supplydeptid order by supplydeptid)/(10+1),*from t ),
t2 (SupplyDeptID,groupid,minsupplyid,maxsupplyid) as(
select SupplyDeptID,groupid ,MIN(SupplyID),MAX(SupplyID) from t1 group by SupplyDeptID,groupid )
,t3 as(
select ROW_NUMBER()over(order by supplydeptid)gp,* from t2
)
select right(('000'+cast(gp as varchar)),3)flowid,
t1.SupplyID,t1.SupplyDeptID from t1 left join t3 on  t1.supplydeptid=t3.supplydeptid 
 and t1.supplyid between t3.minsupplyid and t3.maxsupplyid
 

Answer 2

我用的是SQL2000，用不了ROW_NUMBER()函数~哇……

Answer 3

SQL Server 2000 -- 先把数据灌到一个临时表中，利用 IDENTITY 函数，生成一个自增列， 再加工这个自增列。
-- 下面是个例子。select name , object_id , IDENTITY(int,1,1) id into aa from sysobjects where xtype ='u'
go
select * from aa 
go

Answer 4

SELECT RIGHT('000'+CONVERT(varchar(3),
                           (SELECT ISNULL(SUM(C),0)+1
                             FROM (
                                   -- 比当前 SupplyDeptID 小的分组数
                                        SELECT SupplyDeptID,
                                               (COUNT(*)+9)/10  C
                                          FROM T t0
                                         WHERE t0.SupplyDeptID < T.SupplyDeptID
                                      GROUP BY SupplyDeptID
                                   UNION ALL   
                                   -- SupplyDeptID 相同，比当前 SupplyID 小的分组数
                                        SELECT SupplyDeptID,
                                               COUNT(*)/10  C
                                          FROM T t1
                                         WHERE t1.SupplyDeptID = T.SupplyDeptID
                                           AND t1.SupplyID < T.SupplyID
                                      GROUP BY SupplyDeptID
                                  ) TX
                           )),
             3) FlowID,
       T.SupplyID,
       T.SupplyDeptID
  FROM /* 代入你原先的查询（要求 SupplyID 为 int 型） */ T
FlowID    SupplyID SupplyDeptID
------ ----------- ------------
001              1 D_1
002              2 D_2
002              3 D_2
002              4 D_2
002              5 D_2
002              6 D_2
002              7 D_2
002              8 D_2
002              9 D_2
002             10 D_2
002             11 D_2
003             12 D_2
003             13 D_2
003             14 D_2
004             15 D_3
004             16 D_3