如何合并一个表中的两个选择，其中一个具有 GROUP BY，另一个具有 DISTINCT

Question

这两个单独工作

SELECT  PONumber, 
        count(RequestNumber) as "InNumOfRequests", 
        sum(Amount) as "SumofAmounts" 
FROM tableView
GROUP BY PONumber  

SELECT DISTINCT PONumber, 
                (10 * ceiling((DATEDIFF(day,POApprovedDate,GETDATE()))/10)) 
                  AS "BINofDaysSincePOApproved"                      
FROM tableView

我想最终得到：

PONumber   |  InNumOfRequests  |  SumofAmounts  |  BINofDaysSincePOApproved  
PO1        |  2                |  100           |  180
PO2        |  1                |  50            |  179   

tableView 看起来像：

RequestNumber | PONumber | Amount  |  POApproved Date   
1             | PO1      | 100.00  |  2010-01-01  
2             | PO1      | 100.00  |  2010-01-01  
3             | PO2      |  50.00  |  2010-01-02

请注意，PO1 实际上是 2 个请求的 PO，因此 POApproved 数据和金额对于这 2 个请求是相同的。

这看起来很简单，但从我正在使用的书（SQL 语言）中我无法弄清楚。
帮助:(
亚历克斯

Answer 1

SELECT *
   FROM (
         SELECT PONumber, count(RequestNumber) as "InNumOfRequests", sum(Amount) as "SumofAmounts"
           FROM tableView
          GROUP BY PONumber
        ) a,
        (
         SELECT DISTINCT PONumber, (10 * ceiling((DATEDIFF(day, POApprovedDate, GETDATE()))/10)) AS "BINofDaysSincePOApproved"
           FROM tableView
        ) b
 WHERE a.PONumber = b.PONumber

Answer 2

如果日期始终等于每个 PONumber，则可以放在第一个选择中。 MIN 函数 (MIN(POApprovedDate)) 是 group by,then 应该在字段中使用聚合函数的原因。

SELECT  PONumber, 
        count(RequestNumber) as "InNumOfRequests", 
        sum(Amount) as "SumofAmounts",
        (10 * ceiling((DATEDIFF(day,MIN(POApprovedDate),GETDATE()))/10)) 
         AS "BINofDaysSincePOApproved" 
FROM tableView
GROUP BY PONumber