表2的数据不在表1的ID内如何显示一个值？

[小吵]

如下图，Table1是一个源表，Table2是数据表。

把Table2的CID和Table1的ID连接起来，从而把Table1.Name显示出来，但是Table2中有一个数据（黄底）是Table1中ID没有，这时如果要把相关的字段显示为“无”,应该如何操作？ 有什么函数可以用？
不会要用case when这么长的吧。。。。

[听雨停了]

直接连接表2不就行了，right join 以表2为主表来连不就行了

[吉普赛的歌]

USE tempdb
GO
IF OBJECT_ID('Table1') IS NOT NULL DROP TABLE Table1
IF OBJECT_ID('Table2') IS NOT NULL DROP TABLE Table2

CREATE TABLE Table1 (ID INT PRIMARY KEY,[Name] NVARCHAR(20))
CREATE TABLE Table2 (CID INT,[worker] NVARCHAR(20))

INSERT INTO Table1(ID,Name) VALUES(1,'T1')
INSERT INTO Table1(ID,Name) VALUES(2,'T2')
INSERT INTO Table1(ID,Name) VALUES(3,'T3')

INSERT INTO Table2(CID,worker) VALUES(1,'张三')
INSERT INTO Table2(CID,worker) VALUES(3,'张四')
INSERT INTO Table2(CID,worker) VALUES(2,'张五')
INSERT INTO Table2(CID,worker) VALUES(0,'无名')
INSERT INTO Table2(CID,worker) VALUES(1,'张三')

SELECT 
ID
,b.CID
,CASE WHEN a.ID IS NULL THEN '无' else b.worker END as worker
FROM Table1 AS a RIGHT JOIN Table2 AS b ON a.ID=b.CID
/*
ID          CID         worker
----------- ----------- --------------------
1           1           张三
3           3           张四
2           2           张五
NULL        0           无
1           1           张三
*/

[二月十六]

select * ,case when table1.id is null then '无' else '有' end as col from table2 left join table1 on table2.cid =table1.id

[砸死牛顿的苹果]

只有2个case 分支 可以用iif

select id,cid,iif(id is null,'无',worker)worker from table2 t2 left join  table1 t1 on t1.ID =t2.CID

[小吵]

引用 3 楼 你那美丽的麻花辫的回复:

USE tempdb
GO
IF OBJECT_ID('Table1') IS NOT NULL DROP TABLE Table1
IF OBJECT_ID('Table2') IS NOT NULL DROP TABLE Table2

CREATE TABLE Table1 (ID INT PRIMARY KEY,[Name] NVARCHAR(20))
CREATE TABLE Table2 (CID INT,[worker] NVARCHAR(20))

INSERT INTO Table1(ID,Name) VALUES(1,'T1')
INSERT INTO Table1(ID,Name) VALUES(2,'T2')
INSERT INTO Table1(ID,Name) VALUES(3,'T3')

INSERT INTO Table2(CID,worker) VALUES(1,'张三')
INSERT INTO Table2(CID,worker) VALUES(3,'张四')
INSERT INTO Table2(CID,worker) VALUES(2,'张五')
INSERT INTO Table2(CID,worker) VALUES(0,'无名')
INSERT INTO Table2(CID,worker) VALUES(1,'张三')

SELECT 
ID
,b.CID
,CASE WHEN a.ID IS NULL THEN '无' else b.worker END as worker
FROM Table1 AS a RIGHT JOIN Table2 AS b ON a.ID=b.CID
/*
ID          CID         worker
----------- ----------- --------------------
1           1           张三
3           3           张四
2           2           张五
NULL        0           无
1           1           张三
*/

明白了，原来用left join就可以，还真是从来没用过这个功能。。。。

[吉普赛的歌]

引用 4 楼 Evanlei 的回复:

[quote=引用 3 楼 你那美丽的麻花辫的回复:]

USE tempdb
GO
IF OBJECT_ID('Table1') IS NOT NULL DROP TABLE Table1
IF OBJECT_ID('Table2') IS NOT NULL DROP TABLE Table2

CREATE TABLE Table1 (ID INT PRIMARY KEY,[Name] NVARCHAR(20))
CREATE TABLE Table2 (CID INT,[worker] NVARCHAR(20))

INSERT INTO Table1(ID,Name) VALUES(1,'T1')
INSERT INTO Table1(ID,Name) VALUES(2,'T2')
INSERT INTO Table1(ID,Name) VALUES(3,'T3')

INSERT INTO Table2(CID,worker) VALUES(1,'张三')
INSERT INTO Table2(CID,worker) VALUES(3,'张四')
INSERT INTO Table2(CID,worker) VALUES(2,'张五')
INSERT INTO Table2(CID,worker) VALUES(0,'无名')
INSERT INTO Table2(CID,worker) VALUES(1,'张三')

SELECT 
ID
,b.CID
,CASE WHEN a.ID IS NULL THEN '无' else b.worker END as worker
FROM Table1 AS a RIGHT JOIN Table2 AS b ON a.ID=b.CID
/*
ID          CID         worker
----------- ----------- --------------------
1           1           张三
3           3           张四
2           2           张五
NULL        0           无
1           1           张三
*/

明白了，原来用left join就可以，还真是从来没用过这个功能。。。。[/quote] 没事就结贴吧