数据表
方式 日期 商品 仓库 数量 单价 金额
出库 2007-9-29 钢笔 东库 -30 1.8 -54
入库 2007-4-4 钢笔 东库 50 1.8 90
入库 2007-4-4 书包 西库 50 61.8368 3091.84
出库 2007-5-5 书包 西库 -50 61.8368 -3091.84


求如何得到下面查询数据表的SQL写法 方式 日期 商品 仓库 数量 单价 金额
出库 2007-9-29 钢笔 东库 -30 1.8 -54
入库 2007-4-4 钢笔 东库 50 1.8 90
  小计 20 36
入库 2007-4-4 书包 西库 50 61.8368 3091.84
出库 2007-5-5 书包 西库 -50 61.8368 -3091.84
  小计 0 0
合计 20 36

解决方案 »

  1.   

    SELECT 方式,日期,商品,仓库,SUM(数量),AVG(单价),SUM(金额)FROM Table
    GROUP BY 方式,日期,商品,仓库 WITH CUBE
      

  2.   

    如果比较注重格式可以用UNION ALL来实现
      

  3.   

    楼上的答案不是我想要的,我只想按名称、库房分组,hsmserver 分组用了 "GROUP BY   方式,日期,商品,仓库",我不想按日期和方式分组,但又要在查询里显示日期和方式,在小计和合计行只显示数量和金额的合,想要的结果如下格式,请仔细看好。
    方式    日期        商品     仓库    数量    单价        金额 
    ——————————————————————————————————
    出库   2007-9-29  钢笔     东库    -30    1.8         -54 
    ——————————————————————————————————
    入库   2007-4-4   钢笔     东库     50    1.8          90 
    ——————————————————————————————————
     小计                                 20                36 
    ——————————————————————————————————
    入库   2007-4-4   书包     西库     50    61.8368     3091.84 
    ——————————————————————————————————
    出库   2007-5-5   书包     西库    -50    61.8368     -3091.84 
    ——————————————————————————————————
     小计                                 0                  0 
    ——————————————————————————————————
    合计                                  20                 36 
    ——————————————————————————————————
      

  4.   

    select * from (
    select a.方式,a.日期,a.商品,a.仓库,sum(数量)
     from  table  a group by  a.方式,a.日期,a.商品,a.仓库  
    UNION ALL
          select '小计','',商品,仓库  ,sum(数量)
          from table
          group by 商品,仓库
          )  类似这种方法应该可以满足LZ要求,我也是遇到类似问题,才看到此贴,在别的地方找到这方法,已经实现。
    只是如果要让小计显示在各子项下面要用到排序字段。
      

  5.   


    CREATE TABLE #(方式 VARCHAR(10), 日期  DATETIME,商品 VARCHAR(10), 仓库 VARCHAR(10),
     数量 NUMERIC(12,2),单价 NUMERIC(12,2), 金额 NUMERIC(12,2))INSERT INTO # 
    SELECT '出库', '2007-9-29', '钢笔', '东库', -30, 1.8, -54  UNION ALL
    SELECT '入库', '2007-4-4', '钢笔', '东库', 50, 1.8, 90  UNION ALL
    SELECT '入库', '2007-4-4', '书包', '西库', 50, 61.8368, 3091.84  UNION ALL
    SELECT '出库', '2007-5-5', '书包', '西库', -50, 61.8368, -3091.84 
    SELECT * FROM #
    ORDER BY 商品,仓库
    COMPUTE SUM(数量),SUM(金额) BY 商品,仓库
    COMPUTE SUM(数量),SUM(金额)/*
    方式         日期                                                     商品         仓库         数量             单价             金额             
    ---------- ------------------------------------------------------ ---------- ---------- -------------- -------------- -------------- 
    出库         2007-09-29 00:00:00.000                                钢笔         东库         -30.00         1.80           -54.00
    入库         2007-04-04 00:00:00.000                                钢笔         东库         50.00          1.80           90.00                                                                                        sum
                                                                                            ========================================
                                                                                            20.00                                                                                                                      sum
                                                                                                                          ========================================
                                                                                                                          36.00
    方式         日期                                                     商品         仓库         数量             单价             金额             
    ---------- ------------------------------------------------------ ---------- ---------- -------------- -------------- -------------- 
    入库         2007-04-04 00:00:00.000                                书包         西库         50.00          61.84          3091.84
    出库         2007-05-05 00:00:00.000                                书包         西库         -50.00         61.84          -3091.84                                                                                        sum
                                                                                            ========================================
                                                                                            .00                                                                                                                      sum
                                                                                                                          ========================================
                                                                                                                          .00
                                                                                            sum
                                                                                            ========================================
                                                                                            20.00                                                                                                                      sum
                                                                                                                          ========================================
                                                                                                                          36.00
    (7 row(s) affected)*/