SQL游标分组主从表数据

　　前段日子看到我老大用游标写了一个经典的分组，很是强大。所以在工作完成的前提下也写了一个DEMO。整理了下放到网上和大家分享。

游标概念就不说了。大家可以去GOOGLE搜一下

　　其实在我们用列表呈现数据的时候，都需要用到分组这个功能。通过分组更好的展现数据。

if exists(Select Name From sysobjects Where Name='ICStockBill')
    Drop Table ICStockBill
GO
--创建单据主表
    Create Table ICStockBill
    (
        FInterID int primary key,
        FBillNo varchar(50)
    )

if exists(Select Name From sysobjects Where Name='ICStockBillEntry')
    Drop Table ICStockBillEntry
GO
--创建单据表体
    Create Table ICStockBillEntry
    (
        FInterID int,   --关联主键
        FPrice decimal, --价格
        FQty int,  --数量
        FItemID int   --物料ID
    )

--插入主表测试数据
begin
declare @Count int
    set @Count=1

    while(@Count<=20)
    Begin
        Insert Into ICStockBill Values(10000+@Count,'Work00'+Convert(varchar(10),@Count))
        set @Count=@Count+1
    End
end
GO

--查询数据
Select FInterID,FBillNO From ICStockBill

begin
declare @Count int
declare @IDCount int
declare @Mark int

    set @IDCount=1
    set @Count=0
    set @Mark=0

    while(@Count<=60)
    Begin
        if(@Count%3=0)
        begin
            Insert Into ICStockBillEntry Values(10000+@IDCount,100+@Count,1000+@Count,10000+@Count)
            set @IDCount=@IDCount+1
            set @Mark=10000+@IDCount
        end
        if(@Mark>0)
        begin
            if((@Count+1)%3<>0)
            Insert Into ICStockBillEntry Values(@Mark,101+@Count,1001+@Count,10001+@Count)
        end
        set @Count=@Count+1
    End
end

Select * From ICStockBillEntry

--开始一个事务
begin transaction Ts1

if object_id('tempdb.dbo.#mytemp')is not null
Drop Table #mytemp
GO
Create Table #mytemp
(
   TempID int,
   TempBillNo varchar(50),
   TempItemID int,
   TempPrice decimal,
   TempQty decimal
)
    declare @ID int
    declare @BillNo varchar(50)

    declare mycursor cursor for select FInterID,FBillNo from ICStockBill
    open mycursor
    fetch next from mycursor into @id,@billno

   while(@@fetch_status=0)
    begin
             Insert Into #mytemp(TempID,TempBillNO,TempItemID,TempPrice,TempQty) (select top 1 @ID,@BillNo,t2.FItemID,t2.FPrice,t2.FQty From ICStockBill t1
             Left Join ICStockBillEntry t2 On t1.FInterID=t2.FInterID
             Where t1.FInterID=@ID)

             Insert Into #mytemp(TempID,TempBillNO,TempItemID,TempPrice,TempQty) Select t.c1,t.c2,t.FItemID,t.FPrice,t.FQty From (Select '' As c1,'' As c2,FItemID,FPrice,FQty,Row_Number() Over(Order By FInterID)RowID from ICStockBillEntry Where FInterID=@ID)t Where t.RowID<>1

            fetch next From mycursor Into @id,@billno

    end
    Select TempBillNo,TempItemID,TempPrice,TempQty From #mytemp
    close mycursor
    deallocate mycursor
    drop table #mytemp
GO

--回滚事务
rollback transaction ts1

下面是出来的结果。

SQL游标结果

这样就通过游标实现了分组数据

posted @ 2010-06-03 13:17 Hank.Wen 阅读(279) 评论(0) 收藏举报

刷新页面返回顶部

Hank_Wen

SQL游标分组主从表数据

公告