需要根据条件在SQL Server 2008中进行帮助查询

I have a question about SQL Server

我有一个关于SQL Server的问题

Table: Emp

表：Emp

empid | empidref | Name
------+----------+------
 1    |  NULL    |  a
 3    |   1      |  b
 4    |   2      |  c
 2    | NULL     |  d
 5    | NULL     |  e
 6    | NULL     |  f
 8    |  7       |  g 
 7    | NULL     |  h
10    |NULL      |aa

I want to merge empidref values in empid when empid also same value. If empid values are available in empidref table then same empidref values consider as empid and corresponding empidref name need retrieve if empid values not available in empidref table then same empid values consider as empid and corresponding name need retrieve

我想在empid中合并empidref值，当empid也是相同的值时。如果在empidref表中有empid值，那么相同的empidref值视为empid，如果empidref表中没有empid值，则需要检索相应的empidref名称，然后将相同的empid值视为empid，并且需要检索相应的名称

Based on above table I want output like below

根据上表我想要输出如下

Empid  | Name
-------+------
1      | b
2      | c
5      | e
6      | f
7      |g
10     |aa

Please tell me how to write query to achieve this task in SQL Server

请告诉我如何在SQL Server中编写查询来实现此任务

4 个解决方案

#1

Hope this works for your requirement.

希望这适合您的要求。

SELECT
 E1.EmpId
,CASE WHEN E2.Name IS NOT NULL
        THEN E2.Name
      ELSE E1.Name
 END AS [Name]
FROM #Emp E1
LEFT OUTER JOIN #Emp E2 ON E1.EmpId = E2.EmpIdRef
WHERE E1.EmpIdRef IS NULL

#2

SELECT e1.empid, COALESCE(e2.Name, e1.Name)
FROM Emp e1 LEFT JOIN Emp e2
    ON e1.empid = e2.empidref
WHERE e1.empidref IS NULL

#3

As you say you can code like this:

正如你所说，你可以像这样编码：

SELECT empidref AS Empid , Name
FROM Emp
WHERE empidref IS NOT NULL
-- Select empidref where it is not null
UNION ALL
-- Merge it with
SELECT empid , Name
FROM Emp
WHERE empidref IS NULL 
-- Select empid where empidref is null
    AND empid NOT IN    (SELECT empidref 
                         FROM Emp 
                         WHERE empidref IS NOT NULL)
-- And empid is not in previous select

Another way is using a ROW_NUMBER() like this:

另一种方法是使用这样的ROW_NUMBER（）：

SELECT empid, Name
FROM (
    SELECT COALESCE(empidref, empid) as empid, Name, 
        ROW_NUMBER() OVER (PARTITION BY COALESCE(empidref, empid) ORDER BY empidref DESC) AS seq
    FROM Emp) t
WHERE seq = 1

In above query I said it COALESCE(empidref, empid) as when empidref is null then use empid and make a sequence column - from 1 - for each COALESCE(empidref, empid) that sorted descending over empidref to come null values after not null values then filter that sequence for 1 because I need just first not null value.

在上面的查询中我说COALESCE（empidref，empid）就像当empidref为null然后使用empid并为每个COALESCE（empidref，empid）创建一个序列列 - 从1开始 - 对empidref进行降序排序，在非空值之后变为空值然后将该序列过滤为1，因为我只需要首先不是空值。

#4

try this,

尝试这个，

select isnull(empidref,empid) as empid,name  
from emp  where 
empid not in (select empidref from emp  where empidref is not null)

#1