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
0
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
0
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
0
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
0
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
0
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
0
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
0
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
0
try this,
尝试这个,
select isnull(empidref,empid) as empid,name
from emp where
empid not in (select empidref from emp where empidref is not null)