none
sql2000中如何实现如下的分组? RRS feed

  • 问题

  • 表     日期                   数量          编号
    2009-10-1 11004 09-1665
    2009-10-15 11004 09-1671
    2009-10-31 11004 09-1672
    2009-10-31 11004 09-1673
    2009-11-15 11004 09-1678
    2009-11-30 11004 09-1679
            2009-11-30 11004 09-1680
    我想达到的效果是:将相同日期的数量相加并且将相同日期的编号连在一起,不用存储过程
    如上表要到达的效果如下:
    表     日期                   数量          编号
    2009-10-1 11004 09-1665
    2009-10-15 11004 09-1671
    2009-10-31 22008 09-1672;09-1673
    2009-11-15 11004 09-1678
    2009-11-30 22008 09-1679;09-1680
    如果只对数量统计直接分组就行啦,主要现在多了一个编号要连在一起,不知道怎么搞啦,请高手赐教,谢谢!!
    2009年10月15日 15:15

答案

  • 你如果一定要一个语句完成,又不附加程序集的话,可以考虑用自定义函数结合一个SQL查询的方式。

    --创建测试表
    create table A(date datetime, quantity int, num varchar(10))
    --插入测试数据
    insert into A
    select '2009-10-1', 11004 ,'09-1665'
    union all select '2009-10-15', 11004 ,'09-1671'
    union all select '2009-10-31', 11004 ,'09-1672'
    union all select '2009-10-31', 11004 ,'09-1673'
    union all select '2009-11-15', 11004 ,'09-1678'
    union all select '2009-11-30', 11004 ,'09-1679'
    union all select '2009-11-30', 11004 ,'09-1680'
    
    --写一个拼接A表编号的聚合函数:
    create   function  dbo.fn_MyMerge(@date datetime)
    	returns   varchar(8000)
    as
    begin
          declare  @r   varchar(8000)
          set   @r= ''
          select  @r=@r+ ';'+num  from   A  where   date=@date
          return   stuff(@r,1,1, '') 
    end
    go
    
    --执行单个查询
    select date, sum(quantity) as 数量汇总, dbo.fn_MyMerge(date) as 编号汇总 
    from A
    group by date
    order by date
    
    --执行结果
    /*
    	date                    数量汇总        编号汇总
    	----------------------- ----------- ------------------
    	2009-10-01 00:00:00.000 11004       09-1665
    	2009-10-15 00:00:00.000 11004       09-1671
    	2009-10-31 00:00:00.000 22008       09-1672;09-1673
    	2009-11-15 00:00:00.000 11004       09-1678
    	2009-11-30 00:00:00.000 22008       09-1679;09-1680
    
    	(5 行受影响)
    */
    
    • 已标记为答案 Weber Li 2009年10月16日 5:15
    2009年10月16日 2:01
    版主

全部回复

  • 如果不用存储过程或者函数而直接用一个查询来实现比较的难,用存储过程或者函数可以很容易的实现

    如果知道有哪些日期则比较的好办
    参考这篇文章 http://www.cnblogs.com/jiania1224/archive/2009/04/01/1426805.html


    因为是2000的数据库所以无法使用SQL Server2005中通过附加程序集,添加聚合来实现


    Wenn ich dich hab’,gibt es nichts, was unerträglich ist.坚持不懈!http://hi.baidu.com/1987raymond
    2009年10月15日 17:22
  • 你如果一定要一个语句完成,又不附加程序集的话,可以考虑用自定义函数结合一个SQL查询的方式。

    --创建测试表
    create table A(date datetime, quantity int, num varchar(10))
    --插入测试数据
    insert into A
    select '2009-10-1', 11004 ,'09-1665'
    union all select '2009-10-15', 11004 ,'09-1671'
    union all select '2009-10-31', 11004 ,'09-1672'
    union all select '2009-10-31', 11004 ,'09-1673'
    union all select '2009-11-15', 11004 ,'09-1678'
    union all select '2009-11-30', 11004 ,'09-1679'
    union all select '2009-11-30', 11004 ,'09-1680'
    
    --写一个拼接A表编号的聚合函数:
    create   function  dbo.fn_MyMerge(@date datetime)
    	returns   varchar(8000)
    as
    begin
          declare  @r   varchar(8000)
          set   @r= ''
          select  @r=@r+ ';'+num  from   A  where   date=@date
          return   stuff(@r,1,1, '') 
    end
    go
    
    --执行单个查询
    select date, sum(quantity) as 数量汇总, dbo.fn_MyMerge(date) as 编号汇总 
    from A
    group by date
    order by date
    
    --执行结果
    /*
    	date                    数量汇总        编号汇总
    	----------------------- ----------- ------------------
    	2009-10-01 00:00:00.000 11004       09-1665
    	2009-10-15 00:00:00.000 11004       09-1671
    	2009-10-31 00:00:00.000 22008       09-1672;09-1673
    	2009-11-15 00:00:00.000 11004       09-1678
    	2009-11-30 00:00:00.000 22008       09-1679;09-1680
    
    	(5 行受影响)
    */
    
    • 已标记为答案 Weber Li 2009年10月16日 5:15
    2009年10月16日 2:01
    版主
  • SQL2000可用函數和臨時表實現
    use Tempdb
    go
    --> -->
     
    if not object_id(N'Tempdb..#') is null
        drop table #
    Go
    Create table #([日期] Datetime,[数量] int,[编号] nvarchar(7))
    Insert #
    select '2009-10-1',11004,N'09-1665' union all
    select '2009-10-15',11004,N'09-1671' union all
    select '2009-10-31',11004,N'09-1672' union all
    select '2009-10-31',11004,N'09-1673' union all
    select '2009-11-15',11004,N'09-1678' union all
    select '2009-11-30',11004,N'09-1679' union all
    select '2009-11-30',11004,N'09-1680'
    Go
    Select [日期],[数量],[编号]=cast([编号] as nvarchar(1000)),ID=identity(int,1,1) into #2 from # order by [日期]
    
    
    
    while @@rowcount>0
    begin
        update a
        set [数量]=a.[数量]+b.[数量],[编号]=a.[编号]+';'+b.[编号]
        from #2 a
            inner join #2 b on a.[日期]=b.[日期]
        where not exists(select 1 from #2 where [日期]=a.[日期] and ID<a.ID)
        and b.ID=(select top 1 ID from #2 where [日期]=a.[日期] and ID>a.ID order by ID)
    
        delete b
        from #2 a
            inner join #2 b on a.[日期]=b.[日期]
        where not exists(select 1 from #2 where [日期]=a.[日期] and ID<a.ID)
        and b.ID=(select top 1 ID from #2 where [日期]=a.[日期] and ID>a.ID order by ID)
    end
    
    select [日期]=convert(varchar(10),[日期],120),[数量],[编号] from #2 order by [日期]
    drop table #2
    
    日期         数量          编号
    ---------- ----------- -----------------
    2009-10-01 11004       09-1665
    2009-10-15 11004       09-1671
    2009-10-31 22008       09-1672;09-1673
    2009-11-15 11004       09-1678
    2009-11-30 22008       09-1679;09-1680
    
    (5 個資料列受到影響)
    
    

    SQL2005用CTE和XML實現

    ROY WU(吳熹 )
    2009年10月16日 3:45
    版主