基于事务计数的 MySql 数据透视表

Question

我被要求创建一个报告，显示各个成员(member)的交易数据并显示他们进行相同类型交易的频率。

例如:

Bob from organization X has purchased 5 bananas at once on 10 different occasions/transactions and Bob from that same organization has purchased 3 bananas at once on 20 different occasions/transactions.

数据库包含这种形式的交易数据

|TransactionID | Organization | MemberNumber | ItemID| QuantityPurchased 

我当前有一个查询，它按以下格式创建临时表。

|Organization | MemberNumber | ItemID | QuantityItemsPurchased | QuantityOfTransactions
----------------------------------------------------------------------------------------
|Company X    | 2044         | B2     | 5                      | 10
|Company X    | 2044         | B2     | 3                      | 20
|Company Y    | 2035         | A3     | 5                      | 5

我被要求创建以下格式的报告(报告的格式不由我来讨论)，其中每个数字列都是销售商品数量的交易，每行的值是销售该数量商品的交易数量。

|Organization | MemberNumber | ItemID |1  |2  |3  |4  |5  |6+ |
-----------------------------------------------------------------
|Company X    | 2044         | B2     |0  |0  |20 |0  |10 |0  |
|Company Y    | 2035         | A3     |0  |0  |0  |0  |5  |0  |

我不确定如何编写一个查询，将 quantityOfItemsPurchased 排序到各个列中并显示 QuantityOfTransactions 值。

我尝试编写类似于下面的查询，但它对我不起作用，因为它只是在所有数字列下给出 1 或 0 的值，而不是实际的 QuantityOfTransactions 值。

Select Organization, MemberNumber, ItemId,
count(Case when tempTable.quantityOfItemsPurchased = 1 then tempTable.QuantityOfTransactions  end) as '1',
count(Case when tempTable.quantityOfItemsPurchased = 2 then tempTable.QuantityOfTransactions  end) as '2',
count(Case when tempTable.quantityOfItemsPurchased = 3 then tempTable.QuantityOfTransactions  end) as '3',
count(Case when tempTable.quantityOfItemsPurchased = 4 then tempTable.QuantityOfTransactions  end) as '4',
count(Case when tempTable.quantityOfItemsPurchased = 5 then tempTable.QuantityOfTransactions  end) as '5',
count(Case when tempTable.quantityOfItemsPurchased >= 6 then tempTable.QuantityOfTransactions  end) as '6+'
from TempTable group by Organization, MemberNumber, ItemId

Answer 1

你需要这样的东西:

SELECT Organization, MemberNumber, ItemID,
       SUM(CASE WHEN QuantityItemsPurchased = 1 
           THEN QuantityOfTransactions ELSE 0 END) As `1`,
       SUM(CASE WHEN QuantityItemsPurchased = 2 
           THEN QuantityOfTransactions ELSE 0 END) As `2`,
       SUM(CASE WHEN QuantityItemsPurchased = 3 
           THEN QuantityOfTransactions ELSE 0 END) As `3`,
       SUM(CASE WHEN QuantityItemsPurchased = 4 
           THEN QuantityOfTransactions ELSE 0 END) As `4`,
       SUM(CASE WHEN QuantityItemsPurchased = 5 
           THEN QuantityOfTransactions ELSE 0 END) As `5`,
       SUM(CASE WHEN QuantityItemsPurchased >= 6 
           THEN QuantityOfTransactions ELSE 0 END) As `6+`
FROM Table1
GROUP BY Organization, MemberNumber, ItemID

演示:SQL Fiddle