T-SQL组By和Sub查询

I am aware of what the problem is with my query, but am really struggling to find a solution here.

我知道我的查询有什么问题，但我真的很难在这里找到解决方案。

SQL小提琴

I guess I'm not even really sure how to ask this. What I'm trying to achieve sum all tracking numbers for a date range grouped by branch, but (and this is the kicker) include any other records in the sum that have the same tracking number. I thought of doing something like this, but of course SQL Server doesn't like this because I can't have a subquery in an aggregate function.

我想我都不知道该怎么问。我想要实现的是将所有的跟踪号码按分支分组的日期范围相加，但是(这里是提示符)将所有其他记录包含在具有相同跟踪号码的和中。我想做这样的事情，但是SQL Server当然不喜欢这样，因为聚合函数中不能有子查询。

MAX((select SUM(demo.NegotiatedRate) where #demo.Tracking = demo2.Tracking)) as NegotiatedRate

马克斯(选择总和(demo.NegotiatedRate)#演示。跟踪=降级，降级

Here is the query I have so far if anyone doesnt want to click the SQL Fiddle link

这是我到目前为止的查询，如果有人不想点击SQL Fiddle链接的话

select     demo.Branch, 
           SUM(demo.NegotiatedRate) as NegotiatedRate,
           SUM(demo2.BillRate) as BillRate
from       demo
join       demo2 on demo2.Tracking = demo.Tracking
where      demo.ShipDate = '2014-05-01'
group by   demo.Branch

Expected Output

预期的输出

The output that I am trying to achieve would look something like this. The GH6 negotiated rate and bill rate should match even though one of the GH6 entries falls outside of desired date range.

我要实现的输出是这样的。尽管GH6条目中的一个超出了期望的日期范围，但是GH6协商的速率和账单速率应该匹配。

Branch    NegotiatedRate    BillRate
GH4       50                50
GH6       25                25

3 个解决方案

#1

You can pre-project the overall (non date-range bound, unfiltered) totals in a separate derived table or cte and then join back to it:

您可以在单独的派生表或cte中预先计划总体(非日期范围绑定、未过滤)总量，然后加入到它:

WITH totals AS
(
  SELECT demo.Tracking,
     SUM(demo.NegotiatedRate) as NegotiatedRate
  from       demo
  group by   demo.Tracking
)
select     demo.Branch, 
           MIN(totals.NegotiatedRate) as NegotiatedRate,
           SUM(demo2.BillRate) as BillRate
from       demo
join       demo2 on demo2.Tracking = demo.Tracking
join       totals on totals.Tracking = demo.Tracking
where      demo.ShipDate = '2014-05-01'
group by   demo.Branch;

SqlFiddle here

SqlFiddle这里

Given that there should only be one NegotiatedRate per tracking, you can circumvent the need to add the summed totals.NegotiatedRate to the outer query by applying an aggregate (I've used MIN), although this is just to pacify Sql.

考虑到每个跟踪应该只有一个流水帐，您可以绕过添加合计总数的需要。通过应用聚合(我使用了MIN)对外部查询进行协商，尽管这只是为了平息Sql。

#2

As a bit of a simpler answer, you can do something like:

作为一个简单的答案，你可以这样做:

SELECT     demo.Branch, 
           SUM(demo.NegotiatedRate) AS NegotiatedRate,
           demo2.BillRate
FROM       demo
JOIN       demo2 on demo2.Tracking = demo.Tracking
WHERE      demo.Tracking IN
           (
               SELECT Tracking
               FROM demo
               WHERE ShipDate = '2014-05-01'
           )
GROUP BY   demo.Branch, demo2.BillRate

As I understand it, you get all the tracking numbers you want in a certain date range, then get all the information from those tracking numbers, no matter the date range. Then groups them by the Branch and BillRate, both of which should be one value for each tracking number.

据我所知，你得到所有你想要的跟踪号码在一个特定的日期范围内，然后从这些跟踪号码中得到所有的信息，无论日期范围是什么。然后根据分支和BillRate对它们进行分组，这两个值都应该是每个跟踪号的一个值。

#3

If I understand correctly, you want to include all rows when one of the dates is the selected date. If this is correct, then you want the logic in the having clause, not in the where clause:

如果我理解正确，您想要包含所有的行，当其中一个日期是选定的日期。如果这是正确的，那么你想要的逻辑在have子句，而不是where子句:

select     demo.Branch, 
           SUM(demo.NegotiatedRate) as NegotiatedRate,
           SUM(demo2.BillRate) as BillRate
from       demo
join       demo2 on demo2.Tracking = demo.Tracking
group by   demo.Branch
having sum(case when demo.ShipDate = '2014-05-01' then 1 else 0 end) > 0

#1