如何获取最后删除（多个）的emplyee ID并在单个查询中获取未删除（单个/多个）的最后一个emplyee ID？ [英] How to get last emplyee Ids which are deleted (multiple) and get last emplyee Ids which are not deleted (single/multiple) in a single query?

问题描述

如何获取被删除（多个）的最后一个emplyee ID并在单个查询中获取未删除（单个/多个）的最后一个emplyee ID？



我的代码如下：

How to get last emplyee Ids which are deleted (multiple) and get last emplyee Ids which are not deleted (single/multiple)in a single query ?

My code is as follows:

Select C3EmpRegId,EmpId,CitizenId,OldCardNo,OldAccountNo,NewCardNo,NewAccountNo
from
(SELECT top 1 C3EmpRegId,EmpId,CitizenId,CardNo as OldCardNo,AccountNo as OldAccountNo FROM EMPLOYEE
WHERE EmpId='556556y'  and CorporateId='IBM'  and SalaryAdvance=1 and IsDeleted=1

UNION

SELECT top 1 C3EmpRegId,EmpId,CitizenId,CardNo as NewCardNo,AccountNo as NewAccountNo FROM EMPLOYEE
WHERE EmpId='556556y'  and CorporateId='IBM'  and SalaryAdvance=1 and IsDeleted=0)
results
ORDER by C3EmpRegId desc

尝试如下，我得到了理想的结果（最后的数据w ith IsDeleted = 0）（单独查询）

On trying as below I get the desired result(last data with IsDeleted=0)(Separate Query)

SELECT top 1 C3EmpRegId,EmpId,CitizenId,CardNo as OldCardNo,AccountNo as OldAccountNo FROM EMPLOYEE
WHERE EmpId='556556y'  and CorporateId='IBM'  and SalaryAdvance=1 and IsDeleted=0
ORDER by C3EmpRegId desc

尝试如下我得到了所需的结果（使用IsDeleted的最后数据） = 1）（单独查询）

On trying as below I get the desired result(last data with IsDeleted=1)(Separate Query)

SELECT top 1 C3EmpRegId,EmpId,CitizenId,CardNo as OldCardNo,AccountNo as OldAccountNo FROM EMPLOYEE
WHERE EmpId='556556y'  and CorporateId='IBM'  and SalaryAdvance=1 and IsDeleted=1
ORDER by C3EmpRegId desc

推荐答案

首先，请阅读我对该问题的评论。



您正在寻找查询这样，可能是：

First of all, please read my comment to the question.

You're looking for query like that, probably:

SELECT C3EmpRegId, EmpId, CitizenId, CardNo, AccountNo, IsDeleted
FROM (
    SELECT ROW_NUMBER() OVER(PARTITION BY IsDeleted ORDER BY C3EmpRegId) AS RowNo, C3EmpRegId, EmpId, CitizenId, CardNo, AccountNo, IsDeleted
    FROM EMPLOYEE 
) AS A
WHERE RowNo = 1 AND EmpId='556556y'  and CorporateId='IBM'  and SalaryAdvance=1

有关详细信息，请参阅排名函数 [ ^ ]。



顺便说一下：

你不能使用不同的列名使用 UNION [ ^ ]！

For further information, please see ranking functions[^].

By The Way:
You can't use different column names with UNION[^]!

SELECT top 1 C3EmpRegId,EmpId,CitizenId,CardNo as OldCardNo, AccountNo as OldAccountNo FROM EMPLOYEE
...
UNION
SELECT top 1 C3EmpRegId,EmpId,CitizenId,CardNo as NewCardNo,AccountNo as NewAccountNo FROM EMPLOYEE
...

如果你想得到新旧的 CardNo 和 AccountNo ，你应该加入2个结果集如下：

If you would like to get old and new CardNo and AccountNo, youo should join 2 result sets as follow:

SELECT t1.C3EmpRegId, t1.EmpId, t1.CitizenId, t1.CardNo AS OldCardNo, t1.AccountNo AS OldAccountNo, t2.CardNo AS NewCardNo, t2.AccountNo AS NewAccountNo
FROM (
    SELECT *
    FROM Employee
    WHERE IsDeleted =1
) AS t1 INNER JOIN (
        SELECT C3EmpRegId, EmpId, CitizenId, CardNo, AccountNo
        FROM Employee
        WHERE AND IsDeleted =0
    ) AS t2 ON t1.EmpId = t2.EmpId
WHERE t1.EmpId='556556y' AND t1.CorporateId='IBM' AND t1.SalaryAdvance=1
ORDER BY t1.C3EmpRegId

注意：我没有知道你的数据库（表）的结构，所以上面的查询可能会错过你的需求。

Note: I don't know the structure of your database (tables), so above query can miss your needs.

这篇关于如何获取最后删除（多个）的emplyee ID并在单个查询中获取未删除（单个/多个）的最后一个emplyee ID？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！