select a.a_name, a.a_id, b.b_value from a right outer join b on a.a_id=b.a_id order by b.b_order desc
我的实例:可以参考 1。建立表 create table A(A_id varchar(4),A_Name varchar(20)) go create table b(A_id varchar(4),B_Value int,B_Order int) go 2。输入测试数据 insert into A values('A001','IBM') insert into A values('A003','HP') insert into A values('A006','COMPAQ') insert into A values('A007','MICROSOFT') insert into B values('A001',222,1) insert into B values('A003',4944,2) insert into B values('A006',4954,3) insert into B values('A007',455,4) insert into B values('A001',2922,5) insert into B values('A003',4464,6) insert into B values('A006',454,7) insert into B values('A007',425,8) insert into B values('A003',2122,9) insert into B values('A003',444,10) insert into B values('A001',4524,11) insert into B values('A003',451,12) 3。建立函数 drop function get_val go CREATE FUNCTION Get_val (@A_Id varchar(4)) RETURNS varchar(2000) AS BEGIN declare @Ret_Val varchar(2000)set @Ret_Val =''select @Ret_Val=@Ret_Val+'|'+convert(varchar,b_value) from b where A_Id=@A_id --print @ret_val RETURN(@Ret_Val) EnD go 4。sql语句中调用 select A_id,A_Name,dbo.get_val(A_Id) from A注意:只能在sqlserver2000中使用。
on a.a_id=b.a_id
group by a.id
order by b.b_order desc
select a.a_name, a.a_id, b.b_value
from a left join b
on a.a_id=b.a_id
order by b.b_order desc
from a right outer join b
on a.a_id=b.a_id
order by b.b_order desc
1。建立表
create table A(A_id varchar(4),A_Name varchar(20))
go
create table b(A_id varchar(4),B_Value int,B_Order int)
go
2。输入测试数据
insert into A values('A001','IBM')
insert into A values('A003','HP')
insert into A values('A006','COMPAQ')
insert into A values('A007','MICROSOFT')
insert into B values('A001',222,1)
insert into B values('A003',4944,2)
insert into B values('A006',4954,3)
insert into B values('A007',455,4)
insert into B values('A001',2922,5)
insert into B values('A003',4464,6)
insert into B values('A006',454,7)
insert into B values('A007',425,8)
insert into B values('A003',2122,9)
insert into B values('A003',444,10)
insert into B values('A001',4524,11)
insert into B values('A003',451,12)
3。建立函数
drop function get_val
go
CREATE FUNCTION Get_val (@A_Id varchar(4))
RETURNS varchar(2000)
AS
BEGIN
declare @Ret_Val varchar(2000)set @Ret_Val =''select @Ret_Val=@Ret_Val+'|'+convert(varchar,b_value) from b where A_Id=@A_id
--print @ret_val
RETURN(@Ret_Val)
EnD
go
4。sql语句中调用
select A_id,A_Name,dbo.get_val(A_Id) from A注意:只能在sqlserver2000中使用。