SQL语法:仅当结果超过X个时才选择 [英] SQL syntax: select only if more than X results
问题描述
我有一个带有度量的表,称为 measures .该表在 location 中有一列,而在对应的 value 中有第二列(简化了示例).
I have a table with measurements called measures. The table has one column for the location and a second colum for a corresponding value (example is simplified).
该表格如下所示(请注意 loc1 的2个条目):
The table looks like (note 2 entries for loc1):
location | value
-----------------
loc1 | value1
loc1 | value2
loc2 | value3
loc3 | value4
loc4 | value5
我现在想制定一个SQL查询(实际上我使用sqlite),该查询仅返回表的前两行(即loc + value1和loc1 + value2),因为此位置在该表中有多个条目.
i now want to formulate a SQL query (actually i use sqlite) which only returns the first two rows of the table (i.e. loc+value1 and loc1+value2), because this location has more than one entry in this table.
伪文本表述为:向我显示位置的行,这些行在整个表格中多次出现
密码:
the pseudotext formulation would be: show me the rows of the locations, which are present more than once in the whole table
pseudcode:
SELECT * from measures WHERE COUNT(location over the whole table) > 1
该解决方案可能真的很简单,但是我似乎并没有因此而崩溃.
the solution may be really simple, but somehow i seem not to crack the nut.
到目前为止,我所拥有的是一条SELECT语句,该语句返回具有多个条目的位置.下一步,我将需要与该查询返回的位置完全对应的所有行:
what i have so far is a SELECT statement, which returns locations which have more than one entry. as a next step i would need exactly all rows which correspond to the locations returned from this query:
SELECT location FROM measures GROUP BY location HAVING count(*) > 1
因此,下一步我尝试对同一张表进行JOIN并合并上面的查询,但是结果不正确.我这样尝试过,但这是错误的:
so as a next step i tried to do a JOIN with the same table and incorporate above query, but the results are incorrect. i tried it like this, but this is wrong:
select t1.location, t1.value
from
measures as t1
join
measures as t2 on t1.location = t2.location
group by
t2.location
having count(*) > 1
我们非常感谢您的帮助!
help is appreciated!
推荐答案
您正确使用HAVING
,并考虑使用自联接...只是操作顺序略有不同...
You were right to use HAVING
, and to think about using the self-join... just had the order of the operations slightly off...
select m1.location, m1.value
from measures m1
join (
select location
from measures
group by location
having count(*) > 1
) m2 on m2.location = m1.location
子选择将获得具有多个条目的所有位置...,然后再次将其连接到表以获取完整结果.
The sub-select gets all the locations that have more than one entry... and then this is joined to the table again to get the full results.
这篇关于SQL语法:仅当结果超过X个时才选择的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!