第七色在线视频,2021少妇久久久久久久久久,亚洲欧洲精品成人久久av18,亚洲国产精品特色大片观看完整版,孙宇晨将参加特朗普的晚宴

為了賬號安全，請及時綁定郵箱和手機立即綁定
已解決430363個問題，去搜搜看，總會有你想問的

具有多個列聚合的SQL Server數(shù)據(jù)透視表

具有多個列聚合的SQL Server數(shù)據(jù)透視表

ITMISS 2019-10-18 13:53:03
我有一張桌子： create table mytransactions(country varchar(30), totalcount int, numericmonth int, chardate char(20), totalamount money)該表包含以下記錄：insert into mytransactions(country, totalcount, numericmonth, chardate, totalamount) values('Australia', 36, 7, 'Jul-12', 699.96)Goinsert into mytransactions(country, totalcount, numericmonth, chardate, totalamount) values('Australia', 44, 8, 'Aug-12', 1368.71)Goinsert into mytransactions(country, totalcount, numericmonth, chardate, totalamount) values('Australia', 52, 9, 'Sep-12', 1161.33)Goinsert into mytransactions(country, totalcount, numericmonth, chardate, totalamount) values('Australia', 50, 10, 'Oct-12', 1099.84)Goinsert into mytransactions(country, totalcount, numericmonth, chardate, totalamount) values('Australia', 38, 11, 'Nov-12', 1078.94)Goinsert into mytransactions(country, totalcount, numericmonth, chardate, totalamount) values('Australia', 63, 12, 'Dec-12', 1668.23)Goinsert into mytransactions(country, totalcount, numericmonth, chardate, totalamount) values('Austria', 11, 7, 'Jul-12', 257.82)Go這就是select *的樣子：Country         TotalCount numericmonth  chardate totalamount---------       ---------- -----------   -------- -----------Australia       36         7             Jul-12   699.96Australia       44         8             Aug-12   1368.71Australia       52         9             Sep-12   1161.33Australia       50         10            Oct-12   1099.84Australia       38         11            Nov-12   1078.94Australia       63         12            Dec-12   1668.23Austria         11         7             Jul-12   257.82Austria          5         8             Aug-12   126.55Austria          7         9             Sep-12   92.11Austria         12         10            Oct-12   103.56Austria         21         11            Nov-12   377.68Austria          3         12            Dec-12   14.35
查看完整描述

3 回答

?
慕森卡

TA貢獻1806條經驗 獲得超8個贊

我添加了動態(tài)查詢/解決方案。


靜態(tài)的


SELECT  t.chardate,

        SUM(CASE WHEN t.country='Australia' THEN t.totalcount ELSE 0 END) AS "Australia # of Transactions",

        SUM(CASE WHEN t.country='Australia' THEN t.totalamount ELSE 0 END) AS "Australia Total $ amount",

        SUM(CASE WHEN t.country='Austria' THEN t.totalcount ELSE 0 END) AS "Austria # of Transactions",

        SUM(CASE WHEN t.country='Austria' THEN t.totalamount ELSE 0 END) AS "Austria Total $ amount"

FROM    mytransactions t

GROUP BY t.chardate;

注意：


1）ORDER BY t.chardate將不起作用，因為chardate列中char的值為s。


2）我的建議是分為chardate兩列numericmonth和numericyear。在后一種情況下，您可以使用以下解決方案：


SELECT  t.numericyear, t.numericmonth,

        SUM(CASE WHEN t.country='Australia' THEN t.totalcount ELSE 0 END) AS "Australia # of Transactions",

        SUM(CASE WHEN t.country='Australia' THEN t.totalamount ELSE 0 END) AS "Australia Total $ amount",

        SUM(CASE WHEN t.country='Austria' THEN t.totalcount ELSE 0 END) AS "Austria # of Transactions",

        SUM(CASE WHEN t.country='Austria' THEN t.totalamount ELSE 0 END) AS "Austria Total $ amount"

FROM    mytransactions t

GROUP BY t.numericyear, t.numericmonth

ORDER BY BY t.numericyear, t.numericmonth;

動態(tài)


DECLARE @Sql NVARCHAR(MAX)='SELECT t.chardate';

DECLARE @ColumnTemplate NVARCHAR(MAX)='SUM(CASE WHEN t.country=''{country}'' THEN t.totalcount ELSE 0 END) AS "{country} # of Transactions"

,SUM(CASE WHEN t.country=''{country}'' THEN t.totalamount ELSE 0 END) AS "{country} Total $ amount"'


SELECT @Sql=@Sql+CHAR(13)+','+REPLACE(@ColumnTemplate, '{country}', REPLACE(c.name,'''','''''')e)

FROM (

    SELECT  DISTINCT t.country AS name

    FROM    mytransactions t

) c


SELECT @Sql=@Sql+'

FROM mytransactions t

GROUP BY t.chardate;'


PRINT @Sql;



EXEC(@Sql);

結果：


SELECT t.chardate

,SUM(CASE WHEN t.country='Australia' THEN t.totalcount ELSE 0 END) AS "Australia # of Transactions"

,SUM(CASE WHEN t.country='Australia' THEN t.totalamount ELSE 0 END) AS "Australia Total $ amount"

,SUM(CASE WHEN t.country='Austria' THEN t.totalcount ELSE 0 END) AS "Austria # of Transactions"

,SUM(CASE WHEN t.country='Austria' THEN t.totalamount ELSE 0 END) AS "Austria Total $ amount"

FROM mytransactions t

GROUP BY t.chardate;

注意：REPLACEfrom 的功能SELECT @Sql=@Sql+CHAR(13)+ ... REPLACE(c.name,'''',''''''))用于防止SQL injections。


查看完整回答
反對 回復 2019-10-18
?
幕布斯6054654

TA貢獻1876條經驗 獲得超7個贊

我使用您自己的數(shù)據(jù)透視表作為嵌套查詢，并得出以下結果：


SELECT

  [sub].[chardate],

  SUM(ISNULL([Australia], 0)) AS [Transactions Australia],

  SUM(CASE WHEN [Australia] IS NOT NULL THEN [TotalAmount] ELSE 0 END) AS [Amount Australia],

  SUM(ISNULL([Austria], 0)) AS [Transactions Austria],

  SUM(CASE WHEN [Austria] IS NOT NULL THEN [TotalAmount] ELSE 0 END) AS [Amount Austria]

FROM

(

  select * 

  from  mytransactions

  pivot (sum (totalcount) for country in ([Australia], [Austria])) as pvt

) AS [sub]

GROUP BY

  [sub].[chardate],

  [sub].[numericmonth]

ORDER BY 

  [sub].[numericmonth] ASC


查看完整回答
反對 回復 2019-10-18
  • 3 回答
  • 0 關注
  • 870 瀏覽
慕課專欄
更多

添加回答

舉報

0/150
提交
取消
微信客服

購課補貼
聯(lián)系客服咨詢優(yōu)惠詳情

 幫助反饋 APP下載

慕課網APP
您的移動學習伙伴

 公眾號

掃描二維碼
關注慕課網微信公眾號