嗨，我在sql server中有疑问
部门
+---------+--------+
| deptkey | deptno |
+---------+--------+
| 1 | 100 |
| 2 | 101 |
| 3 | -1 |
+---------+--------+
地方
+--------+-------+
| lockey | locid |
+--------+-------+
| 1 | 200 |
| 2 | 201 |
| 3 | -1 |
+--------+-------+
反式
+----+--------+-------+------+
| id | deptno | locid | Name |
+----+--------+-------+------+
| 1 | 100 | 201 | abc |
| 2 | 101 | 203 | def |
| 3 | 103 | 200 | rav |
| 4 | 105 | 204 | jai |
| 1 | 101 | 200 | kal |
| 4 | 100 | 206 | lo |
+----+--------+-------+------+
这里tran deptno = dept.deptno然后对应的键值如果不匹配，则需要不匹配的deptno分配-1，而对应的键需要检索相似的tran locid = loc.locid
基于上面的表格，我想像下面这样输出
+----+------+---------+--------+
| id | Name | deptkey | lockey |
+----+------+---------+--------+
| 1 | abc | 1 | 2 |
| 2 | def | 2 | 3 |
| 3 | rav | 3 | 1 |
| 4 | jai | 3 | 3 |
| 1 | kal | 2 | 1 |
| 4 | lo | 1 | 3 |
+----+------+---------+--------+
我试过像下面的查询
SELECT a.[id],a.name ,b.deptkey,c.lockey
FROM [trans] a left join dept b on a.deptno=b.deptno
left join loc c on a.locid=c.locid
上面的查询未给出预期的结果，您能告诉我如何在SQL Server中编写查询以实现此任务吗？
SELECT a.[id],a.name ,
(CASE WHEN b.deptkey IS NULL THEN (select deptkey from DEPT WHERE DeptNo = -1)
ELSE b.deptkey END) AS 'deptkey',
(CASE WHEN c.lockey IS NULL THEN (select LocKey from LOC WHERE LocId = -1)
ELSE c.lockey END) AS 'lockey '
FROM [trans] a left join dept b on a.deptno=b.deptno
left join loc c on a.locid=c.locid
本文收集自互联网，转载请注明来源。
如有侵权，请联系 [email protected] 删除。
我来说两句