我有一組當前的資料，看起來如下：
表名。Project_EffectiveDating
| 專案代碼 | 角色代碼生效日期。
|---|
| 1a1 |
上面的表格是現有的資料，下面的表格是想要的輸出。
我一直在嘗試用下面的方法來連接表本身：
我一直在嘗試用下面的方法來連接表本身。
select
dbd.projectcode
, dbd.角色
, dbd.effectivedate
from Project_EffectiveDating as dbd
left join Project_EffectiveDating as dad
on dad.projectcode = dbd.projectcode
and dbd.rolecode <>/span> dad.rolecode
但很明顯，這并不奏效。我正在考慮使用WHERE NOT IN()做一個聯合陳述句。 但我感到很茫然。如果有任何幫助，我將不勝感激。
uj5u.com熱心網友回復：
你似乎想在ProjectCode/RoleCode和EffectiveDate對之間得到一個笛卡爾乘積：
select *
from (select distinct ProjectCode, RoleCode
from Project_EffectiveDating
) pr cross join
(select distinct EffectiveDate
from Project_EffectiveDating
) e;
如果你想加入不存在的行：
insert into Project_EffectiveDating （ProjectCode, RoleCode, EffectiveDate)
select pr.ProjectCode, pr.RoleCode, e.EffectiveDate
from (select distinct ProjectCode, RoleCode
from Project_EffectiveDating
) pr cross join
(select distinct EffectiveDate
from Project_EffectiveDating
) e leftjoin
專案_有效日期
on ped.ProjectCode = pr.ProjectCodeand
ped.RoleCode = pr.RoleCode and
ped.EffectiveDate = pr.EffectiveDate
where ped.ProjectCode is null;
轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/314833.html
標籤：