問題描述

我有:

tbl_product:
------------
product_id
name
description

tbl_user:
------------
user_id
first_name
last_name
initials

多個用戶可以擁有一個產(chǎn)品，我通過創(chuàng)建一個表格來表示:

Multiple users can own a single product and I represent that by creating a table:

xref_product_user: 
product_id
user_id 

組成復(fù)合主鍵，其中每一列都是各自表的外鍵.

that make up a composite primary key where each column is a foreign_key to their respective tables.

由于每個產(chǎn)品可以有多個用戶，所以我需要寫一個包含

Since each product can have multiple users, I need to write a select statement that contains

產(chǎn)品名稱、描述、組合用戶首字母(逗號分隔的字符串).

product name, description, combined user initials (comma separated string).

假設(shè)我有一個產(chǎn)品 chocolate 歸用戶 mike 所有約翰遜 和丹威廉姆斯.那么我的結(jié)果應(yīng)該是

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?

推薦答案

函數(shù)可能是一種很好的、??易于維護的處理方法:

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

這篇關(guān)于組合子查詢中的行的 Select 語句(樞軸)的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網(wǎng)！