并排显示一条 Union 语句

Question

所以我有以下选择语句：

SELECT game_name, status, OS 

FROM mobile_version_sports 

UNION ALL 

SELECT game_name, status, OS 

FROM android_sports

这样我得到以下输出：

Dragons APPROVED    IOS

Dragons APPROVED    ANDROID

这样我就有了一个 PHP 页面，它可以显示所有内容并使用少量 CSS 来更改选项卡的颜色。在那个PHP中，我有以下代码：

 foreach($q as $row2){

    print "<tr><td>".$row2['game_name']."</td>";

    print "<td class=".$row2['mobile_version_sports.status'].">".$row2['OS.mobile_version_sports']." 

 </td>";

    print "<td class=".$row2['status.android_sports'].">".$row2['OS.android_sports']."</td></tr>";

}

我遇到了麻烦，因为我希望它用两个操作系统互相说出游戏的名称，然后根据状态和我的 CSS 改变游戏名称。编辑：我需要通过 PHP 以这种方式查看，以便在网页中查看而不是 SQL 调用 例如：

Dragons IOS ANDROID

Answer 1

你应该能够通过使用JOIN来得到你需要的东西。SELECT mobile_version_sports.game_name,&nbsp; &nbsp; &nbsp; &nbsp;mobile_version_sports.status AS mobile_version_sports_status,&nbsp; &nbsp; &nbsp; &nbsp;mobile_version_sports.OS AS mobile_version_sports_OS,&nbsp; &nbsp; &nbsp; &nbsp;android_sports.status AS android_sports_status,&nbsp; &nbsp; &nbsp; &nbsp;android_sports.OS AS android_sports_OSFROM mobile_version_sports&nbsp; &nbsp; &nbsp;JOIN android_sports&nbsp; &nbsp; &nbsp; &nbsp; ON mobile_version_sports.game_name = android_sports.game_nameSQL 小提琴：http ://sqlfiddle.com/#!9/31be6/1根据您的编辑，您的 PHP 代码将如下所示：foreach($q as $row2){&nbsp; &nbsp; print "<tr><td>".$row2['game_name']."</td>";&nbsp; &nbsp; print "<td class=".$row2['mobile_version_sports_status'].">".$row2['mobile_version_sports_OS']."&nbsp;&nbsp;</td>";&nbsp; &nbsp; print "<td class=".$row2['android_sports_status'].">".$row2['android_sports_OS']."</td></tr>";}这有望使您朝着正确的方向前进。

Answer 2

如您的评论所示，在这里做出一些假设：game_name可用作链接 2 个表的键mobile_version_sports仅持有 iOS 游戏android_sports只支持安卓游戏因此，有了这些假设，您可能会更幸运地使用这样的查询来执行类似于完全外连接的操作：SELECT i.game_name, i.status AS iOSStatus, a.status AS androidStatus,&nbsp;&nbsp; i.OS IS NOT NULL AS iOS,&nbsp; a.OS IS NOT NULL AS androidFROM mobile_version_sports i&nbsp; LEFT JOIN android_sports a ON a.game_name = i.game_nameUNIONSELECT a.game_name, i.status AS iOSStatus, a.status AS androidStatus,&nbsp; i.OS IS NOT NULL AS iOS,&nbsp; a.OS IS NOT NULL AS androidFROM mobile_version_sports i&nbsp; RIGHT JOIN android_sports a ON a.game_name = i.game_namePHP 代码示例：foreach($q as $row2){&nbsp; print "<tr><td>".$row2['game_name']."</td>";&nbsp; print "<td>";&nbsp; print $row2['iOS'] ? '<span class="' . $row2['iOSStatus'] . '">iOS</span>' : '';&nbsp; print $row2['android'] ? '<span class="' . $row2['androidStatus'] . '">Android</span>' : '';&nbsp; print "</td></tr>";}

Answer 3

如果 2 个表之间总是匹配 game_name，请尝试使用这个简短的代码。否则将左连接与一些 ifnull(a.status,'NOT') as a_status语句一起使用：&nbsp; &nbsp;SELECT m.game_name,&nbsp; &nbsp; &nbsp; &nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; m.OS AS m_OS,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; m.status AS m_status,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; a.OS AS a_OS,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; a.status AS a_status&nbsp; &nbsp; FROM mobile_version_sports as m&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;JOIN android_sports as a&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ON m.game_name = a.game_name group by m.game_name;