JOIN两个SELECT语句结果 - JOIN two SELECT statement results

10/14 14:27
阅读数 0

问题:

Is it possible to join the results of 2 sql SELECT statements in one statement?是否可以在一个语句中加入 2 个 sql SELECT语句的结果? I have a database of tasks where each record is a separate task, with deadlines (and a PALT , which is just an INT of days from start to deadline. Age is also an INT number of days.)我有任务的数据库,其中每个记录是一个单独的任务,最后期限(和PALT ,这仅仅是一个INT从开始到最后期限的天数。 Age也是一个INT天数)。

I want to have a table which has each person in the table, the number of tasks they have, and the number of LATE tasks they have (if any.)我想要一个表,其中包含表中的每个人、他们拥有的任务数以及他们拥有的LATE任务数(如果有)。

I can get this data in separate tables easily, like so:我可以轻松地在单独的表中获取这些数据,如下所示:

SELECT ks, COUNT(*) AS '# Tasks' FROM Table GROUP BY ks

returning data like:返回数据,如:

ks        # Tasks
person1   7
person2   3

and then I have:然后我有:

SELECT ks, COUNT(*) AS '# Late' FROM Table WHERE Age > Palt GROUP BY ks

which returns:返回:

ks        # Late
person1   1
person2   1

And I want to join the results of these two select statements (by the KS )我想加入这两个select语句的结果(由KS

I'm trying to avoid using a temp table, but if that's the only practical way to do this, I'd like to know more about using temp tables in this fashion.我试图避免使用临时表,但如果这是唯一可行的方法,我想了解更多有关以这种方式使用临时表的信息。

I also tried to do some kind of count() of rows which satisfy a conditional, but I couldn't figure out how to do that either.我还尝试对满足条件的行进行某种count() ,但我也不知道如何做到这一点。 If it's possible, that would work too.如果可能,那也行。

Addendum: Sorry, I want my results to have columns for KS , Tasks , and Late附录:抱歉,我希望我的结果包含KSTasksLate

KS        # Tasks   # Late
person1   7         1
person2   3         1
person3   2         0  (or null)

Additionally, I want a person to show up even if they have no late tasks.此外,我希望一个人即使没有迟到的任务也能出现。

SUM(CASE WHEN Age > Palt THEN 1 ELSE 0 END) Late
works well, thanks for this answer!效果很好,感谢您的回答!

Two select statements also work, using a LEFT JOIN to join them also works, and I understand now how to join multiple select s in this fashion两个 select 语句也有效,使用LEFT JOIN加入它们也有效,我现在明白如何以这种方式加入多个select


解决方案:

参考一: https://en.stackoom.com/question/iDYR
参考二: https://stackoom.com/question/iDYR
展开阅读全文
打赏
0
0 收藏
分享
加载中
更多评论
打赏
0 评论
0 收藏
0
分享
返回顶部
顶部