34,838
社区成员




CREATE TABLE #a
(
RiQi date,
aTest NVARCHAR(200)
)
CREATE TABLE #b
(
RiQi Date,
bTest NVARCHAR(200)
)
INSERT INTO #a( RiQi, aTest)VALUES('2013-06-11','A第一条')
INSERT INTO #a( RiQi, aTest)VALUES('2013-06-11','A第二条')
INSERT INTO #b( RiQi, BTest)VALUES('2013-06-11','B第一条')
Select f.RiQi, a.aTest, b.BTest
From (select RiQi, ROW_NUMBER() OVER(PARTITION BY RiQi ORDER BY aTest) AS Row from #a UNION select RiQi, ROW_NUMBER() OVER(PARTITION BY RiQi ORDER BY bTest) AS Row from #b ) f
LEFT JOIN (select RiQi, aTest, ROW_NUMBER() OVER(PARTITION BY RiQi ORDER BY aTest) AS Row from #a) a ON f.riqi = a.riqi AND f.row = a.row
LEFT JOIN (select RiQi, bTest, ROW_NUMBER() OVER(PARTITION BY RiQi ORDER BY bTest) AS Row from #b) b ON f.riqi = b.riqi AND f.row = b.row
CREATE TABLE #a
(
RiQi date,
aTest NVARCHAR(20)
)
CREATE TABLE #b
(
RiQi Date,
BTest NVARCHAR(20)
)
INSERT INTO #a( RiQi, aTest)VALUES('2013-06-11','A第一条')
INSERT INTO #a( RiQi, aTest)VALUES('2013-06-11','A第二条')
INSERT INTO #b( RiQi, BTest)VALUES('2013-06-11','B第一条')
SELECT DISTINCT * FROM #a LEFT JOIN #b ON #b.RiQi = #a.RiQi
B表有一条总复的数据
RiQi aTest RiQi BTest
---------- -------------------- ---------- --------------------
2013-06-11 A第二条 2013-06-11 B第一条
2013-06-11 A第一条 2013-06-11 B第一条
(2 行受影响)
CREATE TABLE #a
(
RiQi date,
aTest NVARCHAR(200)
)
CREATE TABLE #b
(
RiQi Date,
BTest NVARCHAR(200)
)
INSERT INTO #a( RiQi, aTest)VALUES('2013-06-11','A第一条')
INSERT INTO #a( RiQi, aTest)VALUES('2013-06-11','A第二条')
INSERT INTO #b( RiQi, BTest)VALUES('2013-06-11','B第一条')
SELECT DISTINCT * FROM #a LEFT JOIN #b ON #b.RiQi = #a.RiQi
CREATE TABLE #a
(
RiQi date,
aTest NVARCHAR(200)
)
CREATE TABLE #b
(
RiQi Date,
BTest NVARCHAR(200)
)
INSERT INTO #a( RiQi, aTest)VALUES('2013-06-11','A')
INSERT INTO #a( RiQi, aTest)VALUES('2013-06-11','A')
INSERT INTO #b( RiQi, BTest)VALUES('2013-06-11','B')
SELECT DISTINCT * FROM #a LEFT JOIN #b ON #b.RiQi = #a.RiQi
CREATE TABLE #a
(
RiQi date,
aTest NVARCHAR(200)
)
CREATE TABLE #b
(
RiQi Date,
BTest NVARCHAR(200)
)
INSERT INTO #a( RiQi, aTest)VALUES('2013-06-11','A')
INSERT INTO #a( RiQi, aTest)VALUES('2013-06-11','A')
INSERT INTO #b( RiQi, BTest)VALUES('2013-06-11','B')
SELECT * FROM #a LEFT JOIN #b ON #b.RiQi = #a.RiQi
刚才测了一下,用这个方法,可是如何取消重复呢?
CREATE TABLE #a
(
ID INT,
aTest NVARCHAR(200)
)
CREATE TABLE #b
(
ID INT,
BTest NVARCHAR(200)
)
INSERT INTO #a( ID, aTest)VALUES(1,'A')
INSERT INTO #a( ID, aTest)VALUES(2,'A')
INSERT INTO #b( ID, BTest)VALUES(1,'B')
SELECT * FROM #a LEFT JOIN #b ON #b.ID = #a.ID
--这样的效果,但这里要怎么做,如果#a表是1行的#b有两行的话,这样就只有一条数据了.
想解决不管哪个表有多少条数据.以最多的的一个表为准,另一个表不够用NULL补上.如果只有用左右的话,如何动态转换呢?