SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";CREATE TABLE IF NOT EXISTS `orders` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`sn` varchar(20) NOT NULL,
`addtime` int(10) unsigned NOT NULL,
`amount` decimal(10,2) unsigned NOT NULL,
`auditstatus` tinyint(3) unsigned NOT NULL COMMENT '审核状态',
`paystatus` tinyint(3) unsigned NOT NULL COMMENT '支付状态',
`payway` tinyint(3) unsigned NOT NULL COMMENT '支付方式',
`deliverystatus` tinyint(3) unsigned NOT NULL COMMENT '发货状态',
`deliveryway` tinyint(3) unsigned NOT NULL COMMENT '发货方式',
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;auditstatus:0未确认|1已确认
paystatus:0未付款|1已付款
payway:1网银|2支付宝|3货到付款
deliverystatus:0未发货|1已发货
deliveryway:1宅急送|2顺丰|3申通求结果:
日期(时间范围) 宅急送单数 宅急送金额 顺丰单数 顺丰金额 支付宝单数 支付宝金额 货到付款单数 货到付款金额
1-1
1-2
1-3
1-4
...
1-30按日期求得每日统计结果,求一个高效的写法,谢谢。
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`sn` varchar(20) NOT NULL,
`addtime` int(10) unsigned NOT NULL,
`amount` decimal(10,2) unsigned NOT NULL,
`auditstatus` tinyint(3) unsigned NOT NULL COMMENT '审核状态',
`paystatus` tinyint(3) unsigned NOT NULL COMMENT '支付状态',
`payway` tinyint(3) unsigned NOT NULL COMMENT '支付方式',
`deliverystatus` tinyint(3) unsigned NOT NULL COMMENT '发货状态',
`deliveryway` tinyint(3) unsigned NOT NULL COMMENT '发货方式',
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;auditstatus:0未确认|1已确认
paystatus:0未付款|1已付款
payway:1网银|2支付宝|3货到付款
deliverystatus:0未发货|1已发货
deliveryway:1宅急送|2顺丰|3申通求结果:
日期(时间范围) 宅急送单数 宅急送金额 顺丰单数 顺丰金额 支付宝单数 支付宝金额 货到付款单数 货到付款金额
1-1
1-2
1-3
1-4
...
1-30按日期求得每日统计结果,求一个高效的写法,谢谢。
sum(if(deliveryway=1,1,0)) as `宅急送单数`,
sum(if(deliveryway=1,amount,0)) as `宅急送金额`,
sum(if(deliveryway=2,1,0)) as `顺丰单数`,
sum(if(deliveryway=2,amount,0)) as `顺丰金额`,
sum(if(payway=2,1,0)) as `支付宝单数`,
sum(if(payway=2,amount,0)) as `支付宝金额`,
sum(if(payway=3,1,0)) as `货到付款单数`,
sum(if(payway=3,amount,0)) as `货到付款金额`
from orders
group by date(addtime)
(60, 'UM20120615172210275', 1339752130, 175.00, 3, 0, 0, 4, 0),
(61, 'UM20120615172415701', 1339752255, 75.00, 4, 0, 3, 0, 2),
(62, 'UM20120615174421457', 1339753461, 128.00, 4, 0, 1, 0, 1),
(63, 'UM20120615193004154', 1339759804, 98.00, 1, 0, 1, 4, 1),
(64, 'UM20120615193116950', 1339759876, 108.00, 3, 0, 1, 0, 1),
(65, 'UM20120615193137788', 1339759897, 108.00, 3, 1, 2, 0, 1),
(66, 'UM20120615193418220', 1339760058, 174.00, 3, 0, 1, 0, 1),
(67, 'UM20120617200419330', 1339934659, 82.00, 3, 0, 1, 0, 1),
(68, 'UM20120618141156234', 1339999916, 239.00, 4, 0, 1, 0, 1),
(69, 'UM20120618142152602', 1340000512, 90.00, 4, 0, 2, 0, 1),
(70, 'UK20120619115655104', 1340078215, 128.00, 3, 0, 3, 0, 2),
(71, 'UM20120619145356375', 1340088836, 138.00, 4, 2, 1, 3, 1),
(72, 'UK20120619201437438', 1340108077, 768.00, 1, 0, 3, 4, 2),
(73, 'UK20120619202145624', 1340108505, 640.00, 4, 0, 3, 0, 2),
(74, 'UM20120620145457485', 1340175297, 138.00, 4, 0, 3, 0, 2),
(75, 'UG20120620164517468', 1340181917, 1115.00, 1, 2, 2, 4, 1),
(76, 'UM20120620164717472', 1340182037, 138.00, 4, 0, 1, 0, 1),
(77, 'UG20120620213109172', 1340199069, 212.00, 1, 2, 2, 4, 1),
(78, 'UJ20120620214213568', 1340199733, 365.00, 3, 0, 1, 0, 1),
(79, 'UG20120620223857705', 1340203137, 212.00, 1, 0, 3, 4, 2),
(80, 'UK20120620224248860', 1340203368, 344.00, 1, 2, 1, 4, 1),
(81, 'UM20120621104453394', 1340246693, 138.00, 3, 0, 1, 2, 1),
(82, 'UG20120621113038974', 1340249438, 43.00, 1, 2, 1, 6, 1),
(83, 'UM20120621113543253', 1340249743, 90.00, 4, 2, 2, 0, 1),
(84, 'UG20120621114310274', 1340250190, 212.00, 1, 0, 3, 4, 2),
(85, 'UG20120621114708787', 1340250428, 158.00, 1, 2, 1, 6, 1),
(86, 'UG20120621203020434', 1340281820, 790.00, 3, 0, 3, 0, 2),
(87, 'UG20120621212848154', 1340285328, 212.00, 3, 0, 3, 0, 2),
(88, 'UC20120621230452266', 1340291092, 418.00, 1, 0, 3, 4, 2),
(89, 'UG20120622021003664', 1340302203, 568.00, 3, 0, 3, 0, 2),
(90, 'UG20120622111025212', 1340334625, 395.00, 1, 0, 3, 4, 2),
(91, 'UC20120622112539592', 1340335539, 408.00, 3, 0, 3, 0, 2),
(92, 'UG20120622120633621', 1340337993, 112.00, 3, 0, 2, 0, 1),
(93, 'UG20120622120843475', 1340338123, 112.00, 3, 0, 2, 0, 1),
(94, 'UG20120622122019283', 1340338819, 194.00, 1, 2, 2, 4, 1),
(95, 'UG20120622122241681', 1340338961, 112.00, 3, 1, 2, 0, 1),
(96, 'UG20120622201546506', 1340367346, 133.00, 3, 0, 1, 0, 1),
(97, 'UG20120622202113247', 1340367673, 133.00, 3, 0, 1, 0, 1),
(98, 'UG20120622202430814', 1340367870, 112.00, 3, 0, 1, 0, 1),
(99, 'UG20120622202541366', 1340367941, 133.00, 3, 0, 1, 0, 1),
(100, 'UH20120622202656720', 1340368016, 236.00, 3, 0, 1, 0, 1),
(101, 'UM20120622203005124', 1340368205, 138.00, 3, 0, 1, 0, 1),
(102, 'UG20120622203737214', 1340368657, 230.00, 3, 0, 1, 0, 1),
(103, 'UG20120622220647585', 1340374007, 165.00, 4, 0, 3, 0, 2),
(104, 'UG20120622220858171', 1340374138, 165.00, 4, 0, 3, 0, 2),
(105, 'UG20120623184748513', 1340448468, 230.00, 1, 0, 3, 4, 2),
(106, 'UG20120623190807216', 1340449687, 810.00, 1, 0, 3, 4, 2),
(107, 'UG20120623231513748', 1340464513, 1060.00, 1, 0, 3, 4, 2),
(108, 'UG20120624050335979', 1340485415, 195.00, 1, 0, 1, 4, 1),
(109, 'UG20120624052205512', 1340486525, 780.00, 1, 1, 2, 4, 1),
(110, 'UG20120624132631909', 1340515591, 1845.00, 1, 0, 3, 4, 2),
(111, 'UG20120624132753980', 1340515673, 590.00, 4, 0, 3, 0, 2),
(112, 'UG20120624133038116', 1340515838, 480.00, 4, 0, 3, 0, 2),
(113, 'UG20120624153206347', 1340523126, 212.00, 1, 0, 3, 4, 2),
(114, 'UG20120624154348136', 1340523828, 162.00, 1, 0, 3, 4, 2),
(115, 'UK20120624192915524', 1340537355, 206.00, 1, 2, 2, 4, 1),
(116, 'UB20120624224146822', 1340548906, 94.00, 3, 0, 2, 0, 1),
(117, 'UG20120625010724241', 1340557644, 245.00, 1, 2, 1, 4, 1),
(118, 'UM20120625085854635', 1340585934, 144.00, 3, 0, 1, 0, 1),
(119, 'UG20120625092125754', 1340587285, 356.00, 3, 0, 1, 0, 1),
(120, 'UG20120625092322419', 1340587402, 356.00, 1, 0, 3, 4, 2),
(121, 'UM20120625092733597', 1340587653, 144.00, 3, 0, 1, 0, 1),
(122, 'UB20120625093307717', 1340587987, 115.00, 3, 0, 1, 0, 1),
(123, 'UM20120625093914360', 1340588354, 148.00, 3, 1, 2, 0, 1),
(124, 'UM20120625094335576', 1340588615, 148.00, 3, 0, 1, 0, 1),
(125, 'UG20120625095356860', 1340589236, 230.00, 3, 0, 1, 0, 1),
(126, 'UG20120625104150926', 1340592110, 428.00, 1, 2, 1, 4, 3),
(127, 'UG20120625104335976', 1340592215, 140.00, 4, 2, 1, 0, 3),
(128, 'UB20120625114127874', 1340595687, 115.00, 3, 0, 1, 0, 1),
(129, 'UM20120625114300952', 1340595780, 138.00, 3, 0, 2, 0, 1),
(130, 'UB20120625114306976', 1340595786, 183.00, 3, 0, 2, 0, 1),
(131, 'UB20120625114328274', 1340595808, 178.00, 3, 0, 1, 0, 1),
(132, 'UM20120625114329279', 1340595809, 144.00, 4, 0, 1, 0, 1),
(133, 'UG20120625114401242', 1340595841, 288.00, 3, 0, 1, 0, 1),
(134, 'UG20120625120246539', 1340596966, 288.00, 3, 0, 1, 0, 1),
(135, 'UG20120625121407730', 1340597647, 232.00, 1, 0, 3, 4, 2),
(136, 'UG20120625143651118', 1340606211, 180.00, 3, 0, 2, 0, 1),
(137, 'UG20120625144547687', 1340606747, 392.00, 1, 0, 2, 4, 1),
(138, 'UG20120625144651704', 1340606811, 180.00, 3, 1, 2, 0, 1),
(139, 'UG20120625144919510', 1340606959, 392.00, 3, 0, 2, 0, 1),
(140, 'UG20120625150749594', 1340608069, 43.00, 3, 0, 1, 0, 1),
(141, 'UM20120625151044902', 1340608244, 98.00, 4, 0, 1, 0, 1),
(142, 'UG20120625151207366', 1340608327, 639.00, 3, 0, 1, 4, 1),
(143, 'UG20120625151443433', 1340608483, 41.00, 4, 0, 1, 0, 1),
(144, 'UG20120625152817375', 1340609297, 168.00, 1, 2, 2, 4, 1),
(145, 'UJ20120625184444384', 1340621084, 268.00, 3, 0, 3, 0, 2),
(146, 'UJ20120626120944874', 1340683784, 298.00, 4, 0, 1, 0, 1),
(147, 'UJ20120626215637829', 1340718997, 325.00, 3, 0, 3, 0, 2),
(148, 'UJ20120626220522802', 1340719522, 328.00, 3, 0, 1, 0, 1),
(149, 'UJ20120627071923902', 1340752763, 328.00, 3, 0, 1, 0, 1),
(150, 'UJ20120627143106323', 1340778666, 1080.00, 4, 2, 3, 0, 2),
(151, 'UM20120627154752512', 1340783272, 73.00, 1, 0, 3, 4, 2),
(152, 'UG20120627163419703', 1340786059, 356.00, 1, 0, 3, 4, 2),
(153, 'UG20120627171756793', 1340788676, 356.00, 3, 0, 3, 0, 2),
(154, 'UG20120627182959509', 1340792999, 300.00, 4, 0, 1, 0, 2),
(155, 'UG20120627184638584', 1340793998, 356.00, 4, 0, 1, 2, 1),
(156, 'UG20120627185053498', 1340794253, 751.00, 1, 2, 1, 4, 1),
(157, 'UU20120627223946339', 1340807986, 702.00, 1, 0, 3, 4, 2),
(158, 'UC20120627230747123', 1340809667, 360.00, 1, 2, 1, 4, 1),
(159, 'UM20120628085817993', 1340845097, 16.00, 4, 0, 1, 0, 1);
select DATE_FORMAT(NOW(),'%m-%d'),sum(if(deliveryway=1,1,0)),
sum(if(deliveryway=1 and paystatus=1,amount,0)),
...
group by DATE_FORMAT(NOW(),'%m-%d')
否则生成每月天数,再与工作表连接