mysql捕获1300的错误,mysql-从查询中获取错误的计数

表结构和样本数据

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

解决方法:

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

在这里,我使用SUMinstead更改了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;

注意：

仅当a = b时,SUM(a = b)才返回1.

所以,在上面的查询中

SUM(MONTH(date_created)= MONTH(NOW()))AS currentMonthOrder仅在date_created和NOW()反映相同的月份数时才会加1

关于COUNT的一些技巧：

SELECT COUNT(0); Result: 1

SELECT COUNT(-1); Result: 1

SELECT COUNT(NULL); Result: 0

SELECT COUNT(71); Result: 1

标签：select,mysql

来源： https://codeday.me/bug/20191026/1939859.html