如何编写可以从4个表中的任何一个表中得到结果的SQL Server查询

I have 4 tables (say emp1, emp2, emp3, emp4) with nearly identical columns. I want to fetch details

我有4个表(比如emp1,emp2,emp3,emp4),几乎相同的列。我想要获取详细信息

(select empid, empname from emp1 where empid = '1' )
UNION
(select empid, empname from emp2 where empid = '1')
UNION
(select empid, empname from emp3 where empid = '1')
UNION
(select empid, empname from emp4 where empid = '1')

The thing is if I got result from first query (emp1) it should ignore queries below (emp2, emp3, emp4). If I get a result from emp2, it should ignore (emp3, emp4) and so on.

问题是,如果我从第一个查询(emp1)得到结果,它应该忽略下面的查询(emp2,emp3,emp4)。如果我从emp2得到一个结果,它应该忽略(emp3,emp4)等等。

Remember in emp1, emp2, emp3, emp4 there an be different empname associated with same empid. That's why union giving all the results. In that case I have to prefer result from uppermost table i.e emp1 > emp2 > emp3. I also tried using 'case' but things are not working for me.

记住在emp1,emp2,emp3,emp4中有一个与同一个empid相关联的不同empname。这就是工会给出所有结果的原因。在那种情况下,我必须更喜欢最上层表的结果,即emp1> emp2> emp3。我也试过使用'case',但事情对我不起作用。

Sample data

emp1

1    deepak

emp2

1    nitin

emp3

1    sateesh

emp4

1    chandra

and expected result is

和预期的结果是

1 deepak

I hope I am clear to you. please help me thank you

我希望我对你很清楚。请帮帮我谢谢

3 个解决方案

#1

You can add an arbitrary column to specify the priority.

您可以添加任意列以指定优先级。

SQL Fiddle

;WITH Cte AS(
    SELECT *, N = 1 FROM emp1 WHERE empId = 1 UNION ALL
    SELECT *, N = 2 FROM emp2 WHERE empId = 1 UNION ALL
    SELECT *, N = 3 FROM emp3 WHERE empId = 1 UNION ALL
    SELECT *, N = 4 FROM emp4 WHERE empId = 1
)
,CteRN AS(
    SELECT *, RN = ROW_NUMBER() OVER(ORDER BY N) FROM Cte
)
SELECT 
    empId, empName
FROM CteRN
WHERE RN = 1

Basically, you want to prioritize results from emp1, then emp2 and so on. This is where the arbitrary column N comes in. You want to rank them in order of priority. The result of the first CTE is:

基本上,您希望优先考虑emp1,然后是emp2等结果。这是任意列N的来源。您希望按优先级顺序对它们进行排名。第一次CTE的结果是:

empId       empName    N
----------- ---------- -----------
1           deepak     1
1           nitin      2
1           sateesh    3
1           chandra    4

Then you use ROW_NUMBER to add a sequential number to each rows. The second CTE, CteRN will give you:

然后使用ROW_NUMBER为每行添加序号。第二个CTE,CteRN会给你:

empId       empName    N           RN
----------- ---------- ----------- --------
1           deepak     1           1
1           nitin      2           2
1           sateesh    3           3
1           chandra    4           4

Laslt, you only want the row with the least RN, so you add a WHERE RN = 1 clause. The final result would be:

Laslt,你只想要RN最少的行,所以你添加一个WHERE RN = 1子句。最终结果将是:

empId       empName
----------- ----------
1           deepak

Additionally, you can add a PARTITION BY empId on RN = ROW_NUMBER() OVER(ORDER BY N)

此外,您可以在RN = ROW_NUMBER()OVER(ORDER BY N)上添加PARTITION BY empId

#2

Based on your updated question, since empid should not be repeated from other tables, you can try something like this.

根据您更新的问题,因为empid不应该从其他表重复,您可以尝试这样的事情。

select empid,empname 
from emp1
UNION ALL

select empid,empname 
from emp2 e 
WHERE NOT EXISTS( SELECT empid FROM emp1 ee WHERE ee.empid = e.empid)
UNION ALL

select empid,empname 
from emp3 e
WHERE NOT EXISTS( SELECT empid FROM emp1 ee WHERE ee.empid = e.empid)
    AND NOT EXISTS( SELECT empid FROM emp2 ee WHERE ee.empid = e.empid)
UNION ALL

select empid,empname from emp4 e
WHERE NOT EXISTS( SELECT empid FROM emp1 ee WHERE ee.empid = e.empid)
    AND NOT EXISTS( SELECT empid FROM emp2 ee WHERE ee.empid = e.empid)
    AND NOT EXISTS( SELECT empid FROM emp3 ee WHERE ee.empid = e.empid)

#3

This isn't pretty, but it works.

这不是很好,但它确实有效。

INSERT INTO emp3
SELECT 1, 'pwalton'

INSERT INTO emp4
SELECT 2, 'jimmy'

DECLARE @myTable TABLE (empid INT, empname VARCHAR(255))

INSERT INTO @myTable
SELECT empid, empname FROM emp1

IF NOT EXISTS (SELECT 1 FROM @myTable)
BEGIN
    INSERT INTO @myTable
    SELECT empid, empname FROM emp2
END

IF NOT EXISTS (SELECT 1 FROM @myTable)
BEGIN
    INSERT INTO @myTable
    SELECT empid, empname FROM emp3
END

IF NOT EXISTS (SELECT 1 FROM @myTable)
BEGIN
    INSERT INTO @myTable
    SELECT empid, empname FROM emp4
END

SELECT * FROM @myTable
--results: 1    pwalton

#1