--> liangCK小梁 于2008-10-14
--> 生成测试数据: #tb1
IF OBJECT_ID('tempdb.dbo.#tb1') IS NOT NULL DROP TABLE #tb1
CREATE TABLE #tb1 (rq VARCHAR(6),djbh VARCHAR(3),dwbh1 VARCHAR(3),dwbh2 VARCHAR(3),dwbh3 VARCHAR(3))
INSERT INTO #tb1
SELECT '081010','003','001','002','003' UNION ALL
SELECT '081010','004','001',null,null UNION ALL
SELECT '081010','005','002','003',null
--> liangCK小梁 于2008-10-14
--> 生成测试数据: #tb2
IF OBJECT_ID('tempdb.dbo.#tb2') IS NOT NULL DROP TABLE #tb2
CREATE TABLE #tb2 (dwbh VARCHAR(3),dwmc NVARCHAR(8))
INSERT INTO #tb2
SELECT '001','同仁堂' UNION ALL
SELECT '002','华联超市' UNION ALL
SELECT '003','北国超市'--SQL查询如下:SELECT rq,djbh,b.dwmc,c.dwmc,d.dwmc
FROM #tb1 AS a
LEFT OUTER JOIN #tb2 AS b
ON a.dwbh1=b.dwbh
LEFT OUTER JOIN #tb2 AS c
ON a.dwbh2=c.dwbh
LEFT OUTER JOIN #tb2 AS d
ON a.dwbh3=d.dwbh
/*
rq djbh dwmc dwmc dwmc
------ ---- -------- -------- --------
081010 003 同仁堂 华联超市 北国超市
081010 004 同仁堂 NULL NULL
081010 005 华联超市 北国超市 NULL(3 行受影响)*/
--> 生成测试数据: #tb1
IF OBJECT_ID('tempdb.dbo.#tb1') IS NOT NULL DROP TABLE #tb1
CREATE TABLE #tb1 (rq VARCHAR(6),djbh VARCHAR(3),dwbh1 VARCHAR(3),dwbh2 VARCHAR(3),dwbh3 VARCHAR(3))
INSERT INTO #tb1
SELECT '081010','003','001','002','003' UNION ALL
SELECT '081010','004','001',null,null UNION ALL
SELECT '081010','005','002','003',null
--> liangCK小梁 于2008-10-14
--> 生成测试数据: #tb2
IF OBJECT_ID('tempdb.dbo.#tb2') IS NOT NULL DROP TABLE #tb2
CREATE TABLE #tb2 (dwbh VARCHAR(3),dwmc NVARCHAR(8))
INSERT INTO #tb2
SELECT '001','同仁堂' UNION ALL
SELECT '002','华联超市' UNION ALL
SELECT '003','北国超市'--SQL查询如下:SELECT rq,djbh,b.dwmc,c.dwmc,d.dwmc
FROM #tb1 AS a
LEFT OUTER JOIN #tb2 AS b
ON a.dwbh1=b.dwbh
LEFT OUTER JOIN #tb2 AS c
ON a.dwbh2=c.dwbh
LEFT OUTER JOIN #tb2 AS d
ON a.dwbh3=d.dwbh
/*
rq djbh dwmc dwmc dwmc
------ ---- -------- -------- --------
081010 003 同仁堂 华联超市 北国超市
081010 004 同仁堂 NULL NULL
081010 005 华联超市 北国超市 NULL(3 行受影响)*/
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货