我如何...在此查询中选择最后3个原始 [英] How do i...select last 3 raw in this query

问题描述

;WITH emp_hierarachy as(
SELECT EmployeeId as emp,EmployeeId, Manager,Firstname + ' ' + surname as name,Position  FROM tblEmployee
UNION ALL
SELECT rs.emp,t.EmployeeId,t.Manager, t.Firstname + ' ' + surname as name,t.Position FROM tblEmployee t 
INNER JOIN emp_hierarachy rs ON t.EmployeeId = rs.Manager
)

select row_number() OVER (ORDER BY(select 1)) id,rs.name,rs.Position from emp_hierarachy rs
where rs.emp = 179 

--and rs.EmployeeId != rs.emp 

order by id desc

What I have tried:

limit with order by but it is not working


give me soluiton

推荐答案

您是否尝试过 TOP ？结果是什么？

Have you experimented with TOP? What were the results?

SELECT TOP 3 Row_Number()...

已更新。如果您反转现有的ORDER，则可以实现最后3行。以下是基于此的完整查询：

Updated. The last 3 rows can be achieved if you reverse the ORDERing you have in place. Here is the full query based upon this:

;WITH emp_hierarachy AS(
	SELECT	emp = EmployeeId, EmployeeId, Manager, Firstname + ' ' + surname as [name], Position
	FROM		tblEmployee
		UNION ALL
	SELECT	rs.emp, t.EmployeeId, t.Manager, t.Firstname + ' ' + surname as [name], t.Position
	FROM		tblEmployee     t
	INNER JOIN emp_hierarachy rs ON t.EmployeeId = rs.Manager
)

	SELECT	TOP 3 id = Row_Number() OVER (ORDER BY (SELECT 1))
		,	rs.name
		,	rs.Position
	FROM		emp_hierarachy     rs
	WHERE	rs.emp = 179 
--	AND	rs.EmployeeId	!= rs.emp 
	ORDER BY ID -- DESC

注释/问题/问题

请注意名称字段用方括号括起来。名字是其中一个特定上下文特殊的词。我通常会避免使用它们。



在你注释掉的AND子句中，你正在使用！= for notquality 。虽然这在SQL Server中有效，但ANSI标准是使用<> ;所以这段代码的可移植性可能有限。



CTE似乎收集的数据超出了它的需要;只调用EmployeeID，[name]和position列，UNION ALL将添加重复记录。



我有兴趣看样本数据以及你期望得出的结果;因为我不知道这些表的架构，但我认为这可以大大减少代码

Notes/Questions/Questions
Please note that the name field is wrapped in square brackets. Name is one of them words that is certain contexts is special.I generally would avoid using them.

In your commented out AND clause, you are using != for inequality. While this is valid in SQL Server, the ANSI standard is to use <>; so the portability of this code may be limited.

The CTE seems to be gathering more data than it needs to; only the EmployeeID, [name] and position columns are called upon, and the UNION ALL is going to add in duplicate records.

I would be interested in seeing sample data and the results you expect to come out; as I do not know the schema of these tables but I think this could be greatly reduced codewise

这篇关于我如何...在此查询中选择最后3个原始的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！