SQL查询来获取结果

问题描述：

CutomerID Salary 
1    2000    
2    3000    
3    4000  
4    6000  
5    5220

表2

DeptID DeptNam 
    1  IT       
    2  Finance     
    3  Marketing

表3

CustomerID DeptID 
    1   1 
    2   1 
    3   2 
    4   3 
    5   3

问：找到人的智慧的H各部门薪水最高

返回行：CustomerID DeptName Salary

问：找到人最高年薪在每个部门

返回行：CustomerID DeptName Salary

任何人可以帮助我如何为此写入SQL查询？

气味功课。我错了吗？ – Steve 2012-07-27 20:49:33

对不起只有SQL ...我只是尽我最大的努力学习像这样得到不同asnwers .. – user957178 2012-07-27 20:51:29

请提供一个[sqlfiddle]（http://sqlfiddle.com）的例子， – 2012-07-27 20:52:43

答

对于SQL Server 2005+：

;WITH CTE AS 
(
    SELECT T1.*, T3.DeptID, RANK() OVER(PARTITION BY T3.DeptID ORDER BY T1.Salary DESC) RN 
    FROM Table1 AS T1 
    INNER JOIN Table3 AS T3 
    ON T1.CustomerId = T3.CustomerId 
) 
SELECT A.CustomerId, B.DeptName, A.Salary 
FROM CTE A 
INNER JOIN Table2 B 
ON A.DeptID = B.DeptID 
WHERE RN = 1

你应该考虑到，如果每个部门的最高薪水是重复的，那么这个查询将返回每个部门不止一个结果。

答

从SQL-Server 2005和超越，使用CTE与ROW_NUMBER功能：

查找与薪水最高的人在各部门

WITH CTE AS 
(
    SELECT t1.CustomerID ,t2.DeptName, t1.Salary , SalaryRank = 
     ROW_NUMBER()OVER(PARTITION BY t2.DeptID ORDER BY t1.Salary DESC) 
    FROM Table1 t1 
    INNER JOIN Table3 t3 ON t1.CustomerID=t3.CustomerID 
    INNER JOIN Table2 t2 ON t3.DeptID=t2.DeptID 
) 
SELECT CustomerID, DeptName, Salary FROM CTE 
WHERE SalaryRank = 1

这里的SQL-小提琴：http://sqlfiddle.com/#!3/41ff1/2/0

如果你想要返回多个客户，如果他们有如同@Lamak建议的那样，使用RANK而不是ROW_NUMBER。

差别如下：http://mangalpardeshi.blogspot.de/2008/10/difference-between-rownumber-rank-and.html

答

SELECT a.customerid, 
     c.deptnam, 
     a.salary 
FROM table1 a 
JOIN table3 b ON a.customerid = b.customerid 
JOIN table2 c ON b.deptid = c.deptid 
JOIN 
(
    SELECT aa.deptid, MAX(bb.salary) AS maxsalary 
    FROM  table3 aa 
    JOIN  table1 bb ON aa.customerid = bb.customerid 
    GROUP BY aa.deptid 
) d ON a.salary = d.maxsalary AND c.deptid = d.deptid

答

试试这个：

CREATE TABLE cust(CutomerID int,Salary float) 

INSERT INTO cust 
VALUES(1,2000),(2,3000),(3,4000),(4,6000),(5,5220) 

CREATE TABLE dept(DeptID int,DeptNam varchar(10)) 

INSERT INTO dept 
VALUES(1,'IT'),(2,'Finance'),(3,'Marketing') 

CREATE TABLE custDept(CustomerID int,DeptID int) 

INSERT INTO custDept 
VALUES(1,1),(2,1),(3,2),(4,3),(5,3) 

select a.CutomerID,a.DeptNam,b.sal from 

(select c.CutomerID ,d.DeptID,c.Salary,d.DeptNam from cust c inner join custDept cd on 
c.CutomerID = cd.CustomerID 
inner join dept d 
on cd.DeptID =d.DeptID) a 

inner join 
(select d.DeptID ,MAX(c.Salary) as sal from cust c inner join custDept cd on 
c.CutomerID = cd.CustomerID 
inner join dept d 
on cd.DeptID =d.DeptID 
group by d.DeptID) b 

on a.DeptID = b.DeptID and a.Salary = b.sal

SQL查询来获取结果

相关推荐