日期:2014-05-18  浏览次数:20448 次

求教两个无关联表的右连接
t1表数据
a b c
t2表数据
d e
f g

如何得到结果:
a b c d e
null null null f g

------解决方案--------------------
SQL code

IF EXISTS (SELECT 1 FROM SYSOBJECTS WHERE name = 'tba')
BEGIN
    DROP TABLE tba
END
GO
CREATE TABLE tba
(
    col1 VARCHAR(10),
    col2 VARCHAR(10),
    col3 VARCHAR(10)
)
GO
INSERT INTO tba
SELECT 'a','b','c'
GO
IF EXISTS (SELECT 1 FROM SYSOBJECTS WHERE name = 'tbb')
BEGIN
    DROP TABLE tbb
END
GO
CREATE TABLE tbb
(
    col1 VARCHAR(10),
    col2 VARCHAR(10)
)
GO
INSERT INTO tbb
SELECT 'd','e' UNION
SELECT 'f','g'
GO

WITH t1 AS
(
SELECT RN=ROW_NUMBER() OVER (ORDER BY GETDATE()),col1,col2,col3
FROM tba
),
t2 AS
(
SELECT RN=ROW_NUMBER() OVER (ORDER BY GETDATE()),col1,col2
FROM tbb
)

SELECT t1.col1,t1.col2,t1.col3,t2.col1,t2.col2
FROM t1 FULL OUTER JOIN t2 ON t1.RN = t2.RN

col1    col2    col3    col1    col2
a    b    c    d    e
NULL    NULL    NULL    f    g