如何返回组中具有MIN/MAX值的行的ID?
问题描述:
SELECT
MAX(`client_id`) `client_id`
FROM
`phrases`
WHERE
`language_id` = 1 AND
`client_id` = 1 OR
`client_id` IS NULL
GROUP BY
`language_phrase_id`
我该如何获得id
对于保存MAX(`client_id`)
值的行?如何返回组中具有MIN/MAX值的行的ID?
我需要这个派生表的上下文,例如,
SELECT
`p2`.`phrase`
FROM
(SELECT `language_phrase_id`, MAX(`client_id`) `client_id` FROM `phrases` WHERE `language_id` = 1 AND `client_id` = 1 OR `client_id` IS NULL GROUP BY `language_phrase_id`) `p1`
INNER JOIN
`phrases` `p2`
ON
`p2`.`language_id` = 1 AND
`p1`.`language_phrase_id` = `p2`.`language_phrase_id` AND
`p1`.`client_id` = `p2`.`client_id`;
答
我有一点很难理解的要求,但是这似乎向是你在找什么。
不是最美丽的SQL,我相信它可以简化,但是一个起点;
SELECT p1.id, p1.phrase
FROM phrases p1
LEFT JOIN `phrases` p2
ON p1.language_id=p2.language_id
AND p1.language_phrase_id=p2.language_phrase_id
AND p1.client_id IS NULL and p2.client_id = 1
WHERE p2.id IS NULL AND p1.language_id=1
AND (p1.client_id=1 or p1.client_id IS NULL)
GROUP BY p1.language_phrase_id
答
使用窗口函数查找每个组最大,然后where
子句最大限度地选择行:
select p.*
from (SELECT p.*, max(client_id) partition by (language_phrase_id) as maxci
from phrases p
WHERE (`language_id` = 1 AND `client_id`= 1) or
`client_id` IS NULL
) p
where client_id = maxci
我还加括号来澄清你的where
声明。当混合and
和or
时,我总是使用括号来避免可能的混淆和错误。
现在您已将mysql
标记添加到您的语句中,但这不起作用。所以,这里是一个MySQL特定的解决方案:
select language_phrase_id,
substring_index(group_concat(id order by client_id desc), ',', 1) as max_id
from phrases
group by phrases p
注意这一点,如果id
将转换为:在这个过程中的字符串。如果它有不同的类型,则可以将其转换回来。
每组如上所述还是最大周期? – 2013-03-07 18:55:46
你真的想做'language_id = 1 AND client_id = 1 OR client_id'是NULL'而不是'language_id = 1 AND(client_id = 1 OR client_id' IS NULL)'? – Lamak 2013-03-07 18:55:48
以下是上下文,http://sqlfiddle.com/#!2/0fd79/1。 – Gajus 2013-03-07 18:56:00