按列分组结果的查询（SQL Server） [英] Query with grouped results by column (SQL Server)

问题描述

我在项目之间有很多关系 - itemnames - 语言

I've got a many to many relationship between items-itemnames-languages

itemnames 不会对每种语言显示。

The itemnames don't appear for every language.

我想获得一个结果，所有的项目只表示一次，但可以设置 languageId 默认为。

I'd like to get a result with all the items only represented once, but be able to set the languageId to default to.

例如，项目1,2,3用两种语言定义，项目4和5各有一种语言，但语言不同

For example items 1,2,3 are defined in two languages, and item 4 and 5 have one language each, but the languages are different

[itemid][languageid][name]
1,       1,         item1
1,       2,         leItem1
2,       1,         item2
2,       2,         leItem2
3,       1,         item3
3,       2,         leItem3
4,       1,         item4
5,       2,         leItem5

我想创建一个只给我一个 itemID ，但允许我指定喜欢的语言，所以如果我选择 languageID 只返回以'leItem'开头的项目名称，除了项目4，它应该仍然给我 item4

I'd like to create a query that only gives me one of each itemID, but allow me to specify which language to prefer, so if I select a languageID of 2, my query would only return item names for that start with 'leItem' with the exception of item 4, which should still give me item4

任何想法如何实现这个与SELECT？

Any ideas how to achieve this with a SELECT?

这个理论（我如何让它工作），是我创建两个查询，一个对于 languageID 匹配x，第二个表示每个项目（按组或不同），然后合并结果。

The theory (how I'm trying to get it to work), is that I create two queries, one for all where languageID matches x , and second where each item is represented (by group or distinct) and then merge the results.

sql用于生成表

-- Languages
CREATE TABLE [Languages] (
    [id] INT NOT NULL PRIMARY KEY IDENTITY,
    [language] NVARCHAR(20) NOT NULL ,
    [languagecode] NVARCHAR(6) NOT NULL
);


-- Items
CREATE TABLE [Items] (
    [id] INT NOT NULL PRIMARY KEY IDENTITY,
    [ImageId] INT ,
    [lastupdate] DATETIME,
    [isactive] BIT NOT NULL DEFAULT 'TRUE'
);

-- ItemNames
CREATE TABLE [ItemNames] (
    [itemId] INT NOT NULL ,
    [languageId] INT NOT NULL ,
    [name] NVARCHAR(50) NOT NULL ,
    FOREIGN KEY (itemId) REFERENCES Items(id),
    FOREIGN KEY (languageId) REFERENCES Languages(id),
    PRIMARY KEY ([itemId],[languageId])
);

推荐答案

我会尝试使用 CASE 语句。基本上，想法是确定首选语言是否存在，如果不，返回 null 。这样，您可以使用 ISNULL 返回首选语言（填充时），否则返回最小语言ID：

I would try using a CASE statement. Basically the idea is to determine if the preferred language exists and return null if it does not. That way you can utilize ISNULL to returned the preferred language (when it is populated) otherwise return the minimum language ID:

// replace @preferred with the preferred language id ie 2
SELECT  itn.ItemID, itn.LanguageID, itn.Name
FROM    ItemNames itn INNER JOIN 
        (
            SELECT  itemID, 
                    // use preferred language if it exists
                    // otherwise, use the minimum languageID
                    ISNULL( MIN ( CASE WHEN LanguageID = @preferred THEN LanguageID ELSE NULL END ) 
                                , MIN (LanguageID) 
                          ) AS LanguageID
            FROM   ItemNames 
            GROUP BY itemID
        )
        sel ON sel.ItemID = itn.ItemID AND sel.LanguageID = itn.LanguageID 

这篇关于按列分组结果的查询（SQL Server）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！