mysql - 需要重写查询以在 where 子句中使用 max 函数

Question

此时使用的查询:

update metrics.time_created 
set cumu_count = (
    select count(*) 
    from perf_stats.time 
    where date(insert_datetime)='2015-12-18'
) 
where id=max(id);

我在这里得到“组函数的无效使用”——我该如何重写它以保持相同的逻辑？我必须更新metrics.time_created 表中的最新行。

Answer 1

试试这个:

update metrics.time_created a
set cumu_count = (
  select count(*) 
  from perf_stats.time 
  where date(insert_datetime)='2015-12-18'
) 
where exists (
  select 1
  from (select max(id) as maxid from metrics.time_created) t
  where maxid = a.id
);

示例演示:http://sqlfiddle.com/#!9/6bc3cd/1

编辑:

根据评论，更改如下

update metrics.time_created a

set cumu_count = 
(
  select count(*) 
  from perf_stats.time pt
  where exists
  (
    select 1
    from (select max(curr_date) as mcd from metrics.time_created) x
    where mcd = date(insert_datetime)
  )
) 

where exists 
(
  select 1
  from (select max(id) as maxid from metrics.time_created) t
  where maxid = a.id
);

示例演示:http://sqlfiddle.com/#!9/fcc91a/1