复杂查询的CTE [英] CTE For a Complex Query
本文介绍了复杂查询的CTE的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有这个查询:
SELECT C.[First Name],
C.[Last Name],
SUM(Salary.Emp_Salary) AS 'Current Salary'
FROM
(
SELECT
Emp_First_Name AS "First Name",
Emp_Last_Name AS "Last Name",
MAX(Emp_Salary_Change_Year) AS "Change Year"
FROM Employee_Details AS e
INNER JOIN Country AS co ON e.Emp_Country_Id = co.Country_Id
INNER JOIN State AS s ON e.Emp_State_Id = s.State_Id
INNER JOIN Designation AS d ON e.Desig_Id = d.Desig_Id
INNER JOIN Salary AS sa ON e.Emp_Id = sa.Emp_Id
WHERE Emp_Active = 1
GROUP BY Emp_First_Name, Emp_Last_Name,
Emp_Middle_Name, Country_Name, Desig_Name) AS C
INNER JOIN Salary ON C.[Change Year] = Salary.Emp_Salary_Change_Year
group by C.[First Name],
C.[Last Name];
返回:
First Name Last Name Current Salary
Chen Chen 76000.00
Sahi King 156000.00
Venessa Katarina 185000.00
该查询如何使用CTE?
我知道可以使用WITH子句,但是由于查询很复杂,所以我无法使用它。我正在使用它作为参考: http: //www.codeproject.com/Articles/265371/Common-Table-Expressions-CTE-in-SQL-SERVER
,但仅包含简单示例。
How do I use a CTE for this query? I know to use the WITH Clause, but I can't get it to work because the query is complex. I'm using this for reference: http://www.codeproject.com/Articles/265371/Common-Table-Expressions-CTE-in-SQL-SERVER but it only contains simple examples.
谢谢。
推荐答案
非常简单;忽略了语法。
Very simple; overlooked the syntax.
WITH T([First Name], [Last Name], [Current Salary])
AS
(
SELECT C.[First Name],
C.[Last Name],
SUM(Salary.Emp_Salary) AS 'Current Salary'
FROM
(
SELECT
Emp_First_Name AS "First Name",
Emp_Last_Name AS "Last Name",
MAX(Emp_Salary_Change_Year) AS "Change Year"
FROM Employee_Details AS e
INNER JOIN Country AS co ON e.Emp_Country_Id = co.Country_Id
INNER JOIN State AS s ON e.Emp_State_Id = s.State_Id
INNER JOIN Designation AS d ON e.Desig_Id = d.Desig_Id
INNER JOIN Salary AS sa ON e.Emp_Id = sa.Emp_Id
WHERE Emp_Active = 1
GROUP BY Emp_First_Name, Emp_Last_Name,
Emp_Middle_Name, Country_Name, Desig_Name) AS C
INNER JOIN Salary ON C.[Change Year] = Salary.Emp_Salary_Change_Year
group by C.[First Name],
C.[Last Name]
)
SELECT * FROM T;
这篇关于复杂查询的CTE的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文