MySQL 如何仅通过以下查询显示最大值/最大值？

Question

我有下表:

mysql> select * from F_FINDINGS;
+---------+------------+
|   F_ID  | CONFIDENCE |
+---------+------------+
|       1 |        100 | 
|       2 |         70 | 
|       2 |       0.35 | 
|       1 |        100 | 
+---------+------------+

mysql> select * from F_THINGS;
+---------+-------------------+--------+------------+
|   F_ID  |    F_TITLE        |  S_ID  | F_VALUE    |
+---------+-------------------+--------+------------+
|       1 | STUFF A           |      1 |        1.1 |       
|       2 | STUFF C           |      1 |      202.2 | 
|       3 | OBJECT P          |      1 |       10.7 |           
|       4 | Things X          |      2 |        540 |   
|       5 | STUFF D           |      2 |       1080 | 
|       6 | OBJECT M          |      2 |        455 |       
|       7 | Things A          |      3 |        333 |              
|       8 | Things B          |      4 |        825 |         
|       9 | STUFF A           |      1 |      103.4 |          
|      10 | STUFF A           |      1 |       98.4 |
+---------+-------------------+--------+------------+

  mysql> select * from DUMP;
   +--------+----------+
   | D_ID   | D_NAME   |
   +--------+----------+
   |      1 |   E0     | 
   |      2 |   A1     | 
   |      3 |   AB     | 
   +--------+----------+

mysql> select * FROM STUFF;
+--------+--------+-------------------+
|   S_ID |   D_ID |   S_TITLE         |
+--------+--------+-------------------+
|      1 |      1 | plastic           | 
|      2 |      2 | metal             | 
|      3 |      3 | wood              | 
|      4 |      3 | gel               | 
+--------+--------+-------------------+

我正在使用以下查询

SELECT
d.D_NAME,
s.S_TITLE,
f.F_VALUE,
IF(r.CONFIDENCE IS NULL, 'N/A', CONCAT((r.CONFIDENCE),'%')AS CONFIDENCE

FROM F_THINGS f

JOIN STUFF s ON s.S_ID=f.S_ID
JOIN DUMP d on d.D_ID=s.D_ID

LEFT JOIN F_FINDINGS r ON f.F_ID=r.F_ID;

期望的结果:

+----------+-----------+------------+------------+
| D_NAME   | S_TITLE   | F_VALUE    | CONFIDENCE |
+----------+-----------+------------+------------+
| E0       |  plastic  |      202.2 | 70%        |  
| E0       |  plastic  |       10.7 | N/A        |
| E0       |  plastic  |        1.1 | 100%       |  
| A1       |  metal    |        540 | N/A        | 
| A1       |  metal    |       1080 | N/A        | 
| A1       |  metal    |        455 | N/A        | 
| AB       |  wood     |        333 | N/A        | 
| AB       |  wood     |        825 | N/A        | 
| E0       |  gel      |      103.4 | N/A        | 
| E0       |  gel      |       98.4 | N/A        | 
+----------+-----------+------------+------------+

我只想显示每个值的最高值

Answer 1

这应该可以做到，但是 STUFF 中没有任何值可以给出您想要的结果(“凝胶”和“塑料”将需要相同的 S_ID 和 D_ID，并且仍然以某种方式不同)，这使得结果与您想要的一款。下次您可能想要添加东西，这样就没有人需要对其进行逆向工程;-)

SELECT
d.D_NAME,
s.S_TITLE,
f.F_VALUE,
IF(MAX(r.CONFIDENCE) IS NULL, 'N/A', CONCAT((MAX(r.CONFIDENCE)),'%')) AS CONFIDENCE
FROM F_THINGS f
JOIN STUFF s ON s.S_ID=f.S_ID
JOIN DUMP d on d.D_ID=s.D_ID
LEFT JOIN F_FINDINGS r ON f.F_ID=r.F_ID
GROUP BY D_NAME, S_TITLE, F_VALUE;

演示 here .

还添加了一个演示，其中修正了 F_THINGS 中的值(您想要与凝胶和塑料分组的线条有不同的 S_ID)，以使您需要的分组成为可能 here .