MySQL中的聚合行将NULL值忽略为单行

给出一张桌子

id  date        job    color  qty
1   2018-12-10  12345  green  1
2   2018-12-11  12345        
3   2018-12-15  12345       
4   2018-12-21  12345  red    
5   2018-12-21  12345         4
6   2018-12-22  12345

id 列是自动递增的,是表的主键 .

一个简单的查询

SELECT * FROM `table` WHERE `job` = '12345' ORDER BY `id` ASC;

将按插入顺序返回作业 12345 的所有记录 .

问题:如何查询表只返回包含每列最新值的单行?

所需的行看起来像这样

6   2018-12-22  12345  red    4

回答(4)

2 years ago

在查询中使用LIMIT

SELECT * FROM `table` WHERE `job` = '12345' ORDER BY `id` DESC LIMIT 1;

2 years ago

您可以使用单独的子查询来查找每个单独列中的最大值:

SELECT
    (SELECT MAX(id) FROM yourTable) AS id,
    (SELECT MAX(date) FROM yourTable) AS date,
    (SELECT MAX(job) FROM yourTable) AS job,
    (SELECT MAX(color) FROM yourTable) AS color,
    (SELECT MAX(qty) FROM yourTable) AS qty;

演示

我不知道你为什么要这样做,这可能表明你的数据库设计/规划不好 .

2 years ago

一种方法是使用 Group_Concat()Substring_Index() 来提取最新的非空值 . GROUP_CONCAT() 将忽略 null 值 .

SELECT 
  MAX(id) AS id, 
  MAX(date) AS date, 
  MAX(job) AS job, 
  SUBSTRING_INDEX(GROUP_CONCAT(color ORDER BY id DESC), ',', 1) AS color, 
  SUBSTRING_INDEX(GROUP_CONCAT(qty ORDER BY id DESC), ',', 1) AS qty 
FROM `table` 
WHERE `job` = '12345';

上述查询中的一个假设是 date 的最大值对应于最大 id 值 .

另外,因为我们只关心"latest"值(只取一个值);我们不会受 group_concat_max_len 变量的限制 .

2 years ago

您可以使用子查询来获得所需的结果:

select max(id) id, 
       job, 
       max(`date`) `date`,
      (select qty from tbl
      where qty is not null
       and tbl.job = job
      order by `date` desc
      limit 1) qty,
      (select color from tbl
      where color is not null
        and tbl.job = job
      order by `date` desc
      limit 1) color
from tbl
group by job