MySQL 是否有像“WHERE XXX NOT IN”这样的“NOT HAVING”语法？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5244210/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Is there a "NOT HAVING" syntax like "WHERE XXX NOT IN"?
提问by Drewneedshelp
- I have a few queries get the ID numbers of rows that will be deleted in the future.
- The row numbers are put into a string and placed in the query below (where you see "2").
I want the results to ignore the rows (as though they have already been deleted).
SELECT MAX(T1.id) AS MAXid FROM transactions AS T1 WHERE id NOT IN ( 2 ) GROUP BY T1.position ORDER BY T1.position
- 我有几个查询获取将来将被删除的行的 ID 号。
- 行号被放入一个字符串并放置在下面的查询中（您看到“2”）。
我希望结果忽略行（好像它们已经被删除）。
SELECT MAX(T1.id) AS MAXid FROM transactions AS T1 WHERE id NOT IN ( 2 ) GROUP BY T1.position ORDER BY T1.position
My guess is that I need to replace the "WHERE" line with "HAVING", but I cannot find "NOT HAVING" syntax.
我的猜测是我需要用“HAVING”替换“WHERE”行，但我找不到“NOT HAVING”语法。
The way this query is currently written, it will not return a row for T1.position if the max id for the position is listed in the WHERE clause.
根据当前编写此查询的方式，如果位置的最大 id 列在 WHERE 子句中，则不会为 T1.position 返回一行。
How do I get this query to give me the max ID for the T1.position while overlooking the rows with IDs listed in the WHERE clause?
如何让这个查询为我提供 T1.position 的最大 ID，同时忽略 WHERE 子句中列出的 ID 的行？
回答by geekosaur
HAVING id NOT IN (2)should work; [NOT] INisn't limited to WHEREclauses.
HAVING id NOT IN (2)应该管用; [NOT] IN不限于WHERE条款。
回答by titanoboa
HAVING is not what you need - it is only useful if you want to filter by MAX. For example, if you do not want to get all MAXids but only those larger than 2, you can use HAVING MAXid > 2.
HAVING 不是您所需要的 - 它仅在您想按 MAX 过滤时才有用。例如，如果您不想获取所有 MAXid，而只想获取大于 2 的 MAXid，则可以使用 HAVING MAXid > 2。
As far as I understand, you want to ignore some rows and calculate the MAXid of the remaining rows. For this purpose, your statement looks correct to me. Afaics a position is not listed in the result set if allits ids are mentioned in your NOT IN clause. This is reasonable since there is nothing left you could calculate a MAX of. If some of a position's ids are listed in NOT IN, while others are not, you should get the MAX of those not listed in NOT IN.
据我了解，您想忽略一些行并计算剩余行的 MAXid。为此，你的陈述在我看来是正确的。如果您的 NOT IN 子句中提到了位置的所有id，则Afaics 不会在结果集中列出该位置。这是合理的，因为没有什么可以计算 MAX 的了。如果某个职位的某些 id 列在 NOT IN 中，而另一些则没有，则您应该获取未列在 NOT IN 中的那些的 MAX。
If your result set does not match these expactations, you should debug the string you insert into NOT IN - maybe it accidentally contains too many ids.
如果您的结果集与这些解释不匹配，您应该调试插入到 NOT IN 中的字符串 - 也许它不小心包含了太多的 id。
回答by fredt
Valid syntax for HAVING is like this
HAVING 的有效语法是这样的
SELECT MAX(T1.id) AS MAXid
FROM transactions AS T1
GROUP BY T1.position
HAVING MAX(T1.id) NOT IN ( 2 )
ORDER BY T1.position
回答by diEcho
did u try with
你试过吗
SELECT MAX(t1.id) AS MAXid
FROM transactions t1
WHERE id <> ANY (2)
GROUP BY t1.position ORDER BY t1.position
