SELECT
`menuid`,
SUM(`num`)AS total,
`storeid`,
DATE_FORMAT(`dateline`,'%Y-%m-%d') days
FROM
loss
WHERE
`storeid` = 2
GROUP BY
days,menuid -- 查询 月 周 天
select DATE_FORMAT(create_time,'%Y%u') weeks,count(caseid) count from tc_case group by weeks;
select DATE_FORMAT(create_time,'%Y%m%d') days,count(caseid) count from tc_case group by days;
select DATE_FORMAT(create_time,'%Y%m') months,count(caseid) count from tc_case group by months; -- 某店某菜品月销量(按天统计某店销售量)
SELECT
DATE_FORMAT(`order`.`dateline`,'%Y%m%d') days,
sum(`order_detail`.`amount`)
FROM
`order_detail`
left join `order` on `order`.`orderid` = `order_detail`.`orderid`
WHERE `order`.`storeid` = 2 and `order`.`dateline` >= '2016-11-01' and `order`.`dateline` < '2016-12-10' and `order_detail`.`status` = 1 and `order_detail`.`menuid` = 2
GROUP BY
days -- 损耗统计(按天统计某店某菜损耗量)
SELECT
DATE_FORMAT(`dateline`,'%Y%m%d') days,
sum(`num`) as `lnum` ,
`menuid`
from
`loss`
where `storeid` = ? and `dateline` >= ? and `dateline` < ? and `menuid` = ?
GROUP BY
days -- 退货统计
SELECT
DATE_FORMAT(`order`.`dateline`,'%Y%m%d') days,
sum(`order_detail`.`amount` - `order_detail`.`takeamount`) as refund
FROM
`order_detail`
LEFT JOIN `order` on `order`.`orderid` = `order_detail`.`orderid`
WHERE `order`.`storeid` = 1 and `order`.`dateline` >= '2016-11-01' and `order`.`dateline` < '2016-12-10' and `order_detail`.`status` = 4 and `order_detail`.`menuid` = 15
GROUP BY
days -- 全国排名
SELECT
DATE_FORMAT(`run`.`datetime`,'%Y%m%d') days,
sum(`run`.`step`) as tstep,
`run`.`uid` as uid,
`users`.`name`,
`users`.`sex`,
`users`.`birth`,
FROM
`run`
LEFT JOIN `users` on `users`.`uid` = `run`.`uid`
WHERE `run`.`datetime` >= ? and `run`.`datetime` < ?
GROUP BY
days,uid
ORDER BY tstep DESC
LIMIT 20 -- 全国排名 自身排名
SELECT
`tb`.`days`,
`tb`.`tstep`,
`tb`.`name`,
`tb`.`sex`,
`tb`.`birth`,
CASE
WHEN @rowtotal = sum(`tb`.`tstep`) THEN @rownum
WHEN @rowtotal := sum(`tb`.`tstep`) THEN @rownum :=@rownum + 1
WHEN @rowtotal = 0 THEN @rownum :=@rownum + 1
END AS rank
FROM
(SELECT
DATE_FORMAT(`run`.`datetime`,'%Y%m%d') days,
sum(`run`.`step`) as tstep,
`run`.`uid` as uid,
`users`.`name`,
`users`.`sex`,
`users`.`birth`
FROM
`run`
LEFT JOIN `users` on `users`.`uid` = `run`.`uid`
WHERE `run`.`datetime` >= ? and `run`.`datetime` < ?
GROUP BY
days,uid
ORDER BY tstep DESC
LIMIT 20
) as `tb`,
(SELECT @rownum := 0 ,@rowtotal := NULL) r