22,210
社区成员
发帖
与我相关
我的任务
分享
DECLARE @TA TABLE([ID] VARCHAR(3), [Content] NVARCHAR(4))
INSERT @TA
SELECT '001', N'内容a1' UNION ALL
SELECT '001', N'内容a2' UNION ALL
SELECT '001', N'内容a3'
DECLARE @TB TABLE([ID] VARCHAR(3), [Content] NVARCHAR(4))
INSERT @TB
SELECT '001', N'内容b1' UNION ALL
SELECT '001', N'内容b2'
SELECT A.ID,A.Content,B.Content
FROM (
SELECT *,SEQ=ROW_NUMBER() OVER (ORDER BY GETDATE()) FROM @TA)A FULL JOIN (
SELECT *,SEQ=ROW_NUMBER() OVER (ORDER BY GETDATE()) FROM @TB) B ON A.SEQ=B.SEQ
/*
ID Content Content
---- ------- -------
001 内容a1 内容b1
001 内容a2 内容b2
001 内容a3 NULL
*/
---测试数据---
if object_id('[A]') is not null drop table [A]
go
create table [A]([ID] varchar(3),[Content] varchar(6))
insert [A]
select '001','内容a1' union all
select '001','内容a2' union all
select '001','内容a3'
if object_id('[B]') is not null drop table [B]
go
create table [B]([ID] varchar(3),[Content] varchar(6))
insert [B]
select '001','内容b1' union all
select '001','内容b2'
---查询---
select
a.id,
a.Content as aContent,
b.Content as bContent
from
(select *,px=(select count(1) as cnt from A t where t.id=a.id and t.content<a.content) from A) as A
left join
(select *,px=(select count(1) as cnt from B t where t.id=b.id and t.content<b.content) from B) as B
on
a.px=b.px
---结果---
id aContent bContent
---- -------- --------
001 内容a1 内容b1
001 内容a2 内容b2
001 内容a3 NULL
(所影响的行数为 3 行)
select
a.id,
a.Content as aContent,
b.Content as bContent
from
A
left join
B
on
a.id=b.id