如何在SQL Server中的单列中显示父表数据和子表数据

Question

我在SQL Server中有两个表。第一表具有成员名称列，第二个表具有其亲戚名称。我想在单列中显示两个表的数据，就像成员有任何亲戚一样，它必须在成员名称之后的第二行中立即显示，并且如果成员没有任何亲戚，则必须仅出现一行。例如，像下面有Memebrsname及其Cityname

的第一表

Regid        Name             City
----------------------------------------
1    Dr. Dharmesh Patel       New york
2    Dr. Vivek Patel          New york
3    Dr. Udayan Kachchhi      New york
4    Dr. Geeti Maheshwari     New york
5    Dr. SHANKAR DAYAL        Arizona
6    Dr. Tejas Kakkad         New york
7    Dr. TUSHAR PATEL         California
8    Dr. Arpan Shah           New york
9    Dr. AMI PATEL            New york

和Relatives表看起来像这样：

Regid   Name             City
-----------------------------------
6    MEETA KAKKAD        New york
1    Aditya Patel        New york
1    Namisha Patel       New york
3    Dimple Kachchhi     New york
4    Pankaj Maheshwari   New york
8    Agastya Shah        New york
8    Roma Shah           New york

我想显示这样的结果：

regid        Name             City
---------------------------------------------
 1   Dr. Dharmesh Patel       New york
 1      Aditya Patel          New york
 1     Namisha Patel          New york
 2   Dr. Vivek Patel          New york
 3   Dr. Udayan Kachchhi      New york
 3       Dimple Kachchhi      New york
 4   Dr. Geeti Maheshwari     New york
 4      Pankaj Maheshwari     New york  
 5   Dr. SHANKAR DAYAL        Arizona
 6   Dr. Tejas Kakkad         New york
 6       MEETA KAKKAD         New york
 7   Dr. TUSHAR PATEL         California
 8   Dr. Arpan Shah           New york
 8       Agastya Shah         New york
 8       Roma Shah            New york
 9  Dr. AMI PATEL             New york

如何在SQL Server中获得以上结果？请帮助

Answer 1

使用 union all使用row_number()函数在其父记录下获取子记录

select *,
         row_number() over (partition by Regid order by Sort) Seq
from
(
    select Regid, Name, City, 1 AS Sort from parent p 
    UNION ALL
    select Regid, Name, City, 2 AS Sort from child c 
)t

您也可以定义明确的排序

select Regid, Name, City, 1 AS Sort from parent p 
UNION ALL
select Regid, Name, City, 2 AS Sort from child c
order by  RegId, Sort

Answer 2

SELECT Regid,Name,City FROM
(
SELECT Regid,Name,City FROM PARENT
UNION
SELECT Regid,Name,City FROM CHILD
) AS T1
ORDER BY Regid

Answer 3

似乎您正在寻找由regid

订购的两个表的联合

select Regid, Name, City 
from table1 
union 
select Regid, Name, City 
from table2
order by regid

Answer 4

假设您想缩进亲戚的名字。使用seq确保成员出现在其亲戚面前：

select Regid, Name, City
from
(
select 1 as seq, Regid, Name, City 
from table1 
union all
select 2, Regid,'    ' + Name, City 
from table2 ) t
order by seq, Regid