如何在选择查询中以相反顺序选择特定列值并且必须保持相同,? [英] How to select particular column values in reverse order in a select query and remaining must has to be same ,?
本文介绍了如何在选择查询中以相反顺序选择特定列值并且必须保持相同,?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
例如:
选择empid,ename from employee
empid ename
============
1 a
2 b
3 c
4 d
我需要的输出如下:
empid ename
============
4 a
3 b
2 c
1 d
请帮助我吗?
解决方案
- 源表
创建 表测试(Id int identity ,EmpId Int ,EName nvarchar ( 100 ))
- 为源表插入记录
插入 进入测试值( 1 ,' a')
将 插入测试< span class =code-keyword> values ( 2 ,' b')
将 插入测试 values ( 3 ,' c')
将 插入测试值( 4 ,' d')
将 插入测试值( 5 ,' z')
- Proc for Result
Alter Proc GetResult
as
开始
声明 @ Tbl1 表(Id int 身份,Empid int )
声明 @ Tbl2 表(Id int 身份,Empnamenvarchar ( 100 ))
将 插入 @ Tbl1 选择 EmpID 来自测试订单 按 1 desc
插入 进入 @ Tbl2 选择 EName from 测试订单 按 1
选择 Empid,Empname 来自 @ Tbl1 T1, @ Tbl2 T2 其中 T1.Id = T2.Id
结束
试试这个:
WITH CTE0( row,empid,ename)
AS
(
选择 ROW_NUMBER () OVER ( order by empid < span class =code-keyword> desc ) AS 行,empid,ename 来自 employee
),
CTE1(row,empid,ename)
AS
(
选择 ROW_NUMBER() OVER ( order by ename asc ) as row,empid,ename 来自 employee
)
SELECT cte0.empid,cte1.ename FROM CTE0 join cte1 on cte0.row = cte1.row
For example :
select empid,ename from employee
empid ename
============
1 a
2 b
3 c
4 d
What I need output is as below :
empid ename
============
4 a
3 b
2 c
1 d
Please can any one help me ?
解决方案
-- Source Table Create Table Test(Id int identity,EmpId Int,EName nvarchar(100)) -- Insert Records for source Table Insert into Test values(1,'a') Insert into Test values(2,'b') Insert into Test values(3,'c') Insert into Test values(4,'d') Insert into Test values(5,'z') -- Proc for Result Alter Proc GetResult as Begin Declare @Tbl1 Table (Id int Identity,Empid int) Declare @Tbl2 Table (Id int Identity,Empname nvarchar(100)) Insert into @Tbl1 Select EmpID from Test Order by 1 desc Insert into @Tbl2 Select EName from Test Order by 1 Select Empid,Empname from @Tbl1 T1,@Tbl2 T2 where T1.Id=T2.Id End
Try this:
WITH CTE0 (row, empid, ename) AS ( select ROW_NUMBER() OVER(order by empid desc) AS row, empid, ename from employee ), CTE1 (row, empid, ename) AS ( select ROW_NUMBER() OVER(order by ename asc) as row, empid, ename from employee ) SELECT cte0.empid, cte1.ename FROM CTE0 join cte1 on cte0.row=cte1.row
这篇关于如何在选择查询中以相反顺序选择特定列值并且必须保持相同,?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文