是否存在“没有”？像“WHERE XXX NOT IN”这样的语法？
- 我有几个查询获取将来将被删除的行的 ID 号。
- 行号被放入一个字符串中并放置在下面的查询中（您可以在其中看到“2”）。
我希望结果忽略这些行（就好像它们已经被删除一样）。
SELECT MAX(T1.id) AS MAXid FROM 交易 AS T1 id 不在的地方 (2) GROUP BY T1.位置 按 T1 位置排序
我的猜测是我需要用“HAVING”替换“WHERE”行，但我找不到“NOT HAVING”语法。
按照当前编写此查询的方式，如果 WHERE 子句中列出了该位置的最大 id，则它不会返回 T1.position 的行。
如何让此查询为我提供 T1.position 的最大 ID 同时忽略 WHERE 子句中列出的 ID 行？
- 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
My guess is that I need to replace the "WHERE" line with "HAVING", but I cannot find "NOT HAVING" syntax.
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.
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?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
HAVING id NOT IN (2)应该可以；[NOT] IN不限于WHERE子句。HAVING id NOT IN (2)should work;[NOT] INisn't limited toWHEREclauses.HAVING 不是您所需要的 - 仅当您想按 MAX 过滤时它才有用。例如，如果您不想获取所有 MAXid，而只想获取大于 2 的 MAXid，则可以使用 HAVING MAXid > 。 2.
据我了解，您想忽略一些行并计算剩余行的MAXid。为此，我认为你的说法是正确的。如果您的 NOT IN 子句中提到了该职位的所有 ID，则该职位不会在结果集中列出。这是合理的，因为没有任何东西可以计算 MAX。如果某个职位的某些 ID 列在 NOT IN 中，而另一些则未，则您应该获取未列在 NOT IN 中的 MAX 个位置。
如果您的结果集与这些表达式不匹配，您应该调试插入到 NOT IN 中的字符串 - 也许它意外地包含了太多 id。
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.
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 all its 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.
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.
HAVING 的有效语法如下
Valid syntax for HAVING is like this
你尝试过吗
did u try with