具有唯一字段值计数的 Mysql 选择查询

Question

我的查询是从数据库中获取联系人:

$contacts = $dbh->prepare("
SELECT r.title region_title
     , c.title
     , c.region_id
     , c.id
     , c.catalog_id
     , c.address
     , c.phone
     , c.email
     , c.website
     , c.category_title
     , c.subcategory_title
     , c.subcategory_id
     , c.manufacturer 
  FROM contacts c 
  LEFT 
  JOIN regions r 
    ON c.region_id = r.id 
 WHERE manufacturer = 1 
   AND region_id IN (".implode(',', $regions).") 
   AND subcategory_id IN (".implode(',', $categories).")
");

并获取分组购买region_title的联系人列表

$contacts->fetchAll(PDO::FETCH_GROUP)

结果是:

array() {
  ["First region title"]=>
  array() {
    contact1
    contact2
    ...
  },
  ["Second region title"]=>
  array() {
    contact3
    contact4
    ...
  }
}

如何获取同一查询中唯一 c.catalog_id 字段的数量？

Answer 1

您可以通过按catalog_id分组来获取唯一的catalog_id字段，并设置具有条件如下:

$contacts = $dbh->prepare("
SELECT r.title region_title
 , c.title
 , c.region_id
 , c.id
 , c.catalog_id
 , c.address
 , c.phone
 , c.email
 , c.website
 , c.category_title
 , c.subcategory_title
 , c.subcategory_id
 , c.manufacturer 
 FROM contacts c 
LEFT 
  JOIN regions r 
ON c.region_id = r.id 
 WHERE manufacturer = 1 
 AND region_id IN (".implode(',', $regions).") 
 AND subcategory_id IN (".implode(',', $categories).")
 GROUP BY catalog_id HAVING COUNT(catalog_id)=1
");