问题描述
我有:
tbl_product: ------------ product_id name description tbl_user: ------------ user_id first_name last_name initials
多个用户可以拥有一个产品,我通过创建一个表格来表示:
Multiple users can own a single product and I represent that by creating a table:
xref_product_user: product_id user_id
组成复合主键,其中每一列都是各自表的外键.
that make up a composite primary key where each column is a foreign_key to their respective tables.
由于每个产品可以有多个用户,所以我需要写一个包含
Since each product can have multiple users, I need to write a select statement that contains
产品名称、描述、组合用户首字母(逗号分隔的字符串).
product name, description, combined user initials (comma separated string).
假设我有一个产品 chocolate 归用户 mike 所有约翰逊 和丹威廉姆斯.那么我的结果应该是
So lets say I have a product chocolate that are owned by user mike johnson and dan williams. Well my results should be
NAME DESCRIPTION INTIALS chocolate candy mj, dw
由于首字母部分,我似乎无法弄清楚如何编写此 select 语句.有人有什么想法吗?
I can't seem to figure out how to write this select statement because of the initials part. Anyone have any ideas?
推荐答案
函数可能是一种很好的、??易于维护的处理方法:
A Function would probably be a good, easily maintainable way to handle that:
CREATE FUNCTION [dbo].[fn_GetInitialsForProduct] ( @product_id ) RETURNS varchar(200) AS BEGIN declare @Initials varchar(200) set @Initials = '' select @Initials=@Initials + ', ' + isnull(u.Initials, '') from dbo.tbl_user u inner join dbo.xref_product_user x on u.user_id = x.user_id where x.product_id = @product_id order by u.Initials if left(@Initials, 2) = ', ' set @Initials = substring(@Initials, 3, len(@Initials) - 2) return @Initials END --AND HERE'S HOW TO CALL IT select p.name, p.description, dbo.GetInitialsForProduct(p.product_id) as Initials from tbl_product p