时间戳

首页 > 解决方案 > 随机连接表并返回列

sql - 随机连接表并返回列

问题描述

我有这 3 张桌子：

CREATE TABLE DimEmployee(EmployeeID INT)
CREATE TABLE DimDepartment(DepartmentID INT)
CREATE TABLE DimDocteur(PositionID INT)

INSERT INTO DimEmployee(EmployeeID) VALUES (1),(2),(3)
INSERT INTO DimDepartment(DepartmentID) VALUES (1),(5),(6)
INSERT INTO DimPosition(PositionID) VALUES (7),(8),(9)

我想随机加入 3 个表并获得如下输出：（示例）

首先执行：

EmployeeID DepartmentID PositionID RandomDate
1          4            7          2020-07-24 00:00:00.000
2          5            9          2020-11-25 00:00:00.000

第二次执行：

EmployeeID DepartmentID PositionID RandomDate
1          4            7          2020-05-04 00:00:00.000  
2          5            9          2020-10-30 00:00:00.000

标签： sqlsql-servertsql

解决方案

如果你想要一个随机加入：

SELECT DP.EmployeeID, Q.Department INTO #T1
FROM DimEmployee AS DP

CROSS APPLY (SELECT TOP 1 Dd.DepartmentID FROM DimDepartment AS DD
ORDER BY NEWID() ) AS Q


SELECT * 
INTO #T2
FROM #T1 AS T
CROSS APPLY (SELECT TOP 1 DP.PositionID FROM DimPosition AS DP
ORDER BY NEWID() ) AS Q

或者，如果您想要所有可能性：

SELECT 
    a.EmployeeID,  b.DepartmentID, c.PositionID
FROM 
    DimEmployee AS a 
  CROSS JOIN 
    DimDepartment AS b
  CROSS JOIN 
    DimPosition AS c

推荐阅读