如何从多个表SQL中查询数据和信息范围？

Question

我有两个表--一个用于Jobs，另一个用于Connections。一个作业可以有多个连接。Connetion的PKs是connid + timestamp，它有一个外键( JOBID )指向Job的JOBID。

JOBID  | NAME
-------+-------
  id1  | Name of Job 1
  id2  | Job 2's Name

CONNID | TIMESTAMP | JOBID | AMOUNT
-------+-------+--------+----------
012    |  doesn't  |  id1  | 11
012    |  matter   |  id1  | 15
012    |           |  id1  | 20
012    |           |  id1  | 30
020    |           |  id1  | 2
020    |           |  id1  | 5
549    |           |  id2  | 19
549    |           |  id2  | 30
549    |           |  id2  | 50
549    |           |  id2  | 57
549    |           |  id2  | 60

因此，我想要做的是，从连接表中，获取每个作业的每个连接的范围总和。因此对于job-id1，我想要(30-11)+(5-2)，即22；对于job-id2，我想要(60-19)，即41。现在我可以计算出如何计算每个连接的最大值减去最小值。

SELECT 
   CONNID ,
   MAX(AMOUNT) - MIN(AMOUNT) AS range
FROM Connection 
GROUP BY CONNID 

...but我对sql还不够熟悉，不知道我还需要什么(就像我几年前在大学里选修的一门数据库课一样)。除此之外，我希望按作业的名称进行排序，因为我必须从Job表中获得它，所以我确信这会增加另一层复杂性。

基本上，我希望结果集看起来像这样

NAME          | range
--------------+------
Name of Job 1 |  22
Job 2's Name  |  41

最后，我只想在过去的24小时内做到这一点，但这应该只是在其中添加一个WHERE子句，它应该非常简单。

我希望的答案将包含一个查询，它只是一个带有JOBID的连接表查询(所以不必担心答案第一部分的作业名称)，然后是我实际查找的查询，它给出了上面的结果集，这样我就可以显式地看到实现该结果所需的额外步骤。

Answer 1

你的想法是正确的，你只需要走得更远。

第一个group by需要按连接和作业。这将为您提供每个作业的各个范围。然后，您希望在连接级别将它们组合在一起。您可以使用子查询来完成此操作：

select connid, SUM(range) as range
from (SELECT CONNID, jobid, MAX(AMOUNT) - MIN(AMOUNT) AS range
      FROM Connection 
      GROUP BY CONNID, jobid
     ) cj
group by connid

如果您想要一个作业的总时间并按作业名称进行排序，那么尝试如下所示：

select jobname, SUM(range) as range
from (SELECT CONNID, jobid, MAX(AMOUNT) - MIN(AMOUNT) AS range
      FROM Connection 
      GROUP BY CONNID, jobid
     ) cj join
     job j
     on j.jobid = cj.jobid
group by jobname

这将对连接中的作业求和，以获得范围，然后将其相加。

Answer 2

你已经很接近了。这是一个两步走的过程：

1. 获取每个CONNID+JOBID
2. Then的最大值/最小值获取每个

的每个CONNID+JOBID的最小值/最大值的总和

因此，第1步：

SELECT 
   CONNID, JOBID
   MAX(AMOUNT) - MIN(AMOUNT) AS Range1
FROM Connection 
GROUP BY CONNID, JOBID

然后，步骤2获取步骤1的结果，并进一步将其聚合

SELECT JOBID, SUM([Range]) AS [Range]
FROM
(
    SELECT 
       CONNID, JOBID
       MAX(AMOUNT) - MIN(AMOUNT) AS [Range]
    FROM Connection 
    GROUP BY CONNID, JOBID
) AS t0
GROUP BY JOBID

Answer 3

如果您想将其分解以便于理解，我使用一个临时表对其进行小计

    SELECT 
       JOBID
       , CONNID 
       , MAX(AMOUNT) - MIN(AMOUNT) AS range
       into #subtotal
    FROM Connection 
    --Where timestamp between "low" and "high"
    GROUP BY JOBID, CONNID 

    select * from #subtotal

    Select job.Name
           , sum(range) as range 
    from #subtotal inner join job on job.jobid = #subtotal.jobid 
    group by job.Name

    drop table #subtotal