php - 使用 SELECT DISTINCT COUNT 在数据库中获取错误的数量

Question

我正在跟踪卖家何时以低于特定价格的价格在亚马逊上销售产品。我在数据库中存储产品、卖家和违规行为。我试图显示 24 小时内违规的卖家数量。我没有显示违规卖家的数量，而是收到了违规总数。

ViolationsRepository.php

public function countNumberOfSellersInViolationsForVendorInLast24Hours($vendorId)
{
    $select = "SELECT DISTINCT COUNT(
        CASE
        WHEN v.source = 'amazon' THEN
          (SELECT DISTINCT s.id
           FROM seller_info_amazon AS sai
             LEFT JOIN sellers_amazon AS sa ON sa.amazon_id = sai.id
             LEFT JOIN sellers AS s ON sa.seller_id = s.id
           WHERE sai.unique_id = v.seller_id_amazon AND s.id IS NOT NULL
           LIMIT 1)
        WHEN v.source = 'ebay' THEN
          (SELECT s.id
           FROM sellers AS s
           WHERE s.id = v.seller_id_ebay
           LIMIT 1)
        WHEN v.source = 'google' THEN
          (SELECT s.id
           FROM sellers AS s
           WHERE s.id = v.seller_id_google
           LIMIT 1)
        END)
    FROM
      violations AS v

    ";

    $timeLimit = new \DateTime('24 hours ago');

    $where = " WHERE v.vendor_id = :vendorId AND v.last_scout_date >= :timeLimit \n";


    $query = $this->getEntityManager()->getConnection()->prepare($select . $where );
    $query->bindValue(':vendorId', $vendorId);
    $query->bindValue(':timeLimit', $timeLimit->format("Y-m-d h:i:s"));

    $query->execute();

    return $query->fetchColumn();

}

sellers_amazon 表

seller_id   amazon_id

违规表

id      vendor_id   seller_id_amazon    last_scout_date source  

seller_info_amazon 表

id  unique_id   name

卖家表

id   originating_vendor_id   name   

Answer 1

解决了这个 - SELECT COUNT(DISTINCT ... 而不是 SELECT DISTINCT COUNT( ...。