mysql - 将两张表合并为一张表,无需使用连接,也无需创建笛卡尔积
问题描述
如何在不创建笛卡尔积的情况下制作两个不同评级的结果表。diffrating 和 hostrating 是两种不同类型的用户评级。用户正在对标有 spotId 的点进行评分。userdiffrating 和 userhostrating 将用户评分链接到正确的位置。我正在尝试获取表格中特定位置的所有评级。示例数据和预期输出如下。
表格内容:
用户差异化:
-------------------------------
| RatingId | userId | spotId |
-------------------------------
| 1 | 1 | 1 |
-------------------------------
| 2 | 2 | 1 |
-------------------------------
| 3 | 1 | 2 |
-------------------------------
发散:
----------------------
| RatingId | userId |
----------------------
| 1 | 5 |
----------------------
| 2 | 2 |
----------------------
| 3 | 4 |
----------------------
用户主机评级:
-------------------------------
| RatingId | userId | spotId |
-------------------------------
| 1 | 1 | 1 |
-------------------------------
| 2 | 2 | 1 |
-------------------------------
| 3 | 1 | 1 |
-------------------------------
主持人评价:
----------------------
| RatingId | userId |
----------------------
| 1 | 1 |
----------------------
| 2 | 3 |
----------------------
| 3 | 4 |
----------------------
这是我最初尝试的,但这会创建一个笛卡尔积:
SELECT D.rating diffrating, H.rating hostrating FROM diffrating D
JOIN userdiffrating UD ON D.ratingId = UD.ratingId
JOIN userhostrating UH ON UD.spotId = UH.spotId
JOIN hostrating H ON UH.ratingId = H.ratingId WHERE UD.spotId = 1
第一次查询的结果(笛卡尔积):
-------------------------
| diffrating| hostrating|
-------------------------
| 5 | 1 |
-------------------------
| 5 | 3 |
-------------------------
| 5 | 4 |
-------------------------
| 2 | 1 |
-------------------------
| 2 | 3 |
-------------------------
| 2 | 4 |
-------------------------
我尝试了下一个查询,但我不能使用具有多于一行的选择语句作为子查询:
SELECT D.rating AS diffrating, H.rating AS hostrating
FROM diffrating D, hostrating H
WHERE D.ratingId = (SELECT ratingId FROM userdiffrating UD WHERE UD.spotId = 1)
AND H.ratingId = (SELECT ratingId FROM userhostrating UH WHERE UH.spotId = 1)
这是预期的结果(spotId = 1 的所有差异和主机评级):
-------------------------
| diffrating| hostrating|
-------------------------
| 5 | 1 |
-------------------------
| 2 | 3 |
-------------------------
| NULL | 4 |
-------------------------
这可能吗?如何做到这一点?谢谢
解决方案
我认为您要编写的代码是
SELECT dr.rating diffratingval, hr.rating hostratingval
FROM diffrating dr
JOIN userdiffrating udr
ON dr.ratingid = udr.ratingid
JOIN userhostrating uhr
ON udr.spotid = uhr.spotid
JOIN hostrating hr
ON uhr.ratingid = hr.ratingid
AND dr.ratingid = hr.ratingid
WHERE udr.spotid = 60;
推荐阅读
- java - 如何使用 CriteriaBuilder 调用 db 函数
- java - 如何在不重复使用弹簧靴的情况下将两个日期参数用于两个不同的日期?
- c# - 从函数参数设置动作为空
- scala - PySpark 中的状态聚合函数
- sql-server - 回滚时重新启动 SQL Server
- javascript - 来自 Promise React Native 的结果
- javascript - React-redux 将 HOC 连接为类装饰器,@connect
- javascript - 按子字符串排序表
- android - 如何管理多个 BLE writeCharacteristic 和 readCharacteristic 调用?
- php - 查询中的 MethodID 重复