将多个字段连接到一个字段

Question

数据库名: table_name

table : abc
Remark1 Remark2 Remark3 Remark4 Remark5
1       2       3       4       5


table : xyz
Kod_type description    
1      xxxx 
2      yyyy 
3      zzzz 
4      aaaa 
5      bbbb 

如何将Remark1、Remark2、Remark3、Remark4、Remark5与kod_type结合起来？

Answer 1

你就像平常那样做-

SELECT ABC.*, XYZ.* FROM XYZ, ABC
WHERE 
XYZ.KOD_TYPE=ABC.REMARK1
AND XYZ.KOD_TYPE=ABC.REMARK2
AND XYZ.KOD_TYPE=ABC.REMARK3
AND XYZ.KOD_TYPE=ABC.REMARK4
AND XYZ.KOD_TYPE=ABC.REMARK5

如果你需要查询任何一个备注匹配的地方-

SELECT ABC.*, XYZ.* FROM XYZ, ABC
WHERE 
XYZ.KOD_TYPE=ABC.REMARK1
OR XYZ.KOD_TYPE=ABC.REMARK2
OR XYZ.KOD_TYPE=ABC.REMARK3
OR XYZ.KOD_TYPE=ABC.REMARK4
OR XYZ.KOD_TYPE=ABC.REMARK5

Answer 2

在连接两个表时，使用带有On子句的AND条件。

如果要匹配所有备注，则列应与Kod_Type匹配

SELECT abc.*, 
       xyz.* 
FROM   abc 
       INNER JOIN xyz 
               ON abc.Remark1 = xyz.Kod_Type 
                  AND abc.Remark2 =  abc.Remark1 
                  AND abc.Remark3 =  abc.Remark1
                  AND abc.Remark4 =  abc.Remark1 
                  AND abc.Remark5 =  abc.Remark1 

如果您想要其中任一注释列与Kod_Type匹配的记录

SELECT abc.*, 
           xyz.* 
    FROM   abc 
           INNER JOIN xyz 
                   ON abc.Remark1 = xyz.Kod_Type 
                      OR abc.Remark2 = xyz.Kod_Type 
                      OR abc.Remark3 = xyz.Kod_Type 
                      OR abc.Remark4 = xyz.Kod_Type 
                      OR abc.Remark5 = xyz.Kod_Type 

Answer 3

这个问题不是很清楚，但我认为这样的事情是故意的。

SELECT COALESC(d1.description, '') as description1
        , COALESC(d2.description, '') as description2
        , COALESC(d3.description, '') as description3
        , COALESC(d4.description, '') as description4
        , COALESC(d5.description, '') as description5
FROM abc
LEFT JOIN xyz d1 ON d1.kod_type=abc.remark1
LEFT JOIN xyz d2 ON d2.kod_type=abc.remark2
LEFT JOIN xyz d3 ON d3.kod_type=abc.remark3
LEFT JOIN xyz d4 ON d4.kod_type=abc.remark4
LEFT JOIN xyz d5 ON d5.kod_type=abc.remark5
        ;