我目前将这个左连接作为查询的一部分:
LEFT JOIN movies t3 ON t1.movie_id = t3.movie_id AND t3.popularity = 0
麻烦的是,如果有几部具有相同名称和相同受欢迎程度的电影(不要问,它就是这样:-)),那么返回重复的结果。
综上所述,我想将左联接的结果限制为一个。
我试过这个:
LEFT JOIN
(SELECT t3.movie_name FROM movies t3 WHERE t3.popularity = 0 LIMIT 1)
ON t1.movie_id = t3.movie_id AND t3.popularity = 0
第二个查询因错误而死亡:
Every derived table must have its own alias
我知道我问的有点模糊,因为我没有提供完整的查询,但我问的通常是可能的吗?
错误很明显——你只需要在子查询的结束)
之后为子查询创建一个别名,并在你的ON
子句中使用它,因为每个表,无论是派生的还是实数的,都必须有自己的标识符。然后,您需要在子查询的选择列表中包含movie_id
才能加入它。 由于子查询已经包含 WHERE popularity = 0
,因此不需要将其包含在连接的 ON
子句中。
LEFT JOIN (
SELECT
movie_id,
movie_name
FROM movies
WHERE popularity = 0
ORDER BY movie_name
LIMIT 1
) the_alias ON t1.movie_id = the_alias.movie_id
如果在外部SELECT
中使用这些列之一,例如通过the_alias.movie_name
引用它。
更好地了解需求后更新:
若要为每个组获取一个要联接的聚合,可以在movie_id
上使用聚合MAX()
或MIN()
,并将其分组到子查询中。然后不需要子查询LIMIT
- 您将收到每个名称的第一个movie_id
MIN()
或最后一个带有MAX()
。
LEFT JOIN (
SELECT
movie_name,
MIN(movie_id) AS movie_id
FROM movies
WHERE popularity = 0
GROUP BY movie_name
) the_alias ON t1.movie_id = the_alias.movie_id
LEFT JOIN movies as m ON m.id = (
SELECT id FROM movies mm WHERE mm.movie_id = t1.movie_id
ORDER BY mm.id DESC
LIMIT 1
)
您可以尝试将GROUP BY t3.movie_id
添加到第一个查询中
在 MySQL 5.7+ 上,请使用 ANY_VALUE & GROUP_BY
:
SELECT t1.id,t1.movie_name, ANY_VALUE(t3.popularity) popularity
FROM t1
LEFT JOIN t3 ON (t3.movie_id=t1.movie_id AND t3.popularity=0)
GROUP BY t1.id
更多信息仅左连接第一行
https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html
试试这个:
LEFT JOIN
(
SELECT t3.movie_name, t3.popularity
FROM movies t3 WHERE t3.popularity = 0 LIMIT 1
) XX
ON t1.movie_id = XX.movie_id AND XX.popularity = 0
LEFT JOIN (
SELECT id,movie_name FROM movies GROUP BY id
) as m ON (
m.id = x.id
)
左连接 1 个最近/最少行的简单解决方案是使用 ON 短语选择
SELECT A.ID, A.Name, B.Content
FROM A
LEFT JOIN B
ON A.id = (SELECT MAX(id) FROM B WHERE id = A.id)
其中 A.id 是自动增量主键。
// Mysql
SELECT SUM(db.item_sales_nsv) as total FROM app_product_hqsales_otc as db
LEFT JOIN app_item_target_otc as it ON
db.id = (SELECT MAX(id) FROM app_item_target_otc as ot WHERE id = db.id)
and db.head_quarter = it.hqcode
AND db.aaina_item_code = it.aaina_item_code AND db.month = it.month
AND db.year = it.year
WHERE db.head_quarter = 'WIN001' AND db.month = '5' AND db.year = '2022' AND db.status = '1'