我正在处理一个查询，该查询应该返回将由报表使用的数据集。假设我有一个dbo.payments数据表，如下所示：
dbo.payments
----------------------------------------------- Customer |Commission| Trade Date | Trade Type | ----------------------------------------------- AMIRALIS | 20.00 | 22/01/2018 | SALE | BRUSSASH | 30.00 | 22/01/2018 | PURCHASE | AMIRALIS | 10.00 | 22/01/2018 | SALE | AKBMOBIL | 50.00 | 22/01/2018 | PURCHASE | AMIRALIS | 10.00 | 23/01/2018 | PURCHASE | BRUSSASH | 10.00 | 23/01/2018 | PURCHASE | BRUSSASH | 30.00 | 23/01/2018 | SALE | BRUSSASH | 10.00 | 23/01/2018 | PURCHASE | AMIRALIS | 60.00 | 24/01/2018 | PURCHASE | BRUSSASH | 10.00 | 24/01/2018 | SALE |
场景1：我想编写一个查询，该查询将为我提供如下所示的结果。请不是百分比是针对SUM(Commission)所有的时间240.00，无论在where子句中。
SUM(Commission)
240.00
----------------------------------------------------- Customer |Total Commission| Trade Date |%Commission | ----------------------------------------------------- AMIRALIS | 30.00 | 22/01/2018 | 0.125 | BRUSSASH | 30.00 | 22/01/2018 | 0.125 | AKBMOBIL | 50.00 | 22/01/2018 | 0.208 | AMIRALIS | 10.00 | 23/01/2018 | 0.041 | BRUSSASH | 50.00 | 23/01/2018 | 0.208 | AMIRALIS | 60.00 | 24/01/2018 | 0.250 | BRUSSASH | 10.00 | 24/01/2018 | 0.041 |
是SQL新手，我能走的最远的是：。
SELECT [TRADE DATE] AS DATE, CUSTOMER, SUM([COMMISSION]) AS TOTAL, ([COMMISSION] / (SELECT SUM([COMMISSION]) FROM DBO.PAYMENTS)) AS '%COMMISSION' FROM DBO.PAYMENTS GROUP BY [TRADE DATE], CUSTOMER, COMMISSION
无论WHERE子句如何，我如何达到期望的结果：WHERE [Trade Date] BETWEEN '21/01/2018' AND '24/01/2018'即将总数减少为170.00
WHERE [Trade Date] BETWEEN '21/01/2018' AND '24/01/2018'
170.00
这可以使用SQL Server窗口函数来解决，例如：
SELECT DISTINCT t.Customer, SUM(t.Commission) OVER (PARTITION BY Customer, Trade_Date) Total_Commission, t.Trade_Date, SUM(t.Commission) OVER (PARTITION BY Customer, Trade_Date) / SUM(t.Commission) OVER() "%Commission" FROM mytable t ORDER BY 1, 3
dbfiddle ：
Customer | Total_Commission | Trade_Date | %Commission :------- | :--------------- | :------------------ | :---------- AKBMOBIL | 50.00 | 22/01/2018 00:00:00 | 0.208333 AMIRALIS | 30.00 | 22/01/2018 00:00:00 | 0.125000 AMIRALIS | 10.00 | 23/01/2018 00:00:00 | 0.041666 AMIRALIS | 60.00 | 24/01/2018 00:00:00 | 0.250000 BRUSSASH | 30.00 | 22/01/2018 00:00:00 | 0.125000 BRUSSASH | 50.00 | 23/01/2018 00:00:00 | 0.208333 BRUSSASH | 10.00 | 24/01/2018 00:00:00 | 0.041666
PS：如果您需要按日期过滤输出，则只需WHERE在查询中添加一个子句即可。这将过滤两个计算。
WHERE
