提问人：ExecChef 提问时间：4/14/2022 最后编辑：Thom AExecChef 更新时间：4/14/2022 访问量：72
使用 SUM 和分组依据的 SQL 联接
SQL Join with SUM and Group By
问：
我有一个 MVC5 C# 应用程序。我想添加一项功能，管理员可以查看每个停车场有多少停车位，以及当前占用的停车位与可用停车位的数量。
比方说，我有两张桌子（Lots 和 Staff）。
“地段”表是现有停车场的名称，以及每个地段的车位数量：
Name | Count
A | 200
B | 450
C | 375
“员工”表包含每个人的 ID，以及每个批次的 int 列。这些列为 1（员工被分配到此批次）或 0（员工未分配到此批次）
StaffID | LotA | Lot B | Lot C
7264 | 0 | 1 | 0
2266 | 0 | 0 | 1
3344 | 1 | 0 | 0
4444 | 0 | 1 | 0
在上述场景中，所需的输出将是 . . . .
Lot | Total | Used | Vacant
A | 200 | 1 | 199
B | 450 | 2 | 448
C | 375 | 1 | 374
过去，我只做过简单的连接，我正在努力弄清楚最好将 COUNT 与 Where 列 = 1 一起使用，还是使用 Sum。但我也会被这群人噎住，迷路了。
我尝试了以下类似的变体（没有成功）
SELECT Lots.Name,
Lots.Count,
SUM(Staff.LotA) As A_Occupied,
SUM(Staff.LotB) as B_Occupied,
SUM(Staff.LotC) as C_Occupied
FROM Lots
CROSS JOIN Staff
GROUP BY Lots.Name
从理论上讲，我预计这将产生一个地段列表，每个地段中有多少个空间，以及每个地段中占用了多少个空间，并按地段名称分组。
这会在“分组依据”中生成错误，因为我选择的列未包含在“分组依据”中，但这只会让我更加困惑如何根据需要进行分组依据。
总的来说，我有一个坏习惯，就是把事情弄得过于复杂，所以我提前道歉。我不是一个“受过传统训练的程序员”。我尽了最大努力组织问题并使其易于理解
答：
1赞
Stu
4/14/2022
#1
首先，如果对设计进行适当的规范化，您的设计会更好 - 您的表格应该有一列代表每个员工使用的批次（如果您有 100 个批次，您会有 100 列吗？这样就不需要将数据从列透视到所需的行。Staff
在当前架构中，可以使用交叉应用将列透视为行，然后可以将其与每个批次名称相关联：
select l.[name] as Lot, q.Qty as Used, l.count - q.Qty as Vacant
from lots l
cross apply (
select Sum(LotA) A, Sum(LotB) B, Sum(LotC) C
from Staff s
)s
cross apply (
select Qty from (
select * from (values ('A', s.A),('B', s.B),('C', s.C))v(Lot, Qty)
)x
where x.Lot = l.[name]
)q;
观看演示小提琴
评论
0赞
ExecChef
4/15/2022
我理解你关于“规范化表”的观点。请理解，这是一个 25 年前的申请，刚刚交给我 （FML）。而且由于它不是一个“关键”应用程序，因此目前尚未获得重写授权。也就是说 - 你的决议是完美的。对不起，我花了一天时间才完成它。我想确保我也理解它，而不仅仅是使用它。非常感谢您在这里的专业知识。我永远不会自己解决这个问题。
0赞
Stu
4/15/2022
是的，我知道 - 指出设计问题总是最好的，但我完全理解并且确实期望在大多数情况下这是不容易改变的，尤其是对于遗留系统。很高兴你有一个解决方案！
0赞
Florin
4/14/2022
#2
是一个外行的解决方案。希望对你有所帮助。
with parking as (
select 'A' as lot ,sum(case when lota >0 then 1 else 0 end ) as lots from staff
union all
select 'B' as lotb , sum(case when lotb >0 then 1 else 0 end ) as lots from staff
union all
select 'C' as lotc, sum(case when lotc >0 then 1 else 0 end ) as lots from staff
)
select a.name, a.count, b.lots, a.count-b.lots as places
from lots a join parking b on a.name=b.lot
评论