我想知道如何引用 TBL_ITEM 中的三列得到SUM(QTY) FROM TBL_INVTY .

TBL_ITEM

+------+-------------+----------+
| CODE | PARENT_CODE | OLD_CODE |
+------+-------------+----------+
|    2 |        NULL |       20 |
|   2A |           2 |     NULL |
|   2B |           2 |     NULL | 
|    3 |        NULL |     NULL |
|    4 |        NULL |     NULL |
|   20 |        NULL |     NULL |
+------+-------------+----------+

TBL_INVTY

+------+-----+
| CODE | QTY |
+------+-----+
|    2 |   2 |
|   2A |   4 |
|   2B |   1 |
|    3 |   3 |
|    4 |   5 |
|   20 |   2 |
+------+-----+

然后，得出这个:

+------+-----+
| CODE | QTY |
+------+-----+
|    2 |   9 |
|    3 |   3 |
|    4 |   5 |
+------+-----+

最后一张表的第一行WHERE CODE=2有一个 QTY 9 因为我拿了 SUM(QTY) from TBL_INVTY WHERE CODE IN (20, 2A, 2B, 2)关于 TBL_ITEM 中的引用资料.

最佳答案

诀窍是从 tbl_item 得到一个两列的结果集。我称之为“匹配”和“ map ”的两列。 “匹配”列中的值是我们将在 JOIN 谓词中使用的值，用于查找“匹配”行。但我们将从映射列返回值，并对其执行 GROUP BY。

对于您的示例，我认为此查询为我们提供了所需的结果集:

SELECT IF(i.old_code IS NOT NULL,i.old_code,i.code) AS `match`
     , IF(i.parent_code IS NOT NULL,i.parent_code,i.code) AS map
  FROM tbl_item i
  LEFT
  JOIN tbl_item j
    ON j.old_code = i.code
 WHERE j.old_code IS NULL
 UNION ALL
SELECT k.code
     , k.code
  FROM tbl_item k
 WHERE k.old_code IS NOT NULL

这应该给出这个结果集:

+-------+-------+
| match | map   |
+-------+-------+
|   20  |   2   |
|   2A  |   2   |
|   2B  |   2   |
|    3  |   3   |
|    4  |   4   |
|    2  |   2   |
+-------+-------+

我们需要确保“匹配”列是唯一的，这样我们就不会无意中将 tbl_invty 中的一行与该集合中的多行匹配。这在这组数据中不是问题，但在更一般的情况下可能是。此外，这只会将旧的“一级”转换为新的。如果"new"代码后来被取代，则此查询将不会找到“最新”代码，只会找到行中的值。同样，这不是此数据的问题，但在更一般的情况下这可能是个问题。

(还有其他方式来编写查询，例如使用 CASE 表达式，而不是 IF，或者更简洁:

SELECT IFNULL(i.old_code,i.code) AS `match`
     , IFNULL(i.parent_code,i.code) AS map
  FROM tbl_item i 
  LEFT
  JOIN tbl_item j
    ON j.old_code = i.code
 WHERE j.old_code IS NULL
 UNION ALL
SELECT k.code
     , k.code
  FROM tbl_item k
 WHERE k.old_code IS NOT NULL      

无论如何，一旦我们的查询获得了合适的结果集，我们就可以轻松获得 SUM(QTY)。我们只是将该查询用作另一个查询中的行源。 (我们称之为“内联 View ”，尽管 MySQL 称它为“派生表”，更准确地描述了 MySQL 实际上是如何处理 View 查询的。)

SELECT m.map      AS CODE
     , SUM(v.qty) AS QTY
  FROM tbl_invty v
  JOIN (
         SELECT IFNULL(i.old_code,i.code) AS `match` 
              , IFNULL(i.parent_code,i.code) AS map 
           FROM tbl_item i 
           LEFT 
           JOIN tbl_item j 
             ON j.old_code = i.code 
          WHERE j.old_code IS NULL 
          UNION ALL
         SELECT k.code
              , k.code
           FROM tbl_item k
          WHERE k.old_code IS NOT NULL
       ) m 
    ON m.match = v.code 
 GROUP 
    BY m.map 

跟进

问:如果一个 CODE 同时具有 PARENT_CODE 和 OLD_CODE 怎么办(我刚刚发现这是一种可能的情况，...

答:您需要测试查询以返回“匹配”和“ map ”代码。

鉴于该新案例(同时具有 PARENT_CODE 和 OLD_CODE 的行)，需要进行调整。您确实需要遍历每个案例，并确定每个案例应该返回什么。

似乎我们想要选取表中的每个代码(无论是在 CODE 列、PARENT_CODE 列还是 OLD_CODE 列中)作为“匹配”代码，并为每个代码导出适当的“映射”代码一个。

我将假设 tbl_item 表中的 CODE 列不是 NULL(只是为了简化这一点。)

这些是我正在考虑的四个查询，用于处理所有这些情况:

-- rows with PARENT_CODE,  match=CODE map=PARENT_CODE
SELECT i.code AS `match_code`
     , i.parent_code AS `map_code`
  FROM tbl_item i
 WHERE i.parent_code IS NOT NULL


-- rows with PARENT_CODE and OLD_CODE,  match=OLD_CODE map=PARENT_CODE
SELECT j.old_code
     , j.parent_code
  FROM tbl_item j
 WHERE j.parent_code IS NOT NULL
   AND j.old_code IS NOT NULL 

-- rows with no PARENT_CODE,  match=CODE map=CODE   
SELECT k.code
     , k.code
  FROM tbl_item k
 WHERE k.parent_code IS NULL

-- rows with OLD_CODE and no PARENT_CODE,  match=OLD_CODE map=CODE
SELECT l.old_code
     , l.code
  FROM tbl_item l
 WHERE l.parent_code IS NULL
   AND l.old_code IS NOT NULL

这些将使用 UNION ALL 运算符组合在一起。

我可以设想古怪的数据，其中相同的 CODE 出现不止一次，并且每个都可能指向不同的 PARENT_CODE

TBL_ITEM 中的一些古怪(意外)行示例。

+------+-------------+----------+
| CODE | PARENT_CODE | OLD_CODE |
+------+-------------+----------+
|  77A |         77A |     NULL |
|  77A |         77B |      77A |
|   77 |          77 |      77A |
|   77 |         77A |     NULL |
+------+-------------+----------+

遇到这样的烂摊子我们该怎么办？

就获取 SUM(QTY) 而言，我们需要确保在“映射/匹配”行集中给定的代码仅出现一次。 (如果我们在那里得到倍数，那么 SUM 会太高，因为我们要匹配多个...

快速修复是将查询包装在另一个查询中，以消除重复项并只选择一个代码映射到。这可能不是“正确的”修复，但它为我们提供了一个我们可以使用的结果集:

SELECT u.match
     , MIN(u.map) AS map
  FROM (
          -- query to get match/map rowset here
       ) u
 GROUP BY u.match

然后我们会将此查询用作获取 SUM(QTY) 的内联 View ，与之前的查询相同。我们刚刚在别名为 m 的内联 View 中更改了该查询。

SELECT m.map      AS CODE
     , SUM(v.qty) AS QTY
  FROM tbl_invty v
  JOIN (
         SELECT u.match
              , MIN(u.map) AS map
           FROM (
                  -- query to get match/map rowset here
                ) u
          GROUP BY u.match
       ) m 
    ON m.match = v.code 
 GROUP
    BY m.map

在这一点上，几乎不言而喻，我们真正想要的是使查询简单，只有两列，一个 (unique 和 not null) match_code 和可选的 map_code。

这将是一个非常简单的查询:

SELECT IFNULL(m.map_code,m.match_code) AS CODE
     , SUM(v.qty) AS QTY
  FROM tbl_invty v
  JOIN tbl_match_map m
    ON m.match_code = v.code
 GROUP
    BY IFNULL(m.map_code,m.match_code)

它正在从给定的表中生成“tbl_match_map”行集，这是一项艰苦的工作。

关于php - 带有父代码的多列引用，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/17715539/