我正在使用下面的代码来获取一个航程类型及其各自航程的分组列表。
public function getVoyageTypesWithTrips() {
//query
$this->db->query('
SELECT voyagetype_name
, voyagetype_description
, voyagetype_image
, voyage_id
, voyage_name
, voyage_startDate
FROM voyagetypes
LEFT
JOIN voyages
ON voyagetypes.voyagetype_id = voyages.voyage_type
WHERE voyagetype_deleted != 1
');
//get the results
$results = $this->db->resultSetGrouped();
return $results;
}
//get results set as array of objects - grouped
public function resultSetGrouped() {
$this->execute();
return $this->statement->fetchAll(PDO::FETCH_GROUP);
}
我想做的是限制voyages表只显示到今天为止最接近的3条记录，而不是返回该类型的所有航次。
所以回归
我最初尝试了ORDER，BY和LIMIT，但我认为这与pdo::FETCH_GROUP不兼容。
我相信我需要我的SQL顺序& 在发送到FETCH_GROUP？？？之前限制联接的表 所以类似于
$this->db->query('
SELECT voyagetype_name
, voyagetype_description
, voyagetype_image
, voyage_id
, voyage_name
, voyage_startDate
FROM voyagetypes
LEFT
JOIN voyages
ON voyagetypes.voyagetype_id = voyages.voyage_type
APPLY THE SORT AND LIMIT TO THE JOINED TABLE
WHERE voyagetype_deleted != 1
');
一种选择是使用子查询进行筛选:
select vt.voyagetype_name, vt.voyagetype_description, vt.voyagetype_image, v.voyage_id, v.voyage_name, v.voyage_startdate
from voyagetypes vt
left join voyages v
on v.voyagetype_id = vt.voyage_type
and (
select count(*)
from voyages v1
where
v1.voyage_type = vt.voyage_type
and v1.voyage_startdate > v.voyage_startdate
) < 3
where vt.voyagetype_deleted <> 1
或者，如果您正在运行MySQL8.0，您可以只使用rank():
select *
from (
select
vt.voyagetype_name,
vt.voyagetype_description,
vt.voyagetype_image,
v.voyage_id,
v.voyage_name,
v.voyage_startdate,
rank() over(partition by vt.voyage_type order by v.voyage_startdate desc) rn
from voyagetypes vt
left join voyages v on v.voyagetype_id = vt.voyage_type
where vt.voyagetype_deleted <> 1
) t
where rn <= 3
