求sql~~这个查询能实现吗~~~如果可以求一个sql~~听说标题要尽量长点~~~~~~~~~
我有这样的两个表tableA和tableB
想得到下面这样的结果
如果可以，语句应该怎么写
谢
[解决办法]
select tableB.*,tel from tableB left join tableA on tableB.id=tableA.id
[解决办法]
select tableB.*,tel from tableB left join tableA on tableB.id=tableA.id
[解决办法]
----------------------------
-- Author :DBA_Huangzj(發糞塗牆)
-- Date :2013-05-13 13:47:06
-- Version:
-- Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64)
--Jun 17 2011 00:54:03
--Copyright (c) Microsoft Corporation
--Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1, v.721)
--
----------------------------
--> 测试数据：[A]
if object_id('[A]') is not null drop table [A]
go
create table [A]([id] int,[tel] int)
insert [A]
select 101,133133 union all
select 101,144144
--> 测试数据：[B]
if object_id('[B]') is not null drop table [B]
go
create table [B]([id] int,[address] varchar(4))
insert [B]
select 101,'add1' union all
select 101,'add2' union all
select 101,'add3'
--------------开始查询--------------------------
SELECT B.ID ,
[ADDRESS] ,
A.TEL
FROM ( SELECT B.* ,
ROW_NUMBER() OVER ( ORDER BY ID, [ADDRESS] ) PID
FROM B
) B
LEFT JOIN ( SELECT * ,
ROW_NUMBER() OVER ( ORDER BY ID, TEL ) PID
FROM A
) A ON B.PID = A.PID
----------------结果----------------------------
/*
ID ADDRESS TEL
----------- ------- -----------
101 add1 133133
101 add2 144144
101 add3 NULL
*/
with a as
(
select row_number() over(order by id)as aid,* from tablea
),b as
(
select row_number() over(order by id)as bid,* from tableb
)
select b.bid,b.id,b.address,a.tel from
(
select b.bid,a.id,b.address from b,a group by b.bid,a.id ,b.address
)b
left join a on a.aid =b.bid
