SQL查询以获得期望的结果

问题描述：

我有2个表。我要为输出MySQL查询中的截图：SQL查询以获得期望的结果

enter image description here

有什么解决方案吗？

考虑向我们提供您迄今为止所尝试的内容，以及您使用的是什么样的SQL Enginge也会有所帮助。 – Jeredepp 2014-10-08 07:35:11

您需要使用'UNION'运算符来处理此类查询。 – mochalygin 2014-10-08 07:36:10

@Jeredepp他可以通过'UNION'操作员轻松获得这样的输入。 – mochalygin 2014-10-08 07:45:10

答

试试这个：

SELECT Table_1.Firstname, Table_2.Lastname, Table_2.RF1 AS 'RaterFirst', Table_2.RL1 AS 'RaterLast' FROM Table_1, Table_2 WHERE (Table_1.ID = Table_2.ID) 
UNION 
SELECT Table_1.Firstname, Table_2.Lastname, Table_2.RF2 AS 'RaterFirst', Table_2.RL2 AS 'RaterLast' FROM Table_1, Table_2 WHERE (Table_1.ID = Table_2.ID) 
UNION 
SELECT Table_1.Firstname, Table_2.Lastname, Table_2.RF3 AS 'RaterFirst', Table_2.RL3 AS 'RaterLast' FROM Table_1, Table_2 WHERE (Table_1.ID = Table_2.ID)

但我想告诉你，这是不是在设计数据库表TABLE_2 你必须保持多行记录每个ID的正确方法。检查First Normal Form

可能是'ORDER BY Table_1.Firstname，Table_2.Lastname'也需要使用。 – mochalygin 2014-10-08 07:47:25

答

您可以使用此：

Select Firstname, Lastname, Table_2.RF1 AS Raterfirst, Table_2.RL1 AS Raterlast 
From Table_1 
Inner Join Table_2 
ON Table_1.ID = Table_2.ID 
WHERE Raterfirst <> '' OR Raterlast <> '' 

UNION 

Select Firstname, Lastname, Table_2.RF2 AS Raterfirst, Table_2.RL2 AS Raterlast 
From Table_1 
Inner Join Table_2 
ON Table_1.ID = Table_2.ID 
WHERE Raterfirst <> '' OR Raterlast <> '' 

UNION 

Select Firstname, Lastname, Table_2.RF3 AS Raterfirst, Table_2.RL3 AS Raterlast 
From Table_1 
Inner Join Table_2 
ON Table_1.ID = Table_2.ID 
WHERE Raterfirst <> '' OR Raterlast <> ''

您可以优化SQL命令，例如只使用一个Where表达。

SQL查询以获得期望的结果

相关推荐