logo标签列表

SQLITE - 将行正确地转置为列

sqldatabasesqlite
10
我是一名有用的助手，可以为您翻译文本。以下是需要翻译的内容：

我有一个包含产品订单表的数据库，就像这样：

order_id | prod_code | prod_color | size | quantity |  
-----------------------------------------------------
1          SHIRT       001          S      10
1          SHIRT       001          M      7
1          SHIRT       001          L      8
1          SHIRT       001          XL     1
1          SHIRT       995          S      2
1          SHIRT       995          M      1
1          SHIRT       995          L      0
1          SHIRT       995          XL     1
2          PANTS       ....

还有一个产品价格表格如下：

prod_code | prod_color | price | currency
-----------------------------------------
SHIRT       001          10      EUR
SHIRT       001          9       USD
SHIRT       001          50      YEN
SHIRT       001          15      RUB
SHIRT       995          20      EUR
SHIRT       995          29      USD
SHIRT       995          100     YEN
SHIRT       995          45      RUB 
PANTS       ....

我希望能够得到这样的视图（为了简单起见，按order_id筛选）：
order_id | prod_code | prod_color | size | quantity | EUR | USD | YEN | RUB
---------------------------------------------------------------------------
1          SHIRT       001          S      10         10    9     50    15
1          SHIRT       001          M      7          10    9     50    15
1          SHIRT       001          L      8          10    9     50    15
1          SHIRT       001          XL     1          10    9     50    15
1          SHIRT       995          S      2          20    29    100   45
1          SHIRT       995          M      1          20    29    100   45
1          SHIRT       995          L      0          20    29    100   45
1          SHIRT       995          XL     1          20    29    100   45

我已经在网上和SO上查找过了，发现这个问题可以准确地告诉我需要做什么，但我认为需要一些修改...
我尝试在数据库上运行这个查询：
SELECT o.order_id, o.prod_code, o.prod_color, o.size, o.quantity,
MAX(CASE WHEN p.currency = 'EUR' THEN p.price END) AS 'EUR',
MAX(CASE WHEN p.currency = 'USD' THEN p.price END) AS 'USD',
MAX(CASE WHEN p.currency = 'YEN' THEN p.price END) AS 'YEN',
MAX(CASE WHEN p.currency = 'RUB' THEN p.price END) AS 'RUB'
FROM products_order o JOIN products_prices p ON o.prod_code = p.prod_code
WHERE o.order_id = 1
GROUP BY o.order_id, o.prod_code, o.prod_color, o.size, o.quantity

将在价格列中返回每行中找到的整个列的最大值：因此，对于[product_color = 001]和[product_color = 995]，我都有价格等于20欧元（当001更便宜时，仅为10欧元！）
我也尝试过不使用MAX，但这会给我每个价格一个行，留下很多空单元格（然后我明白了MAX的用途:D）。
您知道如何做我想做的事情吗？ 我如何在单个prod_color内使用MAX而不是通过每个prod_color？
希望您能理解我的写作，提前感谢您的任何帮助。
（如果您需要更清晰地解释某些内容，请随时提问，我会尽快回复。
再次感谢，最好的问候。）
2
SQLFiddle: http://sqlfiddle.com/#!5/fadad - biziclop
2个回答
14

我添加了额外的 JOIN 条件：AND o.prod_color = p.prod_color

http://sqlfiddle.com/#!5/fadad/5

SELECT
  o.order_id, o.prod_code, o.prod_color, o.size, o.quantity,
  MAX(CASE WHEN p.currency = 'EUR' THEN p.price END) AS 'EUR',
  MAX(CASE WHEN p.currency = 'USD' THEN p.price END) AS 'USD',
  MAX(CASE WHEN p.currency = 'YEN' THEN p.price END) AS 'YEN',
  MAX(CASE WHEN p.currency = 'RUB' THEN p.price END) AS 'RUB'

FROM products_order o
JOIN products_prices p
  ON o.prod_code = p.prod_code
 AND o.prod_color = p.prod_color /* this line is added */

WHERE o.order_id = '1'
GROUP BY
  o.order_id,
  o.prod_code,
  o.prod_color,
  o.size,
  o.quantity

简化的示例查询，没有 GROUP BY 可以显示出差异：

http://sqlfiddle.com/#!5/fadad/13

1
动态查询的方式（对于每个枢轴列使用 CASE 表达式）应该是首选，但当您不知道枢轴列或由于某种原因很难在应用程序端生成查询时，有一个 pivot_vtab SQLite 虚拟表扩展的替代方法。它在撰写本文时不太实用，因为它似乎是概念性的工作，但这并不意味着它不能或不会改进。
我不会详细介绍如何构建扩展 - 您可以在这个 related answer 中看到它们。我正在使用问题中的数据如下。
CREATE TABLE products_order(
  order_id INTEGER,
  prod_code TEXT,
  prod_color INTEGER,
  size TEXT,
  quantity INTEGER
);

INSERT INTO products_order(order_id, prod_code, prod_color, size, quantity)
VALUES
  (1, 'SHIRT', 001, 'S', 10),  (1, 'SHIRT', 001, 'M', 7),
  (1, 'SHIRT', 001, 'L', 8),   (1, 'SHIRT', 001, 'XL', 1),
  (1, 'SHIRT', 995, 'S', 2),   (1, 'SHIRT', 995, 'M', 1),
  (1, 'SHIRT', 995, 'L', 0),   (1, 'SHIRT', 995, 'XL', 1);

CREATE TABLE products_prices(
  prod_code TEXT,
  prod_color INTEGER,
  price INTEGER,
  currency TEXT
);

INSERT INTO products_prices(prod_code, prod_color, price, currency)
VALUES
  ('SHIRT', 001, 10, 'EUR'),  ('SHIRT', 001, 9, 'USD'),
  ('SHIRT', 001, 50, 'YEN'),  ('SHIRT', 001, 15, 'RUB'),
  ('SHIRT', 995, 20, 'EUR'),  ('SHIRT', 995, 29, 'USD'),
  ('SHIRT', 995, 100, 'YEN'), ('SHIRT', 995, 45, 'RUB');

透過以下方式建立樞紐 (我假設已載入擴充套件，.load ./pivot_vtab)。

由于扩展仅限于一个“行”列（您在其中进行分组），因此创建临时映射。我假设订单键是（order_id，prod_code，prod_color，size），并且quantity函数依赖于order_id，因此无需对其进行分组，但它将在第3步中使用。
创建pivot表格使用rowmap表格。请注意，第3个“矩阵填充”查询也只期望单个列。
重新组合rowmap列。
可选地，如果要摆脱row_id列，可以使用另一个临时表删除它（自SQLite 3.35以来支持ALTER TABLE ... DROP COLUMN）。
pivot_vtab 的作者已经实现了这个改进，因此不再需要临时表。 - saaj
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接