27,579
社区成员
发帖
与我相关
我的任务
分享
--> 测试数据:#ta
IF OBJECT_ID('TEMPDB.DBO.#ta') IS NOT NULL DROP TABLE #ta
GO
CREATE TABLE #ta([a1] INT,[a2] INT,[a3] INT,[a4] INT)
INSERT #ta
SELECT 2,3,4,NULL UNION ALL
SELECT 4,2,3,1 UNION ALL
SELECT 5,6,7,2
--> 测试数据:#tb
IF OBJECT_ID('TEMPDB.DBO.#tb') IS NOT NULL DROP TABLE #tb
GO
CREATE TABLE #tb([b1] INT,[b2] INT,[b3] INT,[a1] INT)
INSERT #tb
SELECT 2,1,2,2 UNION ALL
SELECT 3,7,8,4 UNION ALL
SELECT 5,9,2,5
--------------开始查询--------------------------
SELECT a.a1,a2,
a3=CASE WHEN a4 IS NOT NULL AND a.a1<>b1 then b3 --如果a表中a4不等于null且a1值不等于b表中b1值,则将b3的值赋给a3
WHEN a4 IS NOT NULL AND a.a1=b1 THEN NULL--如果a4不等于null且a1值等于b表中b1值,由a3值为空。
WHEN a4 IS NULL THEN a2--如果a4值等于null,则a3值为a2
ELSE a3 end
FROM #ta a,#tb b WHERE a.a1=b.a1
----------------结果----------------------------
/*
a1 a2 a3
2 3 3
4 2 8
5 6 NULL
*/