SQL 查询限制具有不同值的行数

发布于 2024-10-15 12:34:38 字数 320 浏览 4 评论 0原文

SQL 中有没有一种方法可以使用与以下内容等效的查询：

select * from table1, table2 where some_join_condition
and some_other_condition and count(distinct(table1.id)) < some_number;

假设 table1 是员工表。然后，联接将导致有关单个员工的数据分布在多行中。我想将返回的不同员工的数量限制为某个数量。在这种情况下，行号或类似条件的条件是不够的。

那么，获得与上述查询预期相同的效果和相同输出的最佳方法是什么？

原文

Is there a way in SQL to use a query that is equivalent to the following:

select * from table1, table2 where some_join_condition
and some_other_condition and count(distinct(table1.id)) < some_number;

Let us say table1 is an employee table. Then a join will cause data about a single employee to be spread across multiple rows. I want to limit the number of distinct employees returned to some number. A condition on row number or something similar will not be sufficient in this case.

So what is the best way to get the same effect the same output as intended by the above query?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

亂 2024-10-22 12:34:38

select *
from (select * from employee where rownum < some_number and some_id_filter), table2
where some_join_condition and some_other_condition;

select *
from (select * from employee where rownum < some_number and some_id_filter), table2
where some_join_condition and some_other_condition;

回复收藏 0 原文

温柔戏命师 2024-10-22 12:34:38

这几乎适用于所有数据库，

SELECT * 
FROM table1 t1
INNER JOIN  table2 t2
ON some_join_condition
   AND some_other_condition 
INNER JOIN ( 
    SELECT t1.id
    FROM table1 t1
    HAVING 
        count(t1.ID) > someNumber
    ) on t1.id = t1.id

一些数据库具有特殊的语法，可以使这变得更容易。

This will work for nearly all DBs

SELECT * 
FROM table1 t1
INNER JOIN  table2 t2
ON some_join_condition
   AND some_other_condition 
INNER JOIN ( 
    SELECT t1.id
    FROM table1 t1
    HAVING 
        count(t1.ID) > someNumber
    ) on t1.id = t1.id

Some DBs have special syntax to make this a little bit eaiser.

回复收藏 0 原文