两列的 MySQL 动态交叉表

Question

女士们，先生们，

我在 MySQL 5.1 中遇到以下问题。这是我的 table 。

Sale | Store | Product | Discount
---------------------------------
 1   |   1   |   B     | Yes
 2   |   1   |   B     | Yes
 3   |   1   |   B     | No
 4   |   1   |   D     | Yes
 5   |   1   |   A     | No
 6   |   2   |   A     | No
 7   |   3   |   B     | No
 8   |   3   |   B     | No
 9   |   1   |   D     | Yes
10   |   2   |   A     | No

现在，这当然是一种非常不方便的数据组织方式，但这就是数据恰好进入(并将继续进入)的方式。

我需要能够向它抛出一个完全任意的产品类型列表(如果您愿意，可以用逗号分隔)；比方说 A,B,C。最后，我需要一份什么商店卖什么的 list 。这是一个中间结果，以使其更易于理解。

Store | Product | Sales | Discounts
-----------------------------------
  1   |   A     |  1    | 0
  1   |   B     |  3    | 2
  1   |   C     |  0    | 0
  2   |   A     |  2    | 0
  2   |   B     |  0    | 0
  2   |   C     |  0    | 0
  3   |   A     |  0    | 0
  3   |   B     |  2    | 0
  3   |   C     |  0    | 0

我最终需要的是:

Store | Product types sold | Discounts given on product types:
-------------------------------------------------------------
  1   |         2          |        1
  2   |         1          |        0
  3   |         1          |        0

第三列表示有多少已售出(和查询)的产品类型至少获得了一次折扣。

我已经尝试过各种动态交叉表生成查询(是的，我已经看到了所有这些)，但我没有足够的脑力来一次性解决所有问题。您将如何以最有效的方式处理这个问题？临时表/存储过程等都可以，非常欢迎提供粗略的想法。谢谢!

Answer 1

您可以按商店和产品对数据进行分组以生成中间结果:

mysql> SELECT Store, Product,
COUNT(1) AS Sales,
SUM(IF(Discount='Yes', 1, 0)) AS Discounts
FROM input
WHERE Product IN ('A', 'B', 'C', 'D')
GROUP BY Store, Product
+-------+---------+-------+-----------+
| Store | Product | Sales | Discounts |
+-------+---------+-------+-----------+
|     1 | A       |     1 |         0 |
|     1 | B       |     3 |         2 |
|     1 | D       |     2 |         2 |
|     2 | A       |     2 |         0 |
|     3 | B       |     2 |         0 |
+-------+---------+-------+-----------+
5 rows in set (0.00 sec)

然后您可以仅按商店对该结果进行分组，以将其转换为您的最终结果:

mysql> SELECT Store,
COUNT(1) AS `Product types sold`,
SUM(Discounts) AS `Discounts given on product types`
FROM (
SELECT Store, Product,
COUNT(1) AS Sales,
SUM(IF(Discount='Yes', 1, 0)) AS Discounts
FROM input
WHERE Product IN ('A', 'B', 'C', 'D')
GROUP BY Store, Product
) AS intermediate
GROUP BY Store
+-------+--------------------+----------------------------------+
| Store | Product types sold | Discounts given on product types |
+-------+--------------------+----------------------------------+
|     1 |                  3 |                                4 |
|     2 |                  1 |                                0 |
|     3 |                  1 |                                0 |
+-------+--------------------+----------------------------------+
3 rows in set (0.00 sec)

请注意，第一个查询是第二个查询的子查询。