选择排除表中特定值的查询 - Select query that excludes a particular value within a table

Select query that excludes a particular value within a table

本文关键字：查询排除选择 | 更新日期: 2023-09-27

我有一个查询，计算在'dealer_code'字段中具有值'1234'的行数。

这工作得很好，但我不想包括行与'sales_id'包含或以'的值结束。D999 '

例如，如果数据库中的sales_id包含:

sales_id  |  dealer_code
123.D000  |  1234          x
123.D777  |  1234          x
123.D333  |  5555 
123.D999  |  1234 
123.D999  |  5555  
123.D111  |  1234          x
123.D999  |  5555

下面的代码应该显示结果3，但它显示的是4。有人能帮忙吗?

$total_query = "SELECT COUNT(sales_points) AS total_sales_points 
FROM sales_list 
WHERE dealer_code = '1234'
AND sales_id != '.D999'
AND sales_entry_date BETWEEN '2013-11-01 00:00:00' AND '2014-11-31 23:59:59'";

试试这个:

$total_query = "SELECT COUNT(sales_points) AS total_sales_points 
                FROM sales_list 
                WHERE dealer_code = '1234'
                AND sales_id NOT LIKE '%.D999'
                AND sales_entry_date BETWEEN '2013-11-01 00:00:00' AND '2014-11-31 23:59:59'";

SELECT COUNT(sales_points) AS total_sales_points 
FROM sales_list 
WHERE dealer_code = '1234'
AND sales_id NOT LIKE '%.D999%'
AND sales_entry_date BETWEEN '2013-11-01 00:00:00' AND '2014-11-31 23:59:59'";

我认为查询应该如下所示。

  $total_query = "SELECT COUNT(sales_points) AS total_sales_points 
    FROM sales_list 
    WHERE dealer_code = '1234'
    AND sales_id NOT LIKE '%.D999%'
    AND sales_entry_date BETWEEN '2013-11-01 00:00:00' AND '2014-11-31 23:59:59'";

$total_query = "SELECT COUNT(sales_points) AS total_sales_points 
FROM sales_list 
WHERE dealer_code = '1234'
AND sales_id NOT LIKE '%.D999'
AND sales_entry_date BETWEEN '2013-11-01 00:00:00' AND '2014-11-31 23:59:59'";