MySQL 5.5 从表 2 中为表 1 中的每个唯一 ID 选择 MAX

Question

我有两个表:

table1        table2
column id     column id   column value
1             1           count_20
2             1           count_30
3             2           count_20
4             2           count_50
...           2           count_200
              3           count_30
              3           count_50
              3           count_200
              4           count_130
              ...         ...

我想构建一个查询，它将从 table1，column id 中选择每一行，并从相应的 column value 中选择 MAX 值table2 并在 value 列中将 DESC 缩短为 int。

所以输出应该是:

table1.id     value
2             count_200  
3             count_200  
4             count_130  
1             count_30  
...           ...  

我已经尝试过 JOIN 但随后，对于 table2 中的每个 value (count_%) 我得到了来自 table1 的相应 id。

SELECT table1.id, table2.id, table2.value FROM table1
LEFT JOIN table2
ON table2.id=table1.id
WHERE table1.id<'100'
ORDER BY
CASE value
  WHEN 'count_20' THEN '20' 
END DESC,
CASE value
  WHEN 'count_30' THEN '30' 
END DESC,
CASE value
  WHEN 'count_50' THEN '50' 
END DESC,
CASE value
  WHEN 'count_130' THEN '130' 
END DESC,
CASE value
  WHEN 'count_200' THEN '200' 
END DESC;

输出:

table1.id   table2.id   value
2           2           count_200  
3           3           count_200  
4           4           count_130  
2           2           count_50  
3           3           count_50  
1           1           count_30  
3           3           count_30  
1           1           count_20  
2           2           count_20  
...         ...  

如有任何帮助，我们将不胜感激。

Answer 1

试一试:

select t2.id, 
'count_' + cast(max(cast(replace(t2.value, 'count_', '') as int)) varchar) intValue
from table1 t1
join table2 t2 on t1.id = t2.id
group by t2.id
order by max(cast(replace(t2.value, 'count_', '') as int)) desc

这导致:

+----+-----------+| ID |   VALUE   |+----+-----------+|  2 | count_200 ||  3 | count_200 ||  4 | count_130 ||  1 | count_30  |+----+-----------+

fiddle here .