哈哈。先准备数据
CREATE TABLE #Master
(no1 VARCHAR(20),material1 NVARCHAR(20) )
CREATE TABLE #Detail
(no2 VARCHAR(20),material2 NVARCHAR(20) )
INSERT INTO #Master
SELECT '090901','A1' UNION
SELECT '090901','A2' UNION
SELECT '090901','A3' UNION
SELECT '090902','B1' UNION
SELECT '090902','B2' UNION
SELECT '090902','B3'
INSERT INTO #Detail
SELECT '090901','C1' UNION
SELECT '090901','C2' UNION
SELECT '090902','C3'
DROP TABLE #Master
DROP TABLE #Detail
问题来了。
现在要得到以下结果
SELECT '090901','A1' ,'090901','C1' UNION
SELECT '090901','A2' ,'090901','C2' UNION
SELECT '090901','A3' ,'','' UNION
SELECT '090902','B1' ,'090902','C1' UNION
SELECT '090902','B2' ,'','' UNION
SELECT '090902','B3' ,'',''
条件:
1.Master中的记录一定比Detail的多
2.Master与Detail中No1和No2相同的可以放在同一行
3.排序无关系,这样的结果也是正确的
SELECT '090901','A1' ,'090901','C1' UNION
SELECT '090901','A2' ,'','' UNION
SELECT '090901','A3' ,'090901','C2' UNION
SELECT '090902','B1' ,'','' UNION
SELECT '090902','B2' ,'090902','C1' UNION
SELECT '090902','B3' ,'',''
来试一下吧。