我有一个类似如下的数据库结构:
SELECT * FROM Culture;
------------------------
Id ShortName FullName Supported
22 en-US English (United States) 1
23 fr-FR French (France) 1
24 hi-IN Hindi (India) 0
SELECT * FROM ResourceKey;
----------------------------
Id Name
20572 HowAreYou
20571 Hello
SELECT * FROM Strings;
-----------------------
Id CultureId ResourceKeyId ResourceValue
41133 22 20571 Hello
41134 22 20572 How are you?
41135 23 20571 Bonjour
41136 23 20572 Comment allez-vous?
SELECT * FROM Category;
------------------------
Id Name
1 JavaScript
SELECT * FROM StringCategory;
------------------------------
Id ResourceKeyId CategoryId
1 20571 1
2 20572 1
我需要一条SQL查询语句,能够给我以下结果:
Key CategoryName en-US fr-FR another-SupportedCulture And so on
-------------------------------------------------------------------------------------------------------------------------------------------------------------
Hello JavaScript Hello Bonjour Value of hello in that supported culture and so on for all supported cultures
HowAreYou JavaScript How are you? Comment allez-vous? Value of HowAreYou in that supported culture and so on for all supported cultures
我知道如何为单个文化编写查询。我打算为表中所有具有"Supported"字段设置为1(true)的文化编写单独的查询。然后,我将在英语文化(其中将包含所有值)和所有其他文化之间应用LEFT OUTER JOIN以获取上述数据集。
到目前为止,我已经为单个文化编写了查询:
SELECT
ResourceKey.Id AS ResourceKeyId,
ResourceKey.Name AS [Key],
Category.Name AS CategoryName,
Culture.Id AS CultureId,
Culture.ShortName AS CultureShortName,
Strings.ResourceValue
FROM
ResourceKey LEFT OUTER JOIN Strings ON ResourceKey.Id = Strings.ResourceKeyId
INNER JOIN Culture ON Strings.CultureId = Culture.Id
LEFT OUTER JOIN StringCategory ON ResourceKey.Id = StringCategory.ResourceKeyId
LEFT OUTER JOIN Category ON StringCategory.CategoryId = Category.Id
WHERE Strings.CultureId = 23; -- this will be a T-SQL stored procedure parameter I understand
这会返回以下结果:
ResourceKeyId Key CategoryName CultureId CultureShortName ResourceValue
----------------------------------------------------------------------------------
20571 Hello JavaScript 23 fr-FR Bonjour
20572 HowAreYou JavaScript 23 fr-FR Comment allez-vous?
事实是:我不确定如何编写T-SQL中的循环,然后将这些单独的数据集存储在临时内存区域中(我相信有一个术语来描述它,对于一些表类型表达式,它们驻留在内存中,仅在T-SQL批处理的持续时间内,并且只适用于我的情况),然后在SQL进程中应用联接。那就是我需要帮助的地方。
感谢您耐心阅读我的问题并提供帮助。
我正在使用Microsoft SQL Server 2008 Express R2。
tempdb
中,页面可能会被写入磁盘。 - Martin Smith