将sql表行与同一表的所有其他行连接在一起

I'm beginner in SQL Server querying. I have assigned to a task where I need to self join a table.

我是SQL Server查询的初学者。我分配了一个任务，我需要自己加入一个表格。

Above is table structure. And I need result as below. I have tried using self join , sub query etc. I couldn't get result.

以上是表结构。我需要如下的结果。我尝试过使用self join, sub query等，我无法得到结果。

ReqStatusId ReqStatus ChildId  ChildReqStatus
1           Open      2        On Hold 
1           Open      3        Closed  
2           On Hold   1        Open       
2           On Hold   3        Closed  
3           Closed    1        Open       
3           Closed    2        On Hold

Result should come as: Each row in a table should joined with all other rows

结果应该是这样的:表中的每一行应该与所有其他行相连接

4 个解决方案

#1

What you are trying to get is achieved through a cross join. If you select the table twice you would get the desired result.

您试图获得的是通过交叉连接实现的。如果您两次选择该表，您将得到所需的结果。

select a.reqstatusid, a.reqstatus, b.reqstatusid as childreqstatusid,
b.reqstatus as childreqstatus
from table a, table b
where a.reqstatusid <> b.reqstatusid

#2

use CROSS JOIN, Which gives you the Cartesian product between two tables

使用交叉连接，它给出两个表之间的笛卡尔积

Select * 
From YourTable A
CROSS JOIN YourTable B
Where A.ReqStatusId <> B.ReqStatusId

#3

You should do a JOIN on ReqStatusId <> ReqStatusId:

你应该在req上做一个JOIN。

WITH Tbl(ReqStatusId, ReqStatus) AS(
    SELECT 1, 'Open' UNION ALL
    SELECT 2, 'On Hold' UNION ALL
    SELECT 3, 'Closed'
)
SELECT
    t1.*,
    ChildId = t2.ReqStatusId,
    ChildReqStatus = t2.ReqStatus
FROM Tbl t1
INNER JOIN Tbl t2
    ON t2.ReqStatusId <> t1.ReqStatusId

#4

select a.reqstatusid, a.reqstatus, b.reqstatusid,
b.reqstatus
from table a
inner join table b on A.ReqStatusId = B.ReqStatusId 
Where A.ReqStatusId <> B.ReqStatusId

#1