blocks|key|2994312|text|您可以尝试以下代码：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2994313|Select+*
From+Yourtablename
Where+Status_Id+in+(1,+7)
AND+Foreign_ID+=+301;|code-block|syntax|javascript|2994314|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|$]]

You can try the following code:

<pre><code>Select *
From Yourtablename
Where Status_Id in (1, 7)
AND Foreign_ID = 301;
</code></pre>

blocks|key|940814|text|使用not+exists：|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|940815|select+*+
from+table+t
where+not+exists+(
++++select+1+from+table+where+Foreign_ID+=+t.Foreign_ID+and+Status_Id+=+3
);|code-block|syntax|javascript|940816|这称为关联subquery，其中subquery依赖于外部查询，如果外部查询表有3行，则subquery将执行3次。|940817|在您的示例中，子查询具有filter+with+status_id+=+3，这意味着它将只选择status_id+=3的行，依此类推，外部查询具有NOT+EXISTS筛选器，这意味着它将选择status_id+<>+3的行。|940818|entityMap^0|2|A|0|0|5|8|G|8|18|8|0|O|D|21|A|2N|E|0^^$0|@$1|2|3|4|5|6|7|Q|8|@$9|R|A|S|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|T|8|@]|D|@]|E|$I|J]]|$1|K|3|L|5|6|7|U|8|@$9|V|A|W|B|C]|$9|X|A|Y|B|C]|$9|Z|A|10|B|C]]|D|@]|E|$]]|$1|M|3|N|5|6|7|11|8|@$9|12|A|13|B|C]|$9|14|A|15|B|C]|$9|16|A|17|B|C]]|D|@]|E|$]]|$1|O|3|-4|5|6|7|18|8|@]|D|@]|E|$]]]|P|$]]

Use <code>not exists</code> :

<pre><code>select * 
from table t
where not exists (
 select 1 from table where Foreign_ID = t.Foreign_ID and Status_Id = 3
);
</code></pre>

This is called correlated <code>subquery</code> where <code>subquery</code> is depended on outer query, if the outer query table has 3 rows then <code>subquery</code> will execute 3 times.

In your case, the subquery has filter with <code>status_id = 3</code> which means it will select only the rows where status_id = 3 and so forth the outer query has <code>NOT EXISTS</code> filter which means it will select the rows where <code>status_id &lt;&gt; 3</code>.

blocks|key|934023|text|我必须承认，也许我还没有完全理解你的需求，但这应该是可行的：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|934024|SELECT+*+FROM+table+WHERE+(Status_Id=1+OR+Status_Id=7)+AND+Foreign_ID=301|code-block|syntax|javascript|934025|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|$]]

I must admit that maybe I haven't fully understand your requirement,
 but this should work:

<pre><code>SELECT * FROM table WHERE (Status_Id=1 OR Status_Id=7) AND Foreign_ID=301
</code></pre>

blocks|key|4461757|text|抱歉，我还不能发表评论。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|4461758|如果我错了，请纠正我，你想查询status_id+1或7，然后在最后你说你只想得到foreign_id+301？|offset|length|style|CODE|4461759|SELECT+*+FROM+YOURTABLE+WHERE+Foreign_ID+=+301|code-block|syntax|javascript|4461760|这将显示具有Foreign_ID+=+301的行|4461761|如果只想显示Status_Id为1或7的Foreign_ID列|4461762|SELECT+Foreign_ID+FROM+YOURTABLE+WHERE+Status_Id+IN+(1,+7)|4461763|或者你的301+Foreign_ID也包含Status_Id+1？很抱歉，我丢失了您的语句末尾，以及您的表数据。|4461764|如果没有，Aurete的答案将满足您的条件。|4461765|entityMap^0|0|F|9|15|A|0|0|6|G|0|6|9|K|A|0|0|8|A|L|9|0|0^^$0|@$1|2|3|4|5|6|7|Y|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|Z|8|@$D|10|E|11|F|G]|$D|12|E|13|F|G]]|9|@]|A|$]]|$1|H|3|I|5|J|7|14|8|@]|9|@]|A|$K|L]]|$1|M|3|N|5|6|7|15|8|@$D|16|E|17|F|G]]|9|@]|A|$]]|$1|O|3|P|5|6|7|18|8|@$D|19|E|1A|F|G]|$D|1B|E|1C|F|G]]|9|@]|A|$]]|$1|Q|3|R|5|J|7|1D|8|@]|9|@]|A|$K|L]]|$1|S|3|T|5|6|7|1E|8|@$D|1F|E|1G|F|G]|$D|1H|E|1I|F|G]]|9|@]|A|$]]|$1|U|3|V|5|6|7|1J|8|@]|9|@]|A|$]]|$1|W|3|-4|5|6|7|1K|8|@]|9|@]|A|$]]]|X|$]]

Sorry I cant still post a comment.

Correct me if I'm wrong, you want to query <code>status_id</code> 1 or 7 then at the end you said you only want to get <code>foreign_id</code> 301?

<pre><code>SELECT * FROM YOURTABLE WHERE Foreign_ID = 301
</code></pre>

This will display rows having <code>Foreign_ID = 301</code> 

If you want to display only the <code>Foreign_ID</code> column with <code>Status_Id</code> 1 or 7

<pre><code>SELECT Foreign_ID FROM YOURTABLE WHERE Status_Id IN (1, 7)
</code></pre>

Or your 301 <code>Foreign_ID</code> also contains <code>Status_Id</code> 1? Sorry I'm lost at the end of your statement, and also your table data.

If not, Aurete's answer will satisfy your condition.

<pre><code>ID Foreign_ID Number Status_Id
1 101 xyz11 3
2 101 xyz22 3
3 201 xyz33 3
4 201 xyz44 1
5 201 xyz55 7
6 301 xyz66 1
7 301 xyz77 7
8 301 xyz88 7
</code></pre>

Status_Id, 3 refers to active, 1 refers cancelled, 7 refers to revoked
I want to select the rows that have only status_id 1 or 7 and should not have any active rows. The query should only return rows with 301 (Foreign_ID).

SQL to select rows with certain condition based on status and count

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

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

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

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

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

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

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

ID  Foreign_ID  Number   Status_Id1   101         xyz11    32   101         xyz22    33   201         xyz33    34   201         xyz44    15   201         xyz55 ...

问SQL根据状态和计数选择满足一定条件的行
EN

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问SQL根据状态和计数选择满足一定条件的行EN