的＆QUOT SQL选择;最＆QUOT;行 [英] SQL selection of "best" rows

问题描述

我有SQL一知半解，我在一个查询的麻烦太复杂了我。

I have a superficial knowledge of SQL and I'm in trouble with a query too complicated for me.

我的应用程序表名称和描述的一列，是IDS指正确的翻译。我有本地化的字符串表。

I have table of APPS with a column of name and description, that are ids to refer to the correct translation. And I have a table of localized Strings.

APPS（APP_ID，name_strid，description_strid）
STRINGS（str_id，LANG_ID，字符串）

APPS (app_id, name_strid, description_strid) STRINGS (str_id, lang_id, strings)

我需要返回所有的应用程序，以最优秀的翻译为每串查询。
最好的语言顺序（让我说：这，这，这，EN）

I need a query that return all apps, with the best translation for each strings. best in a language order (let me say: it-it, it, en)

我达到了一个解决方案，使所有语言Apps订单：

I reached a solution to get all apps order by languages:

SELECT A.app_id, S1.string, S2.string
FROM APPS as A
JOIN STRINGS AS S1
ON A.name_strid = S1.str_id
JOIN STRINGS AS S2
ON A.description_strid = S2.str_id
WHERE S1.lang_id = S2.lang_id
AND S1.lang_id IN ("it-it", "it", "en")
ORDER BY
   CASE S1.lang_id
      WHEN "it-it" THEN 1
      WHEN "it" THEN 2
      WHEN "en" THEN 3
      ELSE 4
   END;

我怎么能只获得最好的语言？

How I can obtain only the best language?

感谢

推荐答案

在SQL的大多数方言中，您将解决这个使用 ROW_NUMBER（），但你不知道有这样的选择。解决这个的一个方法是使用相关子查询 - 这翻出每个第一个匹配的语言 str_id ，然后你就可以使用过滤

In most dialects of SQL, you would solve this using row_number(), but you don't have that option. One method of solving this is with a correlated subquery -- this pulls out the first matching language for each str_id, which you can then use for filtering.

SELECT A.app_id, S1.string, S2.string
FROM APPS as A JOIN
     STRINGS AS S1
     ON A.name_strid = S1.str_id JOIN
     STRINGS AS S2
     ON A.description_str_id = S2.str_id AND
        S1.lang_id = S2.lang_id
WHERE S1.lang_id IN ('it-it', 'it', 'en') AND
      S1.lang_id = (SELECT s3.LangId
                    FROM Strings s3
                    WHERE s3.str_id = S1.str_id
                    ORDER BY (CASE S3.lang_id
                                  WHEN 'it-it' THEN 1
                                  WHEN 'it' THEN 2
                                  WHEN 'en' THEN 3
                                  ELSE 4
                              END)
                   LIMIT 1
                  );

这篇关于的＆QUOT SQL选择;最＆QUOT;行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！