检索为Mysql中的树数据定义的最后一个值

Product Tier1 Tier2 Tier3   value
 A       ANZ    -     -       2
 A       ANZ    DE    -       3
 A       ANZ    DE    Outlet  9
 B       EMEA   -     -       4
 B       EMEA   IT    -       6

我每次都对为产品定义的最后一个值感兴趣.
例如,对于产品A,我应该得到值9,对于产品B,我应该得到值6.

是否有可能我可以直接使用sql查询获得结果,或者我加载整个产品行并在后端代码中执行操作以获取最终值.什么是更可取的.我正在使用MYsql.

ANZ       2        
   |
   DE       3
   |
   OUTLET - 9 last defined level value is 9


 EMEA   4
  |
 IT     6
  |
  ?     No value defined so last defined level value is 6

案件可以

情况1

Product Tier1 Tier2 Tier3   value
     A       ANZ    -     -       2
     A       ANZ    DE    -       3
     A       ANZ    DE    Outlet  9

Desired result = 9

案例2

Product Tier1 Tier2 Tier3   value
         A       ANZ    DE    Outlet  9

   Desired result = 9

情形3

Product Tier1 Tier2 Tier3   value
         A       ANZ    -     -       2
         A       ANZ    DE    Outlet  9

   Desired result = 9

情形4

Product Tier1 Tier2 Tier3   value
     A       ANZ    -     -       2
     A       ANZ    DE    -       3

   Desired result = 3

case5

case4
   Product Tier1 Tier2 Tier3   value
     A       ANZ    -     -       2

 Desired result = 2

这是一个通过提供数据验证的解决方案.

SQL：

SELECT
    product,
    tier1,
    tier2,
    tier3,
    value
FROM 
    tbl tbl2
WHERE
    ( tbl2.product, ((tier1 IS NOT NULL) + (tier2 IS NOT NULL ) + (tier3 IS NOT NULL)) ) IN
        (
            SELECT 
                product,
                MAX( (tier1 IS NOT NULL) + (tier2 IS NOT NULL ) + (tier3 IS NOT NULL) ) maxTierCnt
            FROM
                tbl
            WHERE
                tbl.product = tbl2.product
        );

输出：

mysql> SELECT * FROM tbl;
+---------+-------+-------+--------+-------+
| product | tier1 | tier2 | tier3  | value |
+---------+-------+-------+--------+-------+
| A       | ANZ   | NULL  | NULL   |     2 |
| A       | ANZ   | DE    | NULL   |     3 |
| A       | ANZ   | DE    | Outlet |     9 |
| B       | EMEA  | NULL  | NULL   |     4 |
| B       | EMEA  | IT    | NULL   |     6 |
+---------+-------+-------+--------+-------+
5 rows in set (0.00 sec)

mysql>
mysql> SELECT
    ->     product,
    ->     tier1,
    ->     tier2,
    ->     tier3,
    ->     value
    -> FROM
    ->     tbl tbl2
    -> WHERE
    ->     ( tbl2.product, ((tier1 IS NOT NULL) + (tier2 IS NOT NULL ) + (tier3 IS NOT NULL)) ) IN
    ->         (
    ->             SELECT
    ->                 product,
    ->                 MAX( (tier1 IS NOT NULL) + (tier2 IS NOT NULL ) + (tier3 IS NOT NULL) ) maxTierCnt
    ->             FROM
    ->                 tbl
    ->             WHERE
    ->                 tbl.product = tbl2.product
    ->         );
+---------+-------+-------+--------+-------+
| product | tier1 | tier2 | tier3  | value |
+---------+-------+-------+--------+-------+
| A       | ANZ   | DE    | Outlet |     9 |
| B       | EMEA  | IT    | NULL   |     6 |
+---------+-------+-------+--------+-------+
2 rows in set (0.00 sec)