blocks|key|3697074|text|不怎么有意思。having子句意味着聚合，这意味着您不再拥有原始行。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3697075|我认为你需要以下几点：|3697076|DELETE+from+UserPredictions
where+UserId+in+(select+UserId+from+UserPredictions+group+by+UserId+having+count(*)+<+500)|code-block|syntax|javascript|3697077|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|L|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|M|8|@]|9|@]|A|$G|H]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

Not really. The having clause implies an aggregation, which means you don't have the original rows any more.

I think you want the following:

<pre><code>DELETE from UserPredictions
where UserId in (select UserId from UserPredictions group by UserId having count(*) &lt; 500)
</code></pre>

blocks|key|3697150|text|可以在DELETE语句中使用联接的subselect：|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|3697151|DELETE+a
FROM+++UserPredictions+a
JOIN
(
++++SELECT+++UserId
++++FROM+++++UserPredictions
++++GROUP+BY+UserId
++++HAVING+++COUNT(1)+<+500
)+b+ON+a.UserId+=+b.UserId|code-block|syntax|javascript|3697152|3697153|SQLFiddle+Demo|3697154|entityMap|0|LINK|mutability|MUTABLE|url|http://www.sqlfiddle.com/#!3/e39fc/2/1^0|3|6|0|0|0|0|E|0|0^^$0|@$1|2|3|4|5|6|7|V|8|@$9|W|A|X|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|Y|8|@]|D|@]|E|$I|J]]|$1|K|3|-4|5|6|7|Z|8|@]|D|@]|E|$]]|$1|L|3|M|5|6|7|10|8|@]|D|@$9|11|A|12|1|13]]|E|$]]|$1|N|3|-4|5|6|7|14|8|@]|D|@]|E|$]]]|O|$P|$5|Q|R|S|E|$T|U]]]]

You can use a joined subselect within the <code>DELETE</code> statement:

<pre><code>DELETE a
FROM UserPredictions a
JOIN
(
 SELECT UserId
 FROM UserPredictions
 GROUP BY UserId
 HAVING COUNT(1) &lt; 500
) b ON a.UserId = b.UserId
</code></pre>

<hr>

<h2><a href="http://www.sqlfiddle.com/#!3/e39fc/2/1" rel="noreferrer">SQLFiddle Demo</a></h2>

blocks|key|3607918|text|尝试此嵌套查询：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3607919|DELETE+FROM+UserPredictions++
WHERE+UserId+IN+(SELECT+UserId
+++++++++++++++++FROM+UserPredictions+
+++++++++++++++++GROUP+BY+UserId
+++++++++++++++++HAVING+COUNT(*)+<+500)|code-block|syntax|javascript|3607920|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Try this nested query:

<pre><code>DELETE FROM UserPredictions 
WHERE UserId IN (SELECT UserId
 FROM UserPredictions 
 GROUP BY UserId
 HAVING COUNT(*) &lt; 500)
</code></pre>

blocks|key|1971825|text|您可以使用row_number()+OVER+(partition+by+)和CTE做很多事情，但前提是您的关系型数据库管理系统支持它。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1971826|示例：|1971827|WITH+CTE+AS+
(
++++SELECT+UserId,
++++ROW_NUMBER()+OVER+(PARTITION+BY+UserId+ORDER+BY+UserId)+AS+rowcount++++
++++FROM+UserPredictions
)
DELETE+FROM+CTE
WHERE+rowcount+<+500|code-block|syntax|javascript|1971828|有关行计数函数的其他信息：|1971829|https://docs.microsoft.com/en-us/sql/t-sql/functions/row-number-transact-sql?view=sql-server-2017|1971830|entityMap|0|LINK|mutability|MUTABLE|url^0|5|X|0|0|0|0|0|2P|0|0^^$0|@$1|2|3|4|5|6|7|X|8|@$9|Y|A|Z|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|10|8|@]|D|@]|E|$]]|$1|H|3|I|5|J|7|11|8|@]|D|@]|E|$K|L]]|$1|M|3|N|5|6|7|12|8|@]|D|@]|E|$]]|$1|O|3|P|5|6|7|13|8|@]|D|@$9|14|A|15|1|16]]|E|$]]|$1|Q|3|-4|5|6|7|17|8|@]|D|@]|E|$]]]|R|$S|$5|T|U|V|E|$W|P]]]]

You can do so much with using <code>row_number() OVER (partition by )</code> and CTE's, but only if your RDBMS supports it.

Example: 

<pre><code>WITH CTE AS 
(
 SELECT UserId,
 ROW_NUMBER() OVER (PARTITION BY UserId ORDER BY UserId) AS rowcount 
 FROM UserPredictions
)
DELETE FROM CTE
WHERE rowcount &lt; 500
</code></pre>

Additional info about the row count function:

<a href="https://docs.microsoft.com/en-us/sql/t-sql/functions/row-number-transact-sql?view=sql-server-2017" rel="nofollow noreferrer">https://docs.microsoft.com/en-us/sql/t-sql/functions/row-number-transact-sql?view=sql-server-2017</a>

blocks|key|2040067|text|我认为那是不可能的，不过你可以试试这个|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2040068|更新:既可以使用inner+join也可以使用In|offset|length|style|CODE|2040069|Declare+@Sample+table+
(
UserID+int,
col2+int
)
INSERT+INTO+@Sample
SELECT+1,50+UNION+ALL
SELECT+1,100+UNION+ALL
SELECT+2,150+UNION+ALL
SELECT+2,200+union+all
Select+4,500

DeLETE+FROM+@Sample++
WHERE+UserID+IN+(SELECT+UserID
+++++++++++++FROM+@Sample+
+++++++++++++GROUP+BY+UserID
+++++++++++++HAVING+COUNT(*)+>+1)


+Delete+O
+FROM+@Sample+O
+INNER+JOIN+
+(
++SELECT+UserID+
++FROM+@Sample++
++GROUP+BY+UserID
+HAVING+COUNT(*)+>1
+)+X
ON+O.UserID+=+X.UserID|code-block|syntax|javascript|2040070|我最初发布的答案是：|2040071|Delete+O
FROM+UserPredictions+O
INNER+JOIN+
(
+SELECT+UserID+
+FROM+UserPredictions++
+GROUP+BY+UserID
+HAVING+COUNT(*)+<500
+)+X
ON+O.UserID+=+X.UserID|2040072|entityMap^0|0|8|A|N|2|0|0|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|T|8|@$D|U|E|V|F|G]|$D|W|E|X|F|G]]|9|@]|A|$]]|$1|H|3|I|5|J|7|Y|8|@]|9|@]|A|$K|L]]|$1|M|3|N|5|6|7|Z|8|@]|9|@]|A|$]]|$1|O|3|P|5|J|7|10|8|@]|9|@]|A|$K|L]]|$1|Q|3|-4|5|6|7|11|8|@]|9|@]|A|$]]]|R|$]]

I don't think that is possible however you can try this 

Update : <code>In</code> as well as <code>inner join</code> can be used 

<pre><code>Declare @Sample table 
(
UserID int,
col2 int
)
INSERT INTO @Sample
SELECT 1,50 UNION ALL
SELECT 1,100 UNION ALL
SELECT 2,150 UNION ALL
SELECT 2,200 union all
Select 4,500

DeLETE FROM @Sample 
WHERE UserID IN (SELECT UserID
 FROM @Sample 
 GROUP BY UserID
 HAVING COUNT(*) &gt; 1)


 Delete O
 FROM @Sample O
 INNER JOIN 
 (
 SELECT UserID 
 FROM @Sample 
 GROUP BY UserID
 HAVING COUNT(*) &gt;1
 ) X
ON O.UserID = X.UserID
</code></pre>

The answer which i originally posted : 

<pre><code>Delete O
FROM UserPredictions O
INNER JOIN 
(
 SELECT UserID 
 FROM UserPredictions 
 GROUP BY UserID
 HAVING COUNT(*) &lt;500
 ) X
ON O.UserID = X.UserID
</code></pre>

I want to do something like below:

<pre><code>DELETE UserPredictions
 GROUP BY UserId
 HAVING COUNT(*) &lt; 500
</code></pre>

But I'm getting a syntax error. Is it even possible to do a delete with a HAVING clause in SQL Server or will I have to roll the counts up into a CTE and do a delete with a join?

Possible to do a delete with a HAVING clause?

33 天实现自己的 AI 进化论？发文瓜分机械键盘、耳机等万元奖池！点击参加活动：<a href="https://cloud.tencent.com/developer/article/2503022" target="_blank">https://cloud.tencent.com/developer/article/2503022</a> 
 <img src="https://qcloudimg.tencent-cloud.cn/raw/01543e0407db10898a7ee9f420794432.jpg"/>

技术创作特训营有奖征文

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋 

腾讯云AI代码助手

CODING DevOps

Cloud Studio

SDK中心

API中心

命令行工具

文章&问答评论现已支持表情

我想做一些如下的事情：DELETE UserPredictions GROUP BY UserId HAVING COUNT(*) < 500但是我得到了一个语法错误。是否可以在SQL Server中使用HAVING子句执行delete操作，或者我是否必须将计数汇总到CTE中并使用join执行delete操作？

问是否可以使用HAVING子句执行delete？
EN

回答 5

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问是否可以使用HAVING子句执行delete？EN

回答 5

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问是否可以使用HAVING子句执行delete？
EN