组合子查询中的行的 Select 语句(枢轴)
我有:
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
相关文章