Ok, this may sound a little weird, but what I need to do is simple. I have a list of companies and each one has a description. The site is in 4 languages. My problem comes when I have listed a company in English but the person who is in charge of translating it in French still didn't make the translation, so I have a simple:
好吧,这听起来有点奇怪,但我需要做的很简单。我有一个公司列表,每个公司都有描述。该网站有4种语言版本。我的问题来自于我用英语列出一家公司,但负责翻译法语的人仍然没有翻译,所以我有一个简单的说法:
SELECT TOP(6)
company.name,
company.description,
company.address
WHERE
langid=1
ORDER BY
company.id DESC
The real query is more complex, but I think it will be easier with this one to understand the problem. What I need to do is, when the description is empty in langid 2 then show me the langid 1 as langid 1 is always the first one that will be added. I don't want to show the description field empty.
真正的查询更复杂,但我认为用这个来理解问题会更容易。我需要做的是,当langid 2中的描述为空时,然后向我显示langid 1,因为langid 1始终是将要添加的第一个。我不想将描述字段显示为空。
Imagine row 1, row 2, row 3 and row 5 have a description for langid 2 but not row 4 and row 6. I need to show langid 2 for the rows containing it but langid 1 for the rows with no description.
想象一下,第1行,第2行,第3行和第5行都有langid 2的描述,但没有第4行和第6行的描述。我需要为包含它的行显示langid 2,但对于没有描述的行,则需要langid 1。
I know that I could probably do a while with a @i field incrementing and inserting the results one by one in a temp table, but is there any better way of doing it?
我知道我可能会在@i字段递增并在临时表中逐个插入结果的情况下做一段时间,但有没有更好的方法呢?
1 个解决方案
#1
This will work unless there is lng1.name etc. (i.e. the English language/fallback variant) missing.
除非缺少lng1.name等(即英语/后备变体),否则这将起作用。
SELECT TOP(6)
COALESCE(lng2.name, lng1.name) [name],
COALESCE(lng2.description, lng1.description) description,
COALESCE(lng2.address , lng1.address ) address
FROM
company lng1
LEFT JOIN company lng2 ON
lng1.id = lng2.id AND
lng1.langid = 1 AND
lng2.langid = 2 /* your "foreign language" id, e.g. a parameter */
WHERE
lng1.id IN (1,2,3,4,5,6) /* or whatever */
ORDER BY
lng1.id DESC
It will also not replace the entire address with the English/fallback version if only part of the localized variant is missing. Only the missing parts will be substituted.
如果只缺少部分本地化变体,它也不会用英语/后备版本替换整个地址。只有缺失的部分才会被替换。
#1
This will work unless there is lng1.name etc. (i.e. the English language/fallback variant) missing.
除非缺少lng1.name等(即英语/后备变体),否则这将起作用。
SELECT TOP(6)
COALESCE(lng2.name, lng1.name) [name],
COALESCE(lng2.description, lng1.description) description,
COALESCE(lng2.address , lng1.address ) address
FROM
company lng1
LEFT JOIN company lng2 ON
lng1.id = lng2.id AND
lng1.langid = 1 AND
lng2.langid = 2 /* your "foreign language" id, e.g. a parameter */
WHERE
lng1.id IN (1,2,3,4,5,6) /* or whatever */
ORDER BY
lng1.id DESC
It will also not replace the entire address with the English/fallback version if only part of the localized variant is missing. Only the missing parts will be substituted.
如果只缺少部分本地化变体,它也不会用英语/后备版本替换整个地址。只有缺失的部分才会被替换。