GROUP_BY 中错误，查询从两个数据库 CODEIGNITER

Question

>我有一个查询，它连接了来自两个不同数据库的表。它已经显示了结果，但后来我想只显示唯一的结果，因为有些结果是多余的。所以我添加了一个 GROUP BY 以仅获得唯一结果，但出现错误。

这是我的代码：

public function search_results_accommodations($location,$from_date,$to_date,$bedroom,$guests)
{
$this->db->select('*, akzapier.bookings.id as BOOKING_ID, akzapier.properties.id as PROPERTY_ID, ci_alexandrohomes.assigned_property.ID as ASSIGNED_PROPERTY_ID, ci_alexandrohomes.listings.ID as LISTING_ID');
$this->db->from('akzapier.bookings');
$this->db->join('akzapier.properties', 'akzapier.properties.id=akzapier.bookings.property_id', 'inner');
$this->db->join('ci_alexandrohomes.assigned_property', 'ci_alexandrohomes.assigned_property.property_id=akzapier.properties.id', 'inner');
$this->db->join('ci_alexandrohomes.listings', 'ci_alexandrohomes.listings.ID=ci_alexandrohomes.assigned_property.listing_id');
$this->db->where('akzapier.bookings.check_in !=', $from_date);
$this->db->where('akzapier.bookings.check_out !=', $to_date);
$this->db->where('ci_alexandrohomes.listings.city', $location);
$this->db->where('ci_alexandrohomes.listings.bedrooms', $bedroom);
$this->db->where('ci_alexandrohomes.listings.guests', $guests);
$this->db->group_by('akzapier.properties.id', 'ASC')
$query = $this->db->get();
return $query->result();
}

该错误未显示在页面中，因此我将其转换为SQL以查看实际交易：

SELECT * akzapier.bookings.id as BOOKING_ID, akzapier.properties.id as PROPERTY_ID, ci_alexandrohomes.assigned_property.ID as ASSIGNED_PROPERTY_ID, ci_alexandrohomes.listings.ID as LISTING_ID
FROM akzapier.bookings 
INNER JOIN akzapier.properties ON akzapier.properties.id=akzapier.bookings.property_id
INNER JOIN ci_alexandrohomes.assigned_property ON ci_alexandrohomes.assigned_property.property_id=akzapier.properties.id
INNER JOIN ci_alexandrohomes.listings ON ci_alexandrohomes.listings.ID=ci_alexandrohomes.assigned_property.listing_id
WHERE akzapier.bookings.check_in != '2019-09-21'
AND akzapier.bookings.check_out != '2019-09-30'
AND ci_alexandrohomes.listings.city = ‘1’
AND ci_alexandrohomes.listings.bedrooms = '2'
AND ci_alexandrohomes.listings.guests = '4'
GROUP BY akzapier.bookings.property_id ASC

错误说：

1055 - SELECT list 的表达式 #1 不在 GROUP BY 子句中，并且包含非聚合列 'akzapier.bookings.id'，该列在功能上不依赖于 GROUP BY 子句中的列;这与 sql_mode=only_full_group_by 不兼容

Answer 1

通常，当您使用 GROUP BY 时，您的 SELECT 语句必须包含聚合(如 MAX(...(、COUNT(...( 等(，或者列必须出现在 GROUP BY 中。您已选择所有字段(非聚合字段(，并带有星号 *。在这种情况下，它抱怨的是字段 akzapier.bookings.id，它既不是聚合的，也不是在您的 GROUP BY 中。

如果您确实想要唯一值，请尝试 SELECT DISTINCT，这将从结果中删除重复的行。