SQL Server：如何加入记录不在另一个表中？

debugcn 发表于 Dev

Joe Yan

我想显示员工的评估提交状态。

Appraisal_Record如果文件已提交，它将在表中添加一条记录。

我该如何编写SQL来显示所有员工的所有评估期，即使他们尚未提交评估（无记录）。添加一列“状态”以指示状态，如果“ [评估记录]”表中没有记录，则“-已提交”和“未提交_提交” <-

我想在sql语句中使用一种情况来添加列... CASE何时xxx，然后“已提交”，否则“未提交”为状态

谢谢

以下是示例

桌子： StaffDB

+---+-----+
|SID|Name |
+---+-----+
|1  |Peter|
+---+-----+
|2  |John |
+---+-----+
|3  |Amy  |
+---+-----+

桌子： Appraisal Period

+--------+-----------------+
|PeriodID|PeriodDescription|
+--------+-----------------+
|1       |2012_Start_Term  |
+--------+-----------------+
|2       |2012_Mid_Term    |
+--------+-----------------+
|3       |2012_End_Term    |
+--------+-----------------+

桌子： Appraisal_Record

+--+---+--------+
|ID|SID|PeriodID|
+--+---+--------+
|1 |1  |1       |
+--+---+--------+
|2 |1  |2       |
+--+---+--------+
|3 |2  |3       |
+--+---+--------+

结果：

+---+-----+--------+-----------------+-------------+
|SID|Name |PeriodID|PeriodDescription|Status       |
+---+-----+--------+-----------------+-------------+
|1  |Peter|1       |2012_Start_Term  |Submitted    |
+---+-----+--------+-----------------+-------------+
|1  |Peter|2       |2012_Mid_Term    |Submitted    |
+---+-----+--------+-----------------+-------------+
|1  |Peter|3       |2012_End_Term    |Not_Submitted|
+---+-----+--------+-----------------+-------------+
|2  |John |1       |2012_Start_Term  |Not_Submitted|
+---+-----+--------+-----------------+-------------+
|2  |John |2       |2012_Mid_Term    |Not_Submitted|
+---+-----+--------+-----------------+-------------+
|2  |John |3       |2012_End_Term    |Submitted    |
+---+-----+--------+-----------------+-------------+
|3  |Amy  |1       |2012_Start_Term  |Not_Submitted|
+---+-----+--------+-----------------+-------------+
|3  |Amy  |2       |2012_Mid_Term    |Not_Submitted|
+---+-----+--------+-----------------+-------------+
|3  |Amy  |3       |2012_End_Term    |Not_Submitted|
+---+-----+--------+-----------------+-------------+

感谢Kishore和Damien。

解决方案是：

select a.sid,a.name,b.periodid,b.perioddescription,
CASE WHEN c.ID IS NOT NULL then 'Submitted' ELSE 'Not_Submitted' as Status
from 
StaffDB a 
cross join 
[Appraisal Period] b
left join 
Appraisal_Record c on a.sid = c.sid and b.periodid =  c.periodid

Kishore

这是您要查找的（假设您的appraisal_record表中的status列）

select a.sid,a.name,b.periodid,b.perioddescription,c.status
from 
StaffDB a 
cross join 
[Appraisal Period] b
left join 
Appraisal_Record c on a.sid = c.sid and b.periodid =  c.periodid

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。