如何效率的列出明细？

[木头是猫]

==========================================================
表A (订单)
ID ORDER_ID ........
1 20090316
2 20090319
==========================================================
表B (订单里产品明细)
ID ORDER_ID PR_NUM PR_ID
1 20090316 20 13
2 20090319 10 12
3 20090316 5 110
==========================================================
表C (产品)
PR_ID PR_NAME
12 AAAAA
13 BBBBB
110 CCCCC
==========================================================

显示页面list.asp

单号 产品
20090316 AAAAA,CCCCC
20090319 BBBBB

==========================================================
能有用一条SQL语句就解决这个列表显示方式，又或者有什么比较效率的方法呢？

[htl258_Tony]

IF OBJECT_ID('A') IS NOT NULL DROP TABLE A
GO
create table A(ID int, ORDER_ID varchar(10))
insert A select 1,  20090316 
insert A select 2,  20090319 
GO
IF OBJECT_ID('B') IS NOT NULL DROP TABLE B
GO
create table B (ID int,ORDER_ID varchar(10), PR_NUM int, PR_ID int)
insert B select 1 , 20090316 , 20 ,     13 
insert B select 2 , 20090319 , 10 ,     12 
insert B select 3 , 20090316 , 5  ,    110 
GO
IF OBJECT_ID('C') IS NOT NULL DROP TABLE C
GO
create table C (PR_ID int,PR_NAME varchar(10))
insert C select 12 ,   'AAAAA' 
insert C select 13 ,   'BBBBB' 
insert C select 110,   'CCCCC' 
GO
IF OBJECT_ID('f_str') IS NOT NULL DROP FUNCTION f_str
GO
CREATE FUNCTION dbo.f_str(@ORDER_ID varchar(20)) 
RETURNS varchar(8000) 
AS 
BEGIN 
    DECLARE @r varchar(8000) 
    SELECT @r =ISNULL(@r + ',','') + PR_NAME FROM C JOIN B ON C.PR_ID=B.PR_ID WHERE B.ORDER_ID=@ORDER_ID
    RETURN @r
END 
GO

select ORDER_ID 单号,dbo.f_str(ORDER_ID) 产品 from A order by ORDER_ID

单号         产品  
---------- -------------
20090316   BBBBB,CCCCC
20090319   AAAAA

（所影响的行数为 2 行）

楼主给的结果不对吧

[htl258_Tony]

又要函数了.

[sdhdy]

CREATE FUNCTION dbo.f_str(@ORDER_ID varchar(20)) 
RETURNS varchar(8000) 
AS 
BEGIN 
    DECLARE @r varchar(8000) 
    SET @r = '' 
    SELECT @r = @r + ',' + 产品 FROM 表C  WHERE PR_ID in (select PR_ID from 表B where ORDER_ID=@ORDER_ID)
    RETURN STUFF(@r, 1, 1, '') 
END 
go
select ORDER_ID,dbo.f_str(dbo.f_str) from 表A

[sdhdy]

--modify
CREATE FUNCTION dbo.f_str(@ORDER_ID varchar(20)) 
RETURNS varchar(8000) 
AS 
BEGIN 
    DECLARE @r varchar(8000) 
    SET @r = '' 
    SELECT @r = @r + ',' + PR_NAME FROM 表C  WHERE PR_ID in (select PR_ID from 表B where ORDER_ID=@ORDER_ID)
    RETURN STUFF(@r, 1, 1, '') 
END 
go
select ORDER_ID,dbo.f_str(dbo.f_str) from 表A