none
SQL分组求和 RRS feed

  • 问题

  • 日期(PRT_Date)        姓名(PRT_EmpName)   部门(PRT_Deprt)      金额(PRT_Amt)    数量(PRT_Qty)   
    2008-1-12               刘                  部门1                100              100
    2008-1-12               张                  部门2                100              100
    2008-1-13               刘                  部门1                100              100
    2008-1-19               张                  部门2                300              300
    2008-2-12               刘                  部门1                120              120
    2008-2-19               张                  部门2                300              300
    2008-3-12               刘                  部门1                130              130
    得到的查询结果:
    姓名 部门    2008-01 数量    2008-02  数量    2008-03   数量
    刘   部门1   200     200     120      120    130       130
    张   部门2   400     400     300      300

    请写出查询语句,提示:分组、求和  每个月每个人得到的金额和数量。 
    2013年8月6日 3:38

答案

  • 您好,

    請問是以下的樣子嗎?

    DROP TABLE #t1;
    
    CREATE TABLE #t1
    (
    prt_date DATETIME
    , prt_empname NVARCHAR(30)
    , prt_deprt NVARCHAR(30)
    , prt_amt DECIMAL
    , prt_qty int
    );
     
    INSERT INTO #t1 VALUES('2008-1-12', N'刘', N'部门1', 100, 100);
    INSERT INTO #t1 VALUES('2008-1-12', N'张', N'部门2', 100, 100);
    INSERT INTO #t1 VALUES('2008-1-13', N'刘', N'部门1', 100, 100);
    INSERT INTO #t1 VALUES('2008-1-19', N'张', N'部门2', 300, 300);
    INSERT INTO #t1 VALUES('2008-2-12', N'刘', N'部门1', 120, 120);
    INSERT INTO #t1 VALUES('2008-2-19', N'张', N'部门2', 300, 300);
    INSERT INTO #t1 VALUES('2008-3-12', N'刘', N'部门1', 130, 130);
    
     
    
     
    
     
    SELECT t2.*, pivotQTY.[2008-01] AS [2008-01QTY], pivotQTY.[2008-02] AS [2008-02QTY], pivotQTY.[2008-03] AS [2008-03QTY]
    FROM
    (SELECT LEFT(CONVERT(VARCHAR, prt_date, 20), 7) AS prt_ym, prt_deprt , prt_empname,  prt_qty 
    FROM #t1) AS SourceTable
    PIVOT
    (
    sum(prt_qty)
    FOR   prt_ym IN ([2008-01], [2008-02], [2008-03])
    ) AS pivotQTY
    CROSS APPLY (
    	SELECT *
    	FROM
    	(SELECT LEFT(CONVERT(VARCHAR, prt_date, 20), 7) AS prt_ym, prt_deprt , prt_empname,  prt_amt 
    	FROM #t1) AS SourceTable
    	PIVOT
    	(
    	sum(prt_amt)
    	FOR   prt_ym IN ([2008-01], [2008-02], [2008-03])
    	) AS pivotAMT 
    	WHERE  pivotQTY.prt_deprt = pivotAMT.prt_deprt AND pivotQTY.prt_empname = pivotAMT.prt_empname  ) t2
    

                            

    另外,如果您不想要寫死COLUMN NAME的話,可以用將欄位名稱用字串的方式串起來,如下,

    --use dynamic
    DECLARE @qty_columns NVARCHAR (MAX), @amt_columns NVARCHAR(MAX)
    SELECT @qty_columns = COALESCE (@qty_columns + ',[' +  N'数量-' +  RTRIM(T.prt_ym) + ']', '[' + N'数量-' +  RTRIM(T.prt_ym) + ']')
    , @amt_columns = COALESCE (@amt_columns + ',[' +  N'金额-' +  RTRIM(T.prt_ym) + ']', '[' + N'金额-' +  RTRIM(T.prt_ym) + ']')
    FROM  (SELECT DISTINCT LEFT(CONVERT(VARCHAR, prt_date, 20), 7) AS prt_ym FROM #t1) T
    ORDER BY T.prt_ym
    PRINT @qty_columns
    PRINT @amt_columns
    
    EXECUTE('SELECT T2.*, ' + @qty_columns + '
    FROM 
    (SELECT N''数量-'' +  LEFT(CONVERT(VARCHAR, prt_date, 20), 7) AS prt_ym, prt_deprt , prt_empname,  prt_qty 
    	FROM #t1
    ) AS X
    PIVOT
    (
        SUM(prt_qty)
        FOR prt_ym IN 
        (' +  @qty_columns + ')
    ) AS PVT  
    CROSS APPLY (
    	 SELECT *
    	FROM 
    	(SELECT N''金额-'' +  LEFT(CONVERT(VARCHAR, prt_date, 20), 7) AS prt_ym, prt_deprt , prt_empname,  prt_amt 
    		FROM #t1
    	) AS X
    	PIVOT
    	(
    		SUM(prt_amt)
    		FOR prt_ym IN 
    		(' +  @amt_columns + ')
    	) AS AMT_PVT
    	WHERE PVT.prt_deprt = AMT_PVT.prt_deprt AND PVT.prt_empname = AMT_PVT.prt_empname ) T2');

    請您再參考看看吧!


    亂馬客blog: http://www.dotblogs.com.tw/rainmaker/


    2013年8月7日 6:11

全部回复

  • 您好,

    欢迎来到MSDN中文论坛!

    您的问题已经移动到 SQL Server 版块,在这里,您会获得更好的解答:)


    Franklin Chen
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    2013年8月7日 4:36
  • 您好,

    請問是以下的樣子嗎?

    DROP TABLE #t1;
    
    CREATE TABLE #t1
    (
    prt_date DATETIME
    , prt_empname NVARCHAR(30)
    , prt_deprt NVARCHAR(30)
    , prt_amt DECIMAL
    , prt_qty int
    );
     
    INSERT INTO #t1 VALUES('2008-1-12', N'刘', N'部门1', 100, 100);
    INSERT INTO #t1 VALUES('2008-1-12', N'张', N'部门2', 100, 100);
    INSERT INTO #t1 VALUES('2008-1-13', N'刘', N'部门1', 100, 100);
    INSERT INTO #t1 VALUES('2008-1-19', N'张', N'部门2', 300, 300);
    INSERT INTO #t1 VALUES('2008-2-12', N'刘', N'部门1', 120, 120);
    INSERT INTO #t1 VALUES('2008-2-19', N'张', N'部门2', 300, 300);
    INSERT INTO #t1 VALUES('2008-3-12', N'刘', N'部门1', 130, 130);
    
     
    
     
    
     
    SELECT t2.*, pivotQTY.[2008-01] AS [2008-01QTY], pivotQTY.[2008-02] AS [2008-02QTY], pivotQTY.[2008-03] AS [2008-03QTY]
    FROM
    (SELECT LEFT(CONVERT(VARCHAR, prt_date, 20), 7) AS prt_ym, prt_deprt , prt_empname,  prt_qty 
    FROM #t1) AS SourceTable
    PIVOT
    (
    sum(prt_qty)
    FOR   prt_ym IN ([2008-01], [2008-02], [2008-03])
    ) AS pivotQTY
    CROSS APPLY (
    	SELECT *
    	FROM
    	(SELECT LEFT(CONVERT(VARCHAR, prt_date, 20), 7) AS prt_ym, prt_deprt , prt_empname,  prt_amt 
    	FROM #t1) AS SourceTable
    	PIVOT
    	(
    	sum(prt_amt)
    	FOR   prt_ym IN ([2008-01], [2008-02], [2008-03])
    	) AS pivotAMT 
    	WHERE  pivotQTY.prt_deprt = pivotAMT.prt_deprt AND pivotQTY.prt_empname = pivotAMT.prt_empname  ) t2
    

                            

    另外,如果您不想要寫死COLUMN NAME的話,可以用將欄位名稱用字串的方式串起來,如下,

    --use dynamic
    DECLARE @qty_columns NVARCHAR (MAX), @amt_columns NVARCHAR(MAX)
    SELECT @qty_columns = COALESCE (@qty_columns + ',[' +  N'数量-' +  RTRIM(T.prt_ym) + ']', '[' + N'数量-' +  RTRIM(T.prt_ym) + ']')
    , @amt_columns = COALESCE (@amt_columns + ',[' +  N'金额-' +  RTRIM(T.prt_ym) + ']', '[' + N'金额-' +  RTRIM(T.prt_ym) + ']')
    FROM  (SELECT DISTINCT LEFT(CONVERT(VARCHAR, prt_date, 20), 7) AS prt_ym FROM #t1) T
    ORDER BY T.prt_ym
    PRINT @qty_columns
    PRINT @amt_columns
    
    EXECUTE('SELECT T2.*, ' + @qty_columns + '
    FROM 
    (SELECT N''数量-'' +  LEFT(CONVERT(VARCHAR, prt_date, 20), 7) AS prt_ym, prt_deprt , prt_empname,  prt_qty 
    	FROM #t1
    ) AS X
    PIVOT
    (
        SUM(prt_qty)
        FOR prt_ym IN 
        (' +  @qty_columns + ')
    ) AS PVT  
    CROSS APPLY (
    	 SELECT *
    	FROM 
    	(SELECT N''金额-'' +  LEFT(CONVERT(VARCHAR, prt_date, 20), 7) AS prt_ym, prt_deprt , prt_empname,  prt_amt 
    		FROM #t1
    	) AS X
    	PIVOT
    	(
    		SUM(prt_amt)
    		FOR prt_ym IN 
    		(' +  @amt_columns + ')
    	) AS AMT_PVT
    	WHERE PVT.prt_deprt = AMT_PVT.prt_deprt AND PVT.prt_empname = AMT_PVT.prt_empname ) T2');

    請您再參考看看吧!


    亂馬客blog: http://www.dotblogs.com.tw/rainmaker/


    2013年8月7日 6:11
  • 好厉害啊


    Please Mark As Answer if it is helpful.

    2013年8月7日 6:27
  • 纵表转横表(行转列):静态语句:

    select prt_empname,
           prt_dept,
           sum (case to_char(prt_date,'YYYY-MM') when '2008-01' then prt_amt else 0 end) "2008-01",
           sum (case to_char(prt_date,'YYYY-MM') when '2008-01' then prt_qty else 0 end) "数量",
           sum (case to_char(prt_date,'YYYY-MM') when '2008-02' then prt_amt else 0 end) "2008-02",
           sum (case to_char(prt_date,'YYYY-MM') when '2008-02' then prt_qty else 0 end) "数量",
           sum (case to_char(prt_date,'YYYY-MM') when '2008-03' then prt_amt else 0 end) "2008-03",
           sum (case to_char(prt_date,'YYYY-MM') when '2008-03' then prt_qty else 0 end) "数量"
               from a
               group by prt_empname,prt_dept;

    2013年8月8日 3:39