sql - 如何将 SQL 数据库中现有表中的行相乘？

Question

我的 SQL 数据库中有表

Code | Ordered | Cut01 | Cut02 | Cut03 | Confirmed
--------------------------------------------------
8832     10        1        1      3        5
8821     12        0        1      2        9
8122     20       10        0      0       10
8901     11        0        8      1        2

如何为每个 Code 分隔行，其中有多个 Cuts* 即 <> 0并像这样繁殖自己:

Code | Ordered | Cut01 | Cut02 | Cut03 | Confirmed
--------------------------------------------------
8832      1        1        0      0        0
8832      1        0        1      0        0
8832      8        0        0      3        5
--------------------------------------------------
8821      1        0        1      0        0
8821     11        0        0      2        9
--------------------------------------------------
8122     20       10        0      0       10
--------------------------------------------------
8901      8        0        8      0        0
8901      3        0        0      1        2

Confirmed = Ordered - Cut01 - Cut02 - Cut03

正如您在结果表中看到的，每个代码的 Ordered 总和 = 为第一个表中的此代码排序，它也适用于 Confirmed 总和。但是在每一行中，我只有一个 Cut 不等于 0。我如何使用 T-SQL 来做到这一点？

Answer 1

SELECT
  Code,
  CASE WHEN sub_row = 1 THEN cut01
       WHEN sub_row = 2 THEN cut02
       WHEN sub_row = 3 THEN ordered - cut01 - cut02 END     Ordered,
  CASE WHEN sub_row = 1 THEN cut01     ELSE 0 END            cut01,
  CASE WHEN sub_row = 2 THEN cut02     ELSE 0 END            cut02,
  CASE WHEN sub_row = 3 THEN cut03     ELSE 0 END            cut03,
  CASE WHEN sub_row = 3 THEN confirmed ELSE 0 END            confirmed
FROM
  yourTable
CROSS JOIN
  (          SELECT 1 AS sub_row
   UNION ALL SELECT 2 AS sub_row
   UNION ALL SELECT 3 AS sub_row ) AS multiplier_table
WHERE
     (sub_row = 1 AND cut01 > 0)
  OR (sub_row = 2 AND cut02 > 0)
  OR (sub_row = 3 AND cut03 > 0)
  OR (sub_row = 3 AND cut01 = 0 AND cut02 = 0 AND cut03 = 0)