問(wèn)題描述

我有:

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

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

多個(gè)用戶(hù)可以擁有一個(gè)產(chǎn)品，我通過(guò)創(chuàng)建一個(gè)表格來(lái)表示:

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.

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

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

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

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

假設(shè)我有一個(gè)產(chǎn)品 chocolate 歸用戶(hù) 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

由于首字母部分，我似乎無(wú)法弄清楚如何編寫(xiě)此 select 語(yǔ)句.有人有什么想法嗎?

I can't seem to figure out how to write this select statement because of the initials part. Anyone have any ideas?

推薦答案

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

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)于組合子查詢(xún)中的行的 Select 語(yǔ)句(樞軸)的文章就介紹到這了，希望我們推薦的答案對(duì)大家有所幫助，也希望大家多多支持html5模板網(wǎng)！