JOIN两个SELECT语句结果 [英] JOIN two SELECT statement results

问题描述

是否可以将 2 个 sql SELECT 语句的结果合并到一个语句中?我有一个任务数据库，其中每条记录都是一个单独的任务，有截止日期(还有一个 PALT，它只是从开始到截止日期的 INT 天.Age 也是一个 INT 天数.)

Is it possible to join the results of 2 sql SELECT statements in one statement? 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.)

我想要一个表格，其中包含表格中的每个人、他们拥有的任务数量以及他们拥有的LATE 任务数量(如果有).

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.)

我可以轻松地在单独的表中获取这些数据，如下所示:

I can get this data in separate tables easily, like so:

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

返回如下数据:

ks        # Tasks
person1   7
person2   3

然后我有:

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

返回:

ks        # Late
person1   1
person2   1

而且我想加入这两个select语句的结果(通过KS)

And I want to join the results of these two select statements (by the 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.

我还尝试对满足条件的行进行某种 count() 处理，但我也不知道该怎么做.如果可能，那也行.

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. If it's possible, that would work too.

附录:抱歉，我希望我的结果包含 KS、Tasks 和 Late

Addendum: Sorry, I want my results to have columns for KS, Tasks, and Late

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) 晚
效果很好，感谢您的回答！

SUM(CASE WHEN Age > Palt THEN 1 ELSE 0 END) Late
works well, thanks for this answer!

两个 select 语句也可以，使用 LEFT JOIN 加入它们也可以，我现在明白如何以这种方式加入多个 select

Two select statements also work, using a LEFT JOIN to join them also works, and I understand now how to join multiple selects in this fashion

推荐答案

SELECT t1.ks, t1.[# Tasks], COALESCE(t2.[# Late], 0) AS [# Late]
FROM 
    (SELECT ks, COUNT(*) AS '# Tasks' FROM Table GROUP BY ks) t1
LEFT JOIN
    (SELECT ks, COUNT(*) AS '# Late' FROM Table WHERE Age > Palt GROUP BY ks) t2
ON (t1.ks = t2.ks);

这篇关于JOIN两个SELECT语句结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！