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

问题描述

是否有可能在一个语句中加入2个sql SELECT语句的结果？
我有一个任务数据库，其中每条记录是一个单独的任务，包含截止日期（和一个PALT，从开始到截止日期只是一个INT天数，年龄也是INT天数。）

我想要一个表格，它包含表中的每个人，他们拥有的任务数量以及他们拥有的LATE任务的数量（如果有的话）。

我可以很容易地在单独的表中获取这些数据，如下所示：

  SELECT ks ，COUNT（*）AS'#Tasks'FROM Table GROUP BY ks 
  

返回如下数据： p>

  ks＃任务
 person1 7 
 person2 3 
  

然后我有

  SELECT ks，COUNT（* ）AS'＃Late'FROM Table WHERE年龄> Palt GROUP BY ks 
  

返回：

 ks＃迟到
 person1 1 
 person2 1 
  

我想加入这两个select语句的结果（由KS）我试图避免使用临时表，但如果这是我想知道更多关于如何以这种方式使用临时表。

我也尝试做一些count（）＃满足条件的行，但我无法弄清楚如何做到这一点。

附录：
对不起，我希望我的结果有KS，Tasks和Late的列

如果可能的话，那也可以。

  KS＃任务＃迟到
 person1 7 1 
 person2 3 1 
 person3 2 0（或无效） 
  

另外，即使他们没有迟到任务，我也希望有人出现。

SUM（CASE当年龄> Palt THEN 1 ELSE 0 END）晚
效果很好，感谢这个答案！

两个select语句也可以工作，使用LEFT JOIN加入它们也很有效，现在我明白了如何以这种方式加入多个选择。谢谢！

解决方案

  select t1.ks，t1。[＃Tasks]，coalesce（ t2。[＃Late]，0）as [＃Late] 
 from 
（SELECT ks，COUNT（*）AS'＃Tasks'FROM表GROUP BY ks）t1 
 left join 
（选择ks，COUNT（*）AS'＃晚'从表WHERE年龄> Palt GROUP BY ks）t2 
关于
 t1.ks = t2.ks 
  




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

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

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

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) 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 selects in this fashion. Thanks!

解决方案

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

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