php - 尝试添加一些条件来排除某些结果

Question

我试图在 mysql 查询中添加一些条件，但我不确定为什么它不起作用，这是我试图修改的原始代码

public function getAffiliates($data = array()) {
    $sql = "SELECT *, CONCAT(a.firstname, ' ', a.lastname) AS name, (SELECT SUM(at.amount) FROM " . DB_PREFIX . "affiliate_transaction at WHERE at.affiliate_id = a.affiliate_id GROUP BY at.affiliate_id) AS balance FROM " . DB_PREFIX . "affiliate a";

    $implode = array();

    if (!empty($data['filter_name'])) {
        $implode[] = "CONCAT(a.firstname, ' ', a.lastname) LIKE '" . $this->db->escape($data['filter_name']) . "%'";
    }

    if (!empty($data['filter_email'])) {
        $implode[] = "LCASE(a.email) = '" . $this->db->escape(utf8_strtolower($data['filter_email'])) . "'";
    }

    if (!empty($data['filter_code'])) {
        $implode[] = "a.code = '" . $this->db->escape($data['filter_code']) . "'";
    }

    if (isset($data['filter_status']) && !is_null($data['filter_status'])) {
        $implode[] = "a.status = '" . (int)$data['filter_status'] . "'";
    }

    if (isset($data['filter_approved']) && !is_null($data['filter_approved'])) {
        $implode[] = "a.approved = '" . (int)$data['filter_approved'] . "'";
    }

    if (!empty($data['filter_date_added'])) {
        $implode[] = "DATE(a.date_added) = DATE('" . $this->db->escape($data['filter_date_added']) . "')";
    }

    if ($implode) {
        $sql .= " WHERE " . implode(" AND ", $implode);
    }

    $sort_data = array(
        'name',
        'a.email',
        'a.code',
        'a.status',
        'a.approved',
        'a.date_added'
    );

    if (isset($data['sort']) && in_array($data['sort'], $sort_data)) {
        $sql .= " ORDER BY " . $data['sort'];
    } else {
        $sql .= " ORDER BY name";
    }

    if (isset($data['order']) && ($data['order'] == 'DESC')) {
        $sql .= " DESC";
    } else {
        $sql .= " ASC";
    }

    if (isset($data['start']) || isset($data['limit'])) {
        if ($data['start'] < 0) {
            $data['start'] = 0;
        }

        if ($data['limit'] < 1) {
            $data['limit'] = 20;
        }

        $sql .= " LIMIT " . (int)$data['start'] . "," . (int)$data['limit'];
    }

    $query = $this->db->query($sql);

    return $query->rows;
}

我正在尝试修改上述代码的第二行，以不选择列名称“status”中包含“Pending”和“Canceled”的任何行“

这就是我要添加的内容

AND (status != 'Pending' AND status != 'Cancelled')

像这样:

替换此代码

$sql = "SELECT *, CONCAT(a.firstname, ' ', a.lastname) AS name, (SELECT SUM(at.amount) FROM " . DB_PREFIX . "affiliate_transaction at WHERE at.affiliate_id = a.affiliate_id GROUP BY at.affiliate_id) AS balance FROM " . DB_PREFIX . "affiliate a";

有了这个

$sql = "SELECT *, CONCAT(a.firstname, ' ', a.lastname) AS name, (SELECT SUM(at.amount) FROM " . DB_PREFIX . "affiliate_transaction at WHERE at.affiliate_id = a.affiliate_id AND (status != 'Pending' AND status != 'Cancelled') GROUP BY at.affiliate_id) AS balance FROM " . DB_PREFIX . "affiliate a";

我仍然收到所有结果，包括 status 列中带有 "Pending" 和 "Cancelled" 的结果，我是什么这里做错了吗？

Answer 1

在我使用 MySQL 的 10 年中，我从未尝试过在 SELECT 字段列表中使用子查询；也许你遇到了一个错误？不管怎样，这可能会更快，因为我想象子查询是为每一行单独运行的。

SELECT `a`.*
   , CONCAT(`a`.firstname, ' ', `a`.lastname) AS name
   , SUM(IFNULL(`at`.amount, 0)) AS balance
FROM affiliate AS `a`
   LEFT JOIN affiliate_transaction AS `at` 
   ON `a`.affiliate_id = `at`.affiliate_id
   AND `at`.status NOT IN ('Pending', 'Cancelled')
WHERE [affiliate conditions]
GROUP BY `a`.affliate_id
;

根据索引/数据，这可能会更快:

SELECT `a`.*
   , CONCAT(`a`.firstname, ' ', `a`.lastname) AS name
   , SUM(IF(`at`.status IS NULL OR `at`.status IN ('Pending', 'Cancelled')
            , 0, `at`.amount)
   ) AS balance
FROM affiliate AS `a`
   LEFT JOIN affiliate_transaction AS `at` 
   ON `a`.affiliate_id = `at`.affiliate_id
WHERE [affiliate conditions]
GROUP BY `a`.affliate_id
;

编辑:哎呀，GROUP BY 会有帮助。