组合子查询中的行的 Select 语句(枢轴)

2021-09-10 00:00:00 tsql sql-server

我有:

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

相关文章