我有一个订单表
桌子:
CREATE TABLE IF NOT EXISTS `order_main` (
`om_id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'ID',
`om_raren` varchar(20) NOT NULL COMMENT 'serial number',
`om_total` int(11) NOT NULL COMMENT 'money',
`om_freight` int(11) NOT NULL COMMENT 'freight',
`om_amount` int(11) NOT NULL COMMENT 'money + freight',
`om_status` int(11) NOT NULL COMMENT 'Order Status:1)Unpaid 2)Paid 3)Cancel 4)Payment
success,
`om_created` datetime NOT NULL COMMENT 'create time',
PRIMARY KEY (`om_id`)
)
我想要三种查询方法:
1)逐年
2)逐月进行
3)每周
我希望结果是:
AllRow AllTotal date
2 100 1
10 1000 10
10 1000 12
全部行:项目计数,计数(*)
全部合计:总和(om_total)
日期:任何月份或任何年份或任何星期
我没有任何想法做这件事
希望你给我建议,谢谢
你可以这样尝试
:select count(*) as AllRow, sum(om_total) as AllTotal,YEAR(om_created) as d from order_main group by YEAR(om_created)
对于月
select count(*) as AllRow, sum(om_total) as AllTotal,Month(om_created) as m from order_main group by year(om_created),month(om_created)
一周
select count(*) as AllRow, sum(om_total) as AllTotal,WEEK(om_created) as week from order_main group by year(om_created),WEEK(om_created)
注意:不测试查询。