您需要首先线性化sales表，方法是使用UNION ALL将模式从〈a，b，c，d〉更改为〈product，sizes〉，如下面的cte所示。
然后您可以：

• 使用INNER JOIN来过滤您需要的产品，
• 使用COUNT生成你的数量字段，通过聚合产品和尺寸

WITH cte_sales AS (
    SELECT 'a' AS product, a AS sizes FROM sales 
    UNION ALL
    SELECT 'b' AS product, b AS sizes FROM sales
    UNION ALL
    SELECT 'c' AS product, c AS sizes FROM sales
    UNION ALL
    SELECT 'd' AS product, d AS sizes FROM sales
)
SELECT s.product, s.sizes, COUNT(s.sizes) AS qty
FROM       products  p
INNER JOIN cte_sales s
        ON p.product = s.product
GROUP BY s.product, s.sizes
ORDER BY s.product, s.sizes

如果你事先知道你只想要a、b和c销售，并且想要避免加入，你也可以做如下操作：

WITH cte_sales AS (
    SELECT 'a' AS product, a AS sizes FROM sales 
    UNION ALL
    SELECT 'b' AS product, b AS sizes FROM sales
    UNION ALL
    SELECT 'd' AS product, d AS sizes FROM sales
)
SELECT s.product, s.sizes, COUNT(s.sizes) AS qty
FROM cte_sales s
GROUP BY s.product, s.sizes
ORDER BY s.product, s.sizes

输出：
| 积|大小|数量|
| - ------|- ------|- ------|
| 项目a|升|1个|
| 项目a|米|第二章|
| 项目a|S的|1个|
| 项目a|xs|四个|
| b.人口基金|米|三个|
| b.人口基金|S的|第二章|
| b.人口基金|加大码|三个|
| 日|升|四个|
| 日|米|1个|
| 日|S的|1个|
| 日|加大码|1个|
| 日|xs|1个|
检查here演示。