mysql - 从查询中得到错误的计数

Question

表结构和示例数据

 CREATE TABLE IF NOT EXISTS `orders` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `customer_id` int(11) NOT NULL,
      `restaurant_id` int(11) NOT NULL,
      `bill_id` int(11) NOT NULL,
      `source_id` int(1) NOT NULL,
      `order_medium_id` int(11) NOT NULL,
      `purchase_method` varchar(255) NOT NULL,
      `totalamount` int(11) NOT NULL,
      `delivery_charg` int(11) NOT NULL,
      `discount` int(11) NOT NULL,
      `vat` int(11) NOT NULL,
      `total_price` int(11) NOT NULL DEFAULT '0',
      `date_created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
      PRIMARY KEY (`id`),
      KEY `customer_id` (`customer_id`),
      KEY `source_id` (`source_id`),
      KEY `restaurant_id` (`restaurant_id`),
      KEY `bill_id` (`bill_id`)
    ) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=22 ;

    --
    -- Dumping data for table `orders`
    --
    INSERT INTO `orders` (`id`, `customer_id`, `restaurant_id`, `bill_id`, `source_id`, `order_medium_id`, `purchase_method`, `totalamount`, `delivery_charg`, `discount`, `vat`, `total_price`, `date_created`, `uploaddate`) VALUES
    (1, 1, 1, 1, 1, 0, 'cash', 1600, 0, 0, 0, 1600, '2016-05-29 13:05:40', '2016-07-07 05:55:39'),
    (2, 1, 1, 2, 2, 1, 'cash', 1820, 0, 0, 0, 1820, '2016-06-27 07:21:25', '2016-07-07 05:55:39'),
    (4, 1, 1, 3, 3, 0, 'cash', 1770, 0, 0, 0, 1770, '2016-05-31 13:05:56', '2016-07-07 05:55:39'),
    (5, 1, 1, 4, 2, 1, 'cash', 1300, 0, 0, 0, 1300, '2016-06-27 07:21:31', '2016-07-07 05:55:39'),
    (6, 1, 1, 5, 1, 0, 'cash', 950, 0, 0, 0, 950, '2016-06-02 13:06:15', '2016-07-07 05:55:39'),
    (7, 1, 1, 6, 1, 0, 'cash', 1640, 0, 0, 0, 1640, '2016-06-03 13:06:24', '2016-07-07 05:55:39'),
    (8, 1, 1, 7, 2, 2, 'cash', 1600, 0, 0, 0, 1600, '2016-06-27 07:21:36', '2016-07-07 05:55:39'),
    (9, 1, 1, 8, 2, 2, 'cash', 1575, 0, 0, 0, 1575, '2016-06-27 07:21:40', '2016-07-07 05:55:39'),
    (10, 1, 1, 9, 3, 0, 'cash', 1125, 0, 0, 0, 1125, '2016-06-06 13:06:48', '2016-07-07 05:55:39'),
    (11, 1, 1, 10, 2, 3, 'cash', 1920, 0, 0, 0, 1920, '2016-06-27 07:21:51', '2016-07-07 05:55:39'),
    (12, 1, 1, 11, 3, 0, 'cash', 1560, 0, 0, 0, 1560, '2016-06-08 13:07:05', '2016-07-07 05:55:39'),
    (13, 1, 1, 12, 2, 4, 'cash', 1365, 0, 0, 0, 1365, '2016-06-27 07:21:56', '2016-07-07 05:55:39'),
    (14, 1, 1, 13, 1, 0, 'cash', 1235, 0, 0, 0, 1235, '2016-06-10 13:07:26', '2016-07-07 05:55:39'),
    (15, 1, 1, 14, 3, 0, 'cash', 1595, 0, 0, 0, 1595, '2016-06-11 13:07:34', '2016-07-07 05:55:39'),
    (16, 1, 1, 15, 3, 0, 'cash', 1900, 0, 0, 0, 1900, '2016-06-12 13:07:44', '2016-07-07 05:55:39'),
    (17, 1, 1, 16, 3, 0, 'cash', 2200, 0, 0, 0, 2200, '2016-06-13 13:07:52', '2016-07-07 05:55:39'),
    (18, 1, 1, 17, 1, 0, 'cash', 1475, 0, 0, 0, 1475, '2016-06-14 13:07:59', '2016-07-07 05:55:39'),
    (19, 2, 1, 35, 2, 4, 'Online', 471, 0, 0, 0, 471, '2016-07-04 13:20:25', '2016-07-07 05:55:39'),
    (21, 4, 1, 36, 1, 0, 'Online Payment', 0, 0, 0, 0, 2000, '2016-07-01 07:06:46', '2016-07-07 05:55:39'),
    (22, 1, 1, 40, 2, 1, 'cash', 3920, 80, 200, 100, 3900, '2016-07-06 13:10:36', '2016-07-07 05:55:39'),
    (23, 5, 1, 42, 2, 2, 'Cash', 0, 0, 0, 0, 2620, '2016-07-11 06:08:24', '2016-07-11 12:31:02'),
    (24, 2, 1, 48, 3, 0, 'cash', 0, 0, 0, 0, 1000, '2016-07-12 16:26:00', '2016-07-12 10:56:40'),
    (25, 6, 1, 47, 3, 0, 'cash', 0, 0, 0, 0, 2330, '2016-07-12 16:35:00', '2016-07-12 11:05:41'),
    (26, 7, 1, 46, 3, 0, 'cash', 0, 0, 0, 0, 1000, '2016-07-12 17:18:00', '2016-07-12 11:48:11');

要求:

1. Need to get total number of order for current and last month.

我试过的查询

SELECT CASE source_id
WHEN 1 
THEN  'visit'
WHEN 2 
THEN  'online'
WHEN 3 
THEN  'phone'
END AS  `type` ,
count( IF( MONTH( date_created ) = MONTH( ( CURDATE() - INTERVAL 1 MONTH ) ) , source_id, 0 )) AS  `lastMonthOrders` , 
count( IF( MONTH( date_created ) = MONTH( NOW() ) , source_id, 0 ) ) AS  `currentMonthOrders` 
FROM  `orders` 
WHERE MONTH(date_created)
BETWEEN MONTH( ( CURDATE() - INTERVAL 1 MONTH ) )
AND MONTH(CURDATE( )) 
AND restaurant_id =1
GROUP BY source_id

得到结果

type    lastMonthOrders currentMonthOrders
online      5                    5
visit       9                    9
phone       8                    8

结果应该是

type    lastMonthOrders currentMonthOrders
online      6                    3
visit       4                    1
phone       5                    3

Answer 1

@MarcB 解释了为什么 COUNT在这种情况下可能会转向错误的输出。

在这里，我使用 SUM 更改了查询而不是 COUNT

SELECT CASE source_id
WHEN 1 
THEN  'online'
WHEN 2 
THEN  'visit'
WHEN 3 
THEN  'phone'
END AS  `type` ,
SUM(MONTH( date_created ) = MONTH( ( CURDATE() - INTERVAL 1 MONTH ) )) AS  `lastMonthOrders` , 
SUM(MONTH( date_created ) = MONTH( NOW() )) AS  `currentMonthOrders` 
FROM  `orders` 
WHERE MONTH(date_created)
BETWEEN MONTH( ( CURDATE() - INTERVAL 1 MONTH ) )
AND MONTH(CURDATE( )) 
AND restaurant_id =1
GROUP BY source_id;

注意:

SUM(a=b) returns 1 only if a=b .

所以，在上面的查询中

SUM(MONTH( date_created ) = MONTH( NOW() )) AS currentMonthOrders只有在 date_created and NOW() 时才会加 1反射(reflect)同月数

关于 COUNT 的一些微妙之处:

SELECT COUNT(0);   Result: 1

SELECT COUNT(-1);  Result: 1

SELECT COUNT(NULL); Result: 0

SELECT COUNT(71); Result: 1

SQL FIDDLE