SQL将具有多行的左连接插入一行 [英] SQL left join with multiple rows into one row

问题描述

基本上，我有两个表，表A包含了我关心的实际项目，表B用于语言翻译。

例如，表A包含实际内容。在表中使用任何时间的文本，而不是存储实际的varchar值，存储的id与表B中存储的文本相关。这允许我通过在表B中添加一个languageID列，对数据库。

示例：

表A

• 标题（int）


• 说明（int）


• 其他资料....

表B

• TextID（int）
  其值存储在其他表中


• LanguageID（int）


• 文本（varchar）
/ ul>



我的问题是更多的建议，如何最好地处理这个问题。理想情况下，我想要一个查询，我可以使用从表中选择，并获得文本，而不是表中的文本的ID。目前，当我在表中有两个文本项时，这是我所做的：

  SELECT C.ID，C.Title，D .Text AS描述
 FROM 
（SELECT A.ID，A.Description，B.Text AS标题
 FROM TableA A，TranslationsTable B 
 WHERE A.Title = B.TextID AND B.LanguaugeID = 1）C 
 LEFT JOIN翻译表D 
 ON C.Description = D.TextID AND D.LanguaugeID = 1 
  

这个查询给了我从表AI中查找（使用where select语句中的语句）的行与基于使用的语言ID而不是文本id的实际文本。





这样工作正常，当我只使用一个或两个文本项，需要翻译，但添加第三个项目或更多，它开始变得很杂乱 -





有关更好的查询的建议，或者至少是在单行中处理3个或更多文本项的好方法吗？





根据建议，我添加了两个表格的示例：

 表A 
 --------------------------- 
 ID |标题|说明
  - -------------------------- 
 1 | 1 | 2 
 ----------- ---------------- 
 2 | 3 | 4 
 --------------------- ------ 
 
表B（翻译表）
 -------------------------- -  
 ID | LanguaugeID | Text 
 --------------------------- 
 1 | 1 |此处是title one 
 --------------------------- 
 1 | 2 |这是espanol $中的一个标题b $ b --------------------------- 
 2 | 1 |这里是描述一个
 ---- ----------------------- 
 2 | 2 |这里是描述一个在espanol 
 --------- ------------------ 
 3 | 1 |标题2 
 ------------------ --------- 
 4 | 1 |说明2 
 --------------------------- 
  

我想要的是能够从表A中拉出一行，

解决方案

查看如何使用一个为需要翻译的每一列返回数据的函数。一个可能是：

  CREATE FUNCTION dbo.fTranslate 
（@TextId int，@LanguageId int）
 RETURNS nvarchar（100） - 应使用nvarchar，并设置字符串的最大长度
 AS 
 BEGIN 
 SELECT [Text]  - 保留字SQL，重命名该列！ 
 FROM TableB 
 WHERE TextId = @TextId 
和LanguageId = @LanguageId 
 END 
  

然后将查询写为：

  SELECT 
 ID，
 dbo .fTranslate（Title，@LanguageId）Title，
 dbo.fTranslate（Description，@LanguageId）说明
 FROM TableA 
  

这可能不会表现特别好，因为你必须为每个返回的每一行调用该函数一次（即100行= 300个函数调用的3列），但如果你一次只返回一行，它可能不会执行这么差。测试它，并保持警惕。




Basically, I have two tables, Table A contains the actual items that I care to get out, and Table B is used for language translations.

So, for example, Table A contains the actual content. Anytime text is used within the table, instead of storing actual varchar values, ids are stored that relate back to text stored in Table B. This allows me to by adding a languageID column to Table B, have multiple translations for the same row in the database.

Example:

Table A

• Title (int)
• Description (int)
• Other Data....

Table B

• TextID (int) - This is the column whose value is stored in other tables
• LanguageID (int)
• Text (varchar)

My question is more a call for suggestions on how to best handle this. Ideally I want a query that I can use to select from the table, and get the text as opposed to the ids of the text out of the table. Currently when I have two text items in the table this is what I do:

SELECT C.ID, C.Title, D.Text AS Description
FROM
(SELECT A.ID, A.Description, B.Text AS Title
FROM TableA A, TranslationsTable B
WHERE A.Title = B.TextID AND B.LanguaugeID = 1) C
LEFT JOIN TranslationsTable D
ON C.Description = D.TextID AND D.LanguaugeID = 1

This query gives me the row from Table A I am looking for (using where statements in the inner select statement) with the actual text based on the language ID used instead of the text ids.

This works fine when I am only using one or two text items that need to be translated, but adding a third item or more, it starts to get really messy - essentially another left join on top of the example.

Any suggestions on a better query, or at least a good way to handle 3 or more text items in a single row?

Per suggestions, I've added an example of the two tables:

    Table A  
    ---------------------------
    ID    |Title    |Description  
    ---------------------------  
    1     |1        |2  
    ---------------------------  
    2     |3        |4  
    --------------------------- 

    Table B (Translations Table) 
    ---------------------------
    ID    |LanguaugeID|Text  
    ---------------------------  
    1     |1        |Here is title one
    ---------------------------  
    1     |2        |Here is a title one in espanol
    ---------------------------
    2     |1        |Here is description one
    ---------------------------  
    2     |2        |Here is description one in espanol
    ---------------------------
    3     |1        |Title 2
    ---------------------------  
    4     |1        |Description 2
    ---------------------------   

What I want is to be able to pull a row out of table A that already has the text from table B, not just the ids - and to be able to do this for several columns that need translations.

解决方案

Look into using a function to return data for each column that requires translation. One could be:

CREATE FUNCTION dbo.fTranslate
    (@TextId int, @LanguageId int)
RETURNS nvarchar(100)  --  Should use nvarchar, and set to max length of string
AS
 BEGIN
    SELECT [Text]  --  Reserved wordin SQL, rename that column!
     FROM TableB
     WHERE TextId = @TextId
      And LanguageId = @LanguageId
 END

Then write the query as:

SELECT
  ID,
  dbo.fTranslate(Title, @LanguageId) Title,
  dbo.fTranslate(Description, @LanguageId) Description
 FROM TableA

This might not perform particularly well, as you have to call the function once for each translated column for each row returned (i.e. 3 columns for 100 rows = 300 function calls), but if you’re only returning one row at a time, it might not perform so poorly. Test it and be wary.

这篇关于SQL将具有多行的左连接插入一行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！