// 表如下
DROP TABLE AA
CREATE TABLE AA
(
nID integer PRIMARY KEY,
sName nvarchar(10),
sOp nvarchar(10)
)
DROP TABLE BB
INSERT INTO AA VALUES (0, '000', '==')
INSERT INTO AA VALUES (1, '001', '==')
INSERT INTO AA VALUES (2, '002', '==')
CREATE TABLE BB
(
nID integer PRIMARY KEY,
sInfo nvarchar(10)
)
INSERT INTO BB VALUES (0, '000')
INSERT INTO BB VALUES (1, '001')
INSERT INTO BB VALUES (2, '002')// 查询如下
SELECT T1.nID AS nID,
T1.sName AS sName,
T1.sOp AS sOp,
T2.sInfo AS sInfo
FROM AA T1 JOIN BB T2 ON T1.nID = T2.nID
ORDER BY T1.nID ASC结果如下
nID sName sOp sInfo
0 000 == 000
1 001 == 001
2 002 == 002但是,如果BB表为空的话,那么查询将会没有数据,而我想要,如下数据,
nID sName sOp sInfo
0 000 == 某个默认值
1 001 == 某个默认值
2 002 == 某个默认值
就是,我要以表AA为主,如果BB没有值那么BB那个字段我就以默认值填它,如果有就一并返回
这个SQL语句如何写
T1.sName AS sName,
T1.sOp AS sOp,
isnull(T2.sInfo,'你的默认值') AS sInfo
FROM AA T1
left JOIN BB T2 ON T1.nID = T2.nID
ORDER BY T1.nID ASC
T1.sName AS sName,
T1.sOp AS sOp,
sInfo=case when T2.sInfo is null then null else T2.sInfo end
FROM AA T1 JOIN BB T2 ON T1.nID = T2.nID
ORDER BY T1.nID ASC
A.sName AS sName,
A.sOp AS sOp,
isnull(T2.sInfo,'默认值') AS sInfo
FROM AA A
left JOIN BB B ON A.nID = B.nID
ORDER BY A.nID ASC
消息 4104,级别 16,状态 1,第 62 行
无法绑定由多个部分组成的标识符 "T2.sInfo"。
3楼得改下笔误
SELECT A.nID AS nID,
A.sName AS sName,
A.sOp AS sOp,
isnull(B.sInfo,'默认值') AS sInfo
FROM AA A
left JOIN BB B ON A.nID = B.nID
ORDER BY A.nID ASC
SELECT T1.nID AS nID,
T1.sName AS sName,
T1.sOp AS sOp,
sInfo=case when T2.sInfo is null then null else T2.sInfo end
FROM AA T1 LEFT JOIN BB T2 ON T1.nID = T2.nID
ORDER BY T1.nID ASC