mysql - having 子句会跳过计算字段的偶数值

Question

场景:每个用户每天仅处理 100 个项目的项目队列。

说明:这是过去 24 小时已处理记录与未处理项目以及剩余每日配额的自连接。

SELECT 
    q.*, 
    case when delivery.processed is null then 0 else delivery.processed end as processed, 
    @subcounter := IF(@context = q.user_id, @subcounter+1, 1) as counter,
    @context := q.user_id as uid 
from queue q

LEFT JOIN (
    SELECT `user_id`, count(*) as `processed` FROM `queue` 
    where `status` = 'processed' and `updated_at` >= DATE_SUB(NOW(), INTERVAL 24 HOUR)
    GROUP BY `user_id`) as `delivery`
    ON `delivery`.`user_id` = `q`.`user_id`

WHERE 
    `q`.`status` = 'queued' and 
    (`delivery`.`processed` is null or `delivery`.`processed` < 100)
HAVING
    `counter` <= (100 - `delivery`.`processed`)

最终条件counter <= (100 - delivery.processed)跳过计数器为偶数的每个备用行是错误的。

如果没有这个最终条件，我就无法跳过那些将超过每日配额限制 100 的记录。

Table: queue
Columns:
id  bigint(20) UN AI PK
created_at  timestamp
updated_at  timestamp
job_id  bigint(20)
user_id bigint(20)
status  varchar(64)

这是查询结果:(最后第二列缺少序列，由于有子句条件而跳过所有偶数)

'224244', '2016-03-24 22:44:32', '2016-03-24 22:44:32', '23942', '3', 'queued', '0', '1', '3'
'224995', '2016-03-24 22:45:25', '2016-03-24 22:45:25', '23963', '3', 'queued', '0', '3', '3'
'225208', '2016-03-24 22:45:40', '2016-03-24 22:45:40', '23970', '3', 'queued', '0', '5', '3'
'225316', '2016-03-24 22:45:47', '2016-03-24 22:45:47', '23972', '3', 'queued', '0', '7', '3'
'227247', '2016-03-27 15:17:00', '2016-03-27 15:17:00', '22741', '3', 'queued', '0', '9', '3'
'227258', '2016-03-27 15:17:00', '2016-03-27 15:17:00', '22747', '3', 'queued', '0', '11', '3'
'228291', '2016-03-27 15:18:22', '2016-03-27 15:18:22', '24537', '3', 'queued', '0', '13', '3'

Answer 1

我认为您需要一个额外的层 - 以及围绕您所拥有的额外的SELECT。除此之外，将 HAVING 移到最外层，此时您可以将其更改为 WHERE。