mysql - CASE WHEN THEN 其他表中的最小值

Question

我有两张 table 。我需要第一个表中第二个表的最小值。我想填充 least_t2 列，其中值将来自 table2 最小值。

table1
id   name  d1   d2   d3
1    asd   1    2    3 

table2
id   name  c1   c2   c3   c4
1    poi   7    5    6    9

我的输出表将如下所示 table1

id   name  d1   d2   d3   least  least_t2
1    asd   1    2    3    1        b  

我的查询如下:

select * , least(d1, d2, d3),
    (CASE least(d1, d2, d3)
        WHEN d1  THEN  (CASE least(c1, c2, c3, c4) from `table2`
                                WHEN c1  THEN 'a'
                                WHEN c2  THEN 'b'
                                WHEN c3  THEN 'c'
                                WHEN c4  THEN 'd'
                        END)
        WHEN d2  THEN  (CASE least(c1, c2, c3, c4) from `table2`
                                WHEN c1  THEN 'a'
                                WHEN c2  THEN 'b'
                                WHEN c3  THEN 'c'
                                WHEN c4  THEN 'd'
                        END)
        WHEN d3  THEN   (CASE least(c1, c2, c3, c4) from `table2`
                                WHEN c1  THEN 'a'
                                WHEN c2  THEN 'b'
                                WHEN c3  THEN 'c'
                                WHEN c4  THEN 'd'
                        END)

END) as least_t2
FROM `table1`

上面的查询没有结果显示。

Answer 1

试试这个查询:

SELECT
    t1.*,
    LEAST(t1.d1, t1.d2, t1.d3) AS `least`,
    CASE WHEN t2.c1 = LEAST(t2.c1, t2.c2, t2.c3, t2.cr) THEN 'a'
         WHEN t2.c2 = LEAST(t2.c1, t2.c2, t2.c3, t2.cr) THEN 'b'
         WHEN t2.c3 = LEAST(t2.c1, t2.c2, t2.c3, t2.cr) THEN 'c'
         WHEN t2.c4 = LEAST(t2.c1, t2.c2, t2.c3, t2.cr) THEN 'd'
         ELSE 'Unknown' END AS least_t2
FROM table1 t1
LEFT JOIN table2 t2
    ON t1.id = t2.id