求一SQL写法和思路

Question

这个是销售系统中的一个问题，有两个表，一个表头，一个表体，表体可能有多条分录，所以一个表头可以对应多个表体
表头的三个字段分别是主键FID，编号FNo，客户FCust，信息如下：
Insert BillHead Select
1001,'PF0001','人人乐'    UNION SELECT
1002,'PF0002','大洋百货'  UNION SELECT
1003,'PF0002','茂业百货' 
GO表体的四个字段分别是主键FID，分录FEntryID，商品FGood，数量FQty，单价FPrice，信息如下：
Insert BillEntry Select
1001,1,'蔬果',12,15     UNION SELECT
1001,2,'夏装',100,60    UNION SELECT
1002,1,'提包',30,200    UNION SELECT
1003,1,'香水',20,1000 
GO
现在想通过select查询得到如下的字符串:
编号:PF0001 客户:人人乐 商品:蔬果 数量:12 单价:15 商品:夏装 数量:100 单价:60
编号:PF0002 客户:大洋百货 商品:提包 数量:30 单价:200
编号:PF0003 客户:茂业百货 商品:香水 数量:20 单价:1000
也就是把表头和表体的信息打包整理出来，表头只有一条记录，表体可能有多条记录
再进一步考虑，可以把表头和表体字段的名字存放在另外一个表中,字段分别是，主键FID（自动编号）,字段FFieldName，字段名FFieldCaption
Insert BillTemplate Select
1,'FNO','编号'
2,'FCust','客户'
3,'FGood','商品'
4,'FQty','数量'
5,'FPrice','单价'
这种情况SQL又该怎么写呢?????

Answer 1

--sql 2000用如下函数实现.create table BillHead (FID int,FNo varchar(10),FCust varchar(10))
Insert BillHead Select
1001,'PF0001','人人乐' UNION SELECT
1002,'PF0002','大洋百货' UNION SELECT
1003,'PF0003','茂业百货'  
create table BillEntry(FID int,FEntryID int,FGood varchar(10),FQty int,FPrice int)
Insert BillEntry Select
1001,1,'蔬果',12,15 UNION SELECT
1001,2,'夏装',100,60 UNION SELECT
1002,1,'提包',30,200 UNION SELECT
1003,1,'香水',20,1000  
GOcreate function dbo.f_str(@FID int) returns varchar(1000)
as
begin
  declare @str varchar(1000)
  select @str = isnull(@str + ' ' , '') + '商品:'+cast(FGood as varchar) + ' 数量:' + ltrim(FQty) + ' 单价:' + ltrim(FPrice) from BillEntry where FID = @FID
  return @str
end
go--调用函数select '编号:' + m.FNo + ' 客户:' + FCust + ' ' + isnull(n.value,'') from BillHead m
left join
(select FID , value = dbo.f_str(FID) from BillEntry group by FID) n
on m.FID = n.FIDdrop function dbo.f_str
drop table BillHead ,BillEntry/*
                                                                           
---------------------------------------------------------------------------
编号:PF0001 客户:人人乐 商品:蔬果 数量:12 单价:15 商品:夏装 数量:100 单价:60
编号:PF0002 客户:大洋百货 商品:提包 数量:30 单价:200
编号:PF0003 客户:茂业百货 商品:香水 数量:20 单价:1000（所影响的行数为 3 行）
*/

Answer 2

--sql 2005
create table BillHead (FID int,FNo varchar(10),FCust nvarchar(10))
Insert BillHead Select
1001,'PF0001',N'人人乐' UNION SELECT
1002,'PF0002',N'大洋百货' UNION SELECT
1003,'PF0003',N'茂业百货'  
create table BillEntry(FID int,FEntryID int,FGood nvarchar(10),FQty int,FPrice int)
Insert BillEntry Select
1001,1,N'蔬果',12,15 UNION SELECT
1001,2,N'夏装',100,60 UNION SELECT
1002,1,N'提包',30,200 UNION SELECT
1003,1,N'香水',20,1000  
GO
select N'编号:' + m.FNo + N' 客户:' + FCust + ' ' + isnull(n.[value],'') from BillHead m
left join
(
  select FID, [value] = stuff((select ',' + N'商品:'+cast(FGood as nvarchar) + N' 数量:' + ltrim(FQty) + N' 单价:' + ltrim(FPrice)  from BillEntry t where FID = BillEntry.FID for xml path('')) , 1 , 1 , '')
  from BillEntry
  group by FID
) n
on m.FID = n.FIDdrop table BillHead ,BillEntry/*----------------------------------------------------------------------------
编号:PF0001 客户:人人乐 商品:蔬果 数量:12 单价:15,商品:夏装 数量:100 单价:60
编号:PF0002 客户:大洋百货 商品:提包 数量:30 单价:200
编号:PF0003 客户:茂业百货 商品:香水 数量:20 单价:1000(3 行受影响)
*/

Answer 3

这样并发会不会有问题：重复创建函数了！sql为什么一直不支持局部函数呢？作用域就像表变量一样

Answer 4

这个SQL确实很好，函数那里它怎么就能取到多条分录的值呢，演示了下效果是想要的，但是不明白它是怎么取多条分录的，另外如果字段的标题是写在另一个表中需要再写一个函数吧

Answer 5

如果数据库字段的名字是放在另一个表FieldTemplate中的，那么我想可以再写一个函数，每个字段都调用一下，如下所示create function dbo.f_getcaption(@sFieldName nchar(30)) returns nchar(30)
as
begin
declare @sFieldCaption nchar(30)
select @sFieldCaption = FFieldCaption From FieldTemplate Where FFieldName = @sFieldName 
return @sFieldCaption
end
go--在查询中每个字段都调用一次这个函数---select rtrim(dbo.f_getCaption('FBillNo'))+':' + rtrim(m.FBillNo) + rtrim(dbo.f_getCaption('FCust'))+':' + rtrim(m.FCust) + ' ' + isnull(n.value,'') from BillHead m
left join
(select FInterID , value = dbo.f_str(FInterID) from BillEntry group by FInterID) n
on m.FInterID = n.FInterID如上面的dbo.f_getCaption('FBillNo'),dbo.f_getCaption('FCust') 但是感觉这样的效率很低下，应该可以直接调用一次把字段名和字段标题放在一个数组中，在select查询中直接取就可以了，sql中有数据或者能存放临时数据的东东吗?

Answer 6

有时候，函数只是因某个sql语句段需要重复使用，才临时需要的，
为此创建一个全局的函数，有点浪费，也会影响数据库的清晰
如果局部函数，才是最好的选择