SQL Server引用嵌套子查询
在一个sql服务器数据库中,我试图用奖金的最疯狂赚钱的方式返回给大家。因此,我有一个子查询来计算每个性别所做的奖金总和,另一个子查询返回最大值,最后,我的主查询返回性别与此最大值。但是我的代码不起作用。SQL Server引用嵌套子查询
SELECT E.Gender,maxx.mm
FROM HumanResources.Employee as E, (SELECT MAX(Bonus) as mm FROM (SELECT E.Gender, SUM(SP.Bonus) AS Bonus FROM HumanResources.Employee AS E, Sales.SalesPerson AS SP GROUP BY E.Gender) AS gen) AS maxx
其中E.Gender = gen.Gender;
最后一行:E.Gender = gen.Gender似乎不能工作,因为gen.Gender不能绑定
任何帮助,请
我不是一个大风扇subquery(subquery(subquery(
模型,因为它往往是浪费,通常很难解析/理解/重写。这里是我最初想重新写它,当我看到它:
SELECT TOP (1) E.Gender, mm = SUM(SP.Bonus)
FROM Sales.SalesPerson AS sp
INNER JOIN HumanResources.Employee AS E
ON E.EmployeeID = sp.EmployeeID -- guess on relationship
GROUP BY E.Gender
ORDER BY mm DESC;
另一种方式(可能更为高效,根据规划,因为你将消除所有,但一个员工从加入 - 这可能发生在上面,但也可能不会):
;WITH sp AS
(
SELECT TOP 1 EmployeeID, mm = SUM(Bonus)
FROM Sales.SalesPerson
GROUP BY EmployeeID
ORDER BY mm DESC
)
SELECT E.Gender, topsp.mm
FROM sp
INNER JOIN HumanResources.Employee AS E
ON E.EmployeeID = sp.EmployeeID; -- again guessing on relationship
我想有人不喜欢我的莫霍克。 – 2012-02-12 22:21:15
将你的过滤器放在子查询中。你可以参考外部查询从里面出来,所以:
AS gen) AS maxx
成为
AS gen WHERE E.Gender = gen.Gender) AS maxx
+1您还必须将'SELECT'列表从'E.Gender'更改为'maxx.Gender'。但我发现嵌套嵌套子查询模型很难解析和理解(因此我的替代建议:-))。 – 2012-02-12 20:51:40
你能告诉样本数据和预期的效果? 'Employee'和'SalesPerson'之间还有一个通用的'EmployeeID'列吗?现在你有一个糟糕的交叉连接(两个表,但没有'WHERE'子句)。 – 2012-02-12 20:44:44