blocks|key|3333982|text|您已经完成了，只需将两个查询放在一起：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3333983|SELECT+tbl1.ATID,+tbl1.ATDesc,+tbl2.AValue,+tbl2.ADesc,+tbl2.APosNeg
FROM+tbl1+LEFT+OUTER+JOIN+tbl2+ON+(tb1.ATID+=+tbl2.ATID)
WHERE+tbl1.ATID+NOT+IN+(
++select+ATID
++from+tbl2
++group+by+ATID
++HAVING+COUNT(*)+>=+100
)|code-block|syntax|javascript|3333984|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've already done it, you just need to put your two queries together:

<pre><code>SELECT tbl1.ATID, tbl1.ATDesc, tbl2.AValue, tbl2.ADesc, tbl2.APosNeg
FROM tbl1 LEFT OUTER JOIN tbl2 ON (tb1.ATID = tbl2.ATID)
WHERE tbl1.ATID NOT IN (
 select ATID
 from tbl2
 group by ATID
 HAVING COUNT(*) &gt;= 100
)
</code></pre>

blocks|key|1928468|text|这里有一种方法可以使用|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1928469|Count()+Over()窗口聚合将count表中每个ATID的记录数。然后计算可用于过滤ATID的Join条件。|offset|length|style|CODE|1928470|SELECT+tbl1.ATID,
+++++++tbl1.ATDesc,
+++++++tbl2.AValue,
+++++++tbl2.ADesc,
+++++++tbl2.APosNeg
FROM+++tbl1
+++++++LEFT+OUTER+JOIN+(SELECT+Count(1)OVER(partition+BY+ATID)+AS+cnt,*
++++++++++++++++++++++++FROM+++tbl2)+tbl2
++++++++++++++++++++ON+tb1.ATID+=+tbl2.ATID
+++++++++++++++++++++++AND+tbl2.cnt+<=+100;+|code-block|syntax|javascript|1928471|注释:，如果您不想要tabl1中的所有ATID's，那么将tbl2.cnt+<=+100条件移到Where子句中，这样就可以得到计数小于100+in+tbl2的ATID's计数。|BOLD|1928472|entityMap^0|0|0|E|J|5|S|4|1A|4|1F|4|0|0|0|3|A|5|J|6|T|F|1C|5|1W|3|23|4|28|6|0^^$0|@$1|2|3|4|5|6|7|R|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|S|8|@$D|T|E|U|F|G]|$D|V|E|W|F|G]|$D|X|E|Y|F|G]|$D|Z|E|10|F|G]|$D|11|E|12|F|G]]|9|@]|A|$]]|$1|H|3|I|5|J|7|13|8|@]|9|@]|A|$K|L]]|$1|M|3|N|5|6|7|14|8|@$D|15|E|16|F|O]|$D|17|E|18|F|G]|$D|19|E|1A|F|G]|$D|1B|E|1C|F|G]|$D|1D|E|1E|F|G]|$D|1F|E|1G|F|G]|$D|1H|E|1I|F|G]|$D|1J|E|1K|F|G]]|9|@]|A|$]]|$1|P|3|-4|5|6|7|1L|8|@]|9|@]|A|$]]]|Q|$]]

Here is one way to do it using 

<code>Count() Over()</code> window aggregate will <code>count</code> the number of records for each <code>ATID</code> in <code>tbl2</code> table. Then count the can be used to filter the <code>ATID</code> in <code>Join</code> condition

<pre><code>SELECT tbl1.ATID,
 tbl1.ATDesc,
 tbl2.AValue,
 tbl2.ADesc,
 tbl2.APosNeg
FROM tbl1
 LEFT OUTER JOIN (SELECT Count(1)OVER(partition BY ATID) AS cnt,*
 FROM tbl2) tbl2
 ON tb1.ATID = tbl2.ATID
 AND tbl2.cnt &lt;= 100; 
</code></pre>

Note : If you dont want all the <code>ATID's</code> from <code>tabl1</code> then move the <code>tbl2.cnt &lt;= 100</code> condition to <code>Where</code> clause so you will get the count of <code>ATID's</code> whose count is less than <code>100</code> in <code>tbl2</code>

blocks|key|3334045|text|对于SQL服务器，使用CTE+(比子查询更快)。此外，查询中不必要的括号|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3334046|with+RowCo+as
(
select+ATID,+count(*)+as+row_count
from+tbl2
group+by+ATID
having+row_count+<=+100
)

SELECT+tbl1.ATID,+tbl1.ATDesc,+tbl2.AValue,+tbl2.ADesc,+tbl2.APosNeg
FROM+tbl1+
inner+join+RowCo
++++on+RowCo.ATID+=+tbl1.ATID
LEFT+JOIN+tbl2+
++++ON+tb1.ATID+=+tbl2.ATID|code-block|syntax|javascript|3334047|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|$]]

For SQL server, use a CTE (faster than a sub-query). Also, unnecessary brackets in your query

<pre><code>with RowCo as
(
select ATID, count(*) as row_count
from tbl2
group by ATID
having row_count &lt;= 100
)

SELECT tbl1.ATID, tbl1.ATDesc, tbl2.AValue, tbl2.ADesc, tbl2.APosNeg
FROM tbl1 
inner join RowCo
 on RowCo.ATID = tbl1.ATID
LEFT JOIN tbl2 
 ON tb1.ATID = tbl2.ATID
</code></pre>

blocks|key|3334060|text|对于第二个查询，与聚合函数相关的筛选器将继续使用“已有”部分。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3334061|select+ATID,+count(*)+as+row_count
from+tbl2
group+by+ATID
order+by+row_count+desc;
having+count(*)+<+100|code-block|syntax|javascript|3334062|对于第一个查询，只需使用子查询即可。|3334063|SELECT+tbl1.ATID,+tbl1.ATDesc,+tbl2.AValue,+tbl2.ADesc,+tbl2.APosNeg
FROM+tbl1+
+++++LEFT+OUTER+JOIN+tbl2+ON+(tb1.ATID+=+tbl2.ATID)
WHERE+tbl1.ATID+in+(select+ATID
++++++++++++++++++++from+tbl2
++++++++++++++++++++group+by+ATID
++++++++++++++++++++having+count(*)+<+100)|3334064|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|N|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|O|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

For the second query, the filters related to aggregate functions goes on a HAVING section 

<pre><code>select ATID, count(*) as row_count
from tbl2
group by ATID
order by row_count desc;
having count(*) &lt; 100
</code></pre>

And for the first query you can simply use a subquery 

<pre><code>SELECT tbl1.ATID, tbl1.ATDesc, tbl2.AValue, tbl2.ADesc, tbl2.APosNeg
FROM tbl1 
 LEFT OUTER JOIN tbl2 ON (tb1.ATID = tbl2.ATID)
WHERE tbl1.ATID in (select ATID
 from tbl2
 group by ATID
 having count(*) &lt; 100)
</code></pre>

I was extracting some data earlier and found my results too numerous to be of any use (over 4 million rows returned). I found the culprit to be a large handful of IDs that have several thousand records apiece.

Heres the structure of the data. I have two tables. tbl1 has a single record of every 'ATID' (an ATID being a scale id, basically). Each ATID is in essence its own table, though - it has a record for every item on the scale. tbl2 enumerates those items - it has a row for every scale item per ATID. So it has anywhere from 1 to 500,000 rows for each ATID (I know, pretty f'ing excessive). 

I'd like to exclude the ATIDs which have over 100 rows. FYI this is a database I have read-only access to.

Here's my original query:

<pre><code>SELECT tbl1.ATID, tbl1.ATDesc, tbl2.AValue, tbl2.ADesc, tbl2.APosNeg
FROM tbl1 LEFT OUTER JOIN tbl2 ON (tb1.ATID = tbl2.ATID);
</code></pre>

Here's the query I used to get a list of ATIDs and record counts in tbl2 per ATID:

<pre><code>select ATID, count(*) as row_count
from tbl2
group by ATID
order by row_count desc;
</code></pre>

For my end product I'd like to return only ATIDs with fewer than 100 rows in tbl2. However, if I even try to add a WHERE clause to the second query using the row_count variable, it fails. So I have no idea how to even approach combining the two queries and getting the WHERE clause in.

The only alternative I have is to exclude ATIDs specifically when I know they have too many records, but there are too many to exclude (ie, using WHERE NOT ATID = 1016 AND NOT ATID = 554 AND NOT .... etc)

Exclude rows from a SQL query based on number of rows an ID is present in

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

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

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

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

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

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

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

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋 

腾讯云代码助手

CODING DevOps

Cloud Studio

SDK中心

API中心

命令行工具

我之前提取了一些数据，发现我的结果太多了，没有任何用处(返回了400多万行)。我发现罪魁祸首是大量的I，每个I都有几千条记录。这是数据的结构。我有两张桌子。tbl1有每个' ATID‘的单一记录(基本上，ATID是一个刻度id )。不过，每个ATID在本质上都是它自己的桌子--它对每个规模的项目都有记录。tbl2枚举这...

问根据ID存在的行数从SQL查询中排除行
EN

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问根据ID存在的行数从SQL查询中排除行EN