我读了很多关于只获取左连接的第一行的帖子,但是,由于某种原因,这对我不起作用。
这是我的结构(当然是简化的)
饲料
id | title | content
----------------------
1 | Feed 1 | ...
艺术家
artist_id | artist_name
-----------------------
1 | Artist 1
2 | Artist 2
feeds_artists
rel_id | artist_id | feed_id
----------------------------
1 | 1 | 1
2 | 2 | 1
...
现在我想获取文章并只加入第一个艺术家,我想到了这样的事情:
SELECT *
FROM feeds
LEFT JOIN feeds_artists ON wp_feeds.id = (
SELECT feeds_artists.feed_id FROM feeds_artists
WHERE feeds_artists.feed_id = feeds.id
LIMIT 1
)
WHERE feeds.id = '13815'
只是为了获得 feeds_artists 的第一行,但这已经不起作用了。
由于我的数据库,我不能使用 TOP
并且我不能按 feeds_artists.artist_id
对结果进行分组,因为我需要按日期对它们进行排序(我通过这种方式对它们进行分组得到了结果,但结果不是最新的)
也尝试了 OUTER APPLY - 也没有成功。老实说,我真的无法想象这些行中发生了什么——这可能是我无法让它工作的最大原因。
解决方案:
SELECT *
FROM feeds f
LEFT JOIN artists a ON a.artist_id = (
SELECT artist_id
FROM feeds_artists fa
WHERE fa.feed_id = f.id
LIMIT 1
)
WHERE f.id = '13815'
如果您可以假设艺术家 ID 随着时间的推移而增加,那么 MIN(artist_id)
将是最早的。
所以尝试这样的事情(未经测试......)
SELECT *
FROM feeds f
LEFT JOIN artists a ON a.artist_id = (
SELECT
MIN(fa.artist_id) a_id
FROM feeds_artists fa
WHERE fa.feed_id = f.feed_id
) a
没有子选择的版本:
SELECT f.title,
f.content,
MIN(a.artist_name) artist_name
FROM feeds f
LEFT JOIN feeds_artists fa ON fa.feed_id = f.id
LEFT JOIN artists a ON fa.artist_id = a.artist_id
GROUP BY f.id
@Matt Dodges 的回答让我走上了正轨。再次感谢所有答案,同时帮助了很多人。让它像这样工作:
SELECT *
FROM feeds f
LEFT JOIN artists a ON a.artist_id = (
SELECT artist_id
FROM feeds_artists fa
WHERE fa.feed_id = f.id
LIMIT 1
)
WHERE f.id = '13815'
f.id
条件时中断。
基于这里的几个答案,我发现了一些对我有用的东西,我想概括并解释正在发生的事情。
兑换:
LEFT JOIN table2 t2 ON (t2.thing = t1.thing)
至:
LEFT JOIN table2 t2 ON (t2.p_key = (SELECT MIN(t2_.p_key)
FROM table2 t2_ WHERE (t2_.thing = t1.thing) LIMIT 1))
连接 t1 和 t2 的条件从 ON
移动到内部查询 WHERE
。 MIN(primary key)
或 LIMIT 1
确保内部查询只返回 1 行。
选择一个特定行后,我们需要告诉 ON
它是哪一行。这就是 ON
比较连接表的主键的原因。
您可以使用内部查询(即 order+limit),但它必须返回所需行的一个主键,这将告诉 ON
要加入的确切行。
更新 - 适用于 MySQL 5.7+
与 MySQL 5.7+ 相关的另一个选项是使用 ANY_VALUE
+GROUP BY
。它将选择一个不一定是第一个的艺术家姓名。
SELECT feeds.*,ANY_VALUE(feeds_artists.name) artist_name
FROM feeds
LEFT JOIN feeds_artists ON feeds.id = feeds_artists.feed_id
GROUP BY feeds.id
有关 ANY_VALUE 的更多信息:https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html
LIMIT
或仅适用于 MIN
。 ON
条件必须在连接表主键上以避免性能影响。
我用过别的东西(我觉得更好......)并想分享它:
我创建了一个具有“组”子句的 VIEW
CREATE VIEW vCountries AS SELECT * PROVINCES GROUP BY country_code
SELECT * FROM client INNER JOIN vCountries on client_province = province_id
我想说的是,我认为我们需要做这个解决方案,因为我们在分析中做错了什么......至少在我的情况下......但有时这样做更便宜重新设计一切......
我希望它有帮助!
country_code
有多个行(省?),那么 GROUP BY
是不合适的。见ONLY_FULL_GROUP_BY
。
WITH x AS (
子句吗?
SELECT *
。相反,选择您需要的确切字段,仅此而已。它将允许优化器更好地工作,您很可能会看到性能提升。您可以通过 EXPLAIN
看到不同之处。
这是我使用 group by 子句的答案。
SELECT *
FROM feeds f
LEFT JOIN
(
SELECT artist_id, feed_id
FROM feeds_artists
GROUP BY artist_id, feed_id
) fa ON fa.feed_id = f.id
LEFT JOIN artists a ON a.artist_id = fa.artist_id
我想给出一个更笼统的答案。当您只想选择 LEFT JOIN 中的第一项时,它可以处理任何情况。
您可以使用 GROUP_CONCATS 的子查询(也已排序!),然后只需拆分 GROUP_CONCAT 的结果并仅获取其第一项,就像这样...
LEFT JOIN Person ON Person.id = (
SELECT SUBSTRING_INDEX(
GROUP_CONCAT(FirstName ORDER BY FirstName DESC SEPARATOR "_" ), '_', 1)
) FROM Person
);
由于我们将 DESC 作为我们的 ORDER BY 选项,因此这将为像“Zack”这样的人返回一个 Person id。如果我们想要一个名字像“Andy”的人,我们会将 ORDER BY FirstName DESC 更改为 ORDER BY FirstName ASC。
这是灵活的,因为这将订购的权力完全掌握在您的手中。但是,经过大量测试,它在用户和数据量很大的情况下无法很好地扩展。
但是,它在为管理员运行数据密集型报告时很有用。
GROUP_CONCAT
技巧就很好。默认限制为 1024 个字符。
"_"
的名称,因此我建议使用一些不可打印的字符作为分隔符。
对于像 DB2 和 PostgreSQL 这样的数据库,您必须使用关键字 LATERAL
在 LEFT JOIN
中指定子查询:(这里是针对 DB2)
SELECT f.*, a.*
FROM feeds f
LEFT JOIN LATERAL
(
SELECT artist_id, feed_id
FROM feeds_artists sfa
WHERE sfa.feed_id = f.id
fetch first 1 rows only
) fa ON fa.feed_id = f.id
LEFT JOIN artists a ON a.artist_id = fa.artist_id
我知道这不是一个直接的解决方案,但是当我遇到这个问题时,这对我来说总是一个大问题,而且使用左连接选择等有时会导致数据库和服务器的处理成本很高,我更喜欢做这种在 php 中使用数组左连接,如下所示:
首先从第二个表中获取范围内的数据,而您只需要第二个表中的一行,只需将它们与左连接公共列作为结果数组中的键一起保存。
SQL1:
$sql = SELECT artist_id FROM feeds_artists fa WHERE fa.feed_id {...RANGE...}
$res = $mysqli->query($sql);
if ($res->num_rows > 0) {
while ($row = $res->fetch_assoc()) {
$join_data[...$KEY...] = $row['artist_id'];
}
然后,获取基础数据并从前一个数组中添加左连接表的详细信息,同时获取它们,如下所示:
SQL2:
$sql = SELECT * FROM feeds f WHERE f.id {...RANGE...};
$res = $mysqli->query($sql);
if ($res->num_rows > 0) {
while ($row = $res->fetch_assoc()) {
$key = $row[in_common_col_value];
$row['EXTRA_DATA'] = $join_data[$key];
$final_data[] = $row;
}
现在,您将拥有一个 $final_data 数组,其中包含来自 $join_data 数组的额外数据。这通常适用于日期范围数据,就像这样。
不定期副业成功案例分享