统计总条目数
SELECT COUNT(*) FROM recipe_cook_task
按添加时间倒序取1000条
SELECT * FROM recipe_cook_task ORDER BY add_time DESC LIMIT 1000
按添加时间取倒数第三条
SELECT * FROM recipe_cook_task ORDER BY add_time DESC LIMIT 2,1
按recipeId、时间段筛选出创建时间、烹饪开始时间、添加任务时间、设备编号,倒序(时间戳转日期)
SELECT
create_time AS '创建时间',
FROM_UNIXTIME( cook_time / 1000 ) AS '烹饪开始时间',
FROM_UNIXTIME( add_time / 1000 ) AS '添加任务时间',
device_cgid AS '设备编号'
FROM
recipe_cook_task
WHERE
recipe_id = '4501359'
AND create_time >= '2020-07-22 00:00:00'
AND create_time <= '2020-07-28 23:59:59'
ORDER BY
create_time;
查询时间段范围内满足recipeId的所有值——时间戳转Date
SELECT
FROM_UNIXTIME( cook_time / 1000 ) AS '烹饪开始时间'
FROM
recipe_cook_task
WHERE
recipe_id = '4501359'
AND create_time >= '2020-07-22 00:00:00'
AND create_time <= '2020-07-28 23:59:59'
ORDER BY
cook_time DESC;
统计时间段范围内的满足某recipeId的一些数据
select
store_id as '店铺Id',
count(case
when status in (3 ,
4) then cast(1 as signed)
else null
end) as '失败',
count(case
when status in (2) then cast(1 as signed)
else null
end) as '成功',
count(store_id) as '烹饪次数',
count(distinct task_date) as '天数',
count(distinct recipe_id) as '菜谱个数'
from
recipe_cook_task
where
create_time>= '2020-07-01 00:00:00'
and create_time<= '2020-07-28 23:59:59'
group by
store_id
SELECT DISTINCT(status) from
recipe_cook_task
查找满足某productKey的数据
select device_cgid, device_alias, product_key, store_id, count(status) from recipe_cook_task where product_key='a1ZkgblMxMc' group by device_cgid , device_alias , product_key , store_id;
按merchant_id、store_id统计天分组统计天数(count与distinct联合使用)
SELECT
count( 1 ) AS total,
sum( CASE finish_time WHEN 0 THEN 1 ELSE 0 END ) AS success,
sum( CASE finish_time WHEN 0 THEN 0 ELSE 1 END ) AS failure,
merchant_id,
store_id,
COUNT( DISTINCT DATE( create_time ) ) AS days
FROM
recipe_cook_task
GROUP BY
merchant_id,
store_id
SELECT
store_id,
merchant_id,
count( CASE WHEN STATUS IN ( 3, 4 ) THEN cast( 1 AS signed ) ELSE NULL END ) AS errorCuisineCount,
count( CASE WHEN STATUS IN ( 2 ) THEN cast( 1 AS signed ) ELSE NULL END ) AS successCuisineCount,
count( store_id ),
count( DISTINCT DATE( create_time ) ) AS days
FROM
recipe_cook_task
GROUP BY
store_id,
merchant_id;
SELECT
store_id,
merchant_id,
count( CASE WHEN STATUS IN ( 3, 4 ) THEN cast( 1 AS signed ) ELSE NULL END ),
count( CASE WHEN STATUS IN ( 2 ) THEN cast( 1 AS signed ) ELSE NULL END ),
count( store_id ),
count( DISTINCT task_date ),
count( DISTINCT recipe_id )
FROM
recipe_cook_task
WHERE
1 = 1
GROUP BY
store_id,
merchant_id;
select count(1) as total,
sum(case finish_time when 0 then 1 else 0 end) as success,
sum(case finish_time when 0 then 0 else 1 end) as failure,
merchant_id,
store_id,
COUNT(DISTINCT DATE(create_time)) AS days
from recipe_cook_task
group by merchant_id,store_id
select count(1) as total,
sum(case finish_time when 0 then 1 else 0 end) as success,
sum(case finish_time when 0 then 0 else 1 end) as failure,
merchant_id,
store_id,
COUNT(DISTINCT DATE(create_time)) AS days
from recipe_cook_task
group by merchant_id,store_id
按小时统计
SELECT DATE_FORMAT(create_time, '%Y-%m-%d :%H' ) hours, MAX(money) FROM user_order GROUP BY hours;
count、distinct的使用
select
recipe_name ,
recipe_id ,
product_key ,
count(case when status in (3) then cast(1 as signed) else null end) as errorCounts,
count(status) as cuisineCounts,
count(DISTINCT store_id) as storeCounts
from recipe_cook_task
group by recipe_name,recipe_id,product_key;
select recipe_name , recipe_id product_key ,
count(case when status in (3) then cast(1 as signed) else null end) as errorCounts,
count(status) as cuisineCounts,
count(store_id) as storeCounts
from recipe_cook_task
where 1=1
group by recipe_name,recipe_id
sum、count的使用
select count(1) as total,
sum(case finish_time when 0 then 1 else 0 end) as success,
sum(case finish_time when 0 then 0 else 1 end) as failure,
merchant_id,
store_id
from recipe_cook_task
group by merchant_id,store_id
where、count、group by的联合使用
SELECT device_cgid,device_alias,product_key,
count(status) as cuisineCounts
FROM recipe_cook_task
WHERE store_id = '40092672'
GROUP BY device_cgid,device_alias,product_key
explain的使用
EXPLAIN SELECT
*
FROM
recipe_cook_task
WHERE
1 = 1
ORDER BY
create_time DESC
其中:
type 为 ALL: 全表扫描
possible_keys: 可能用到的索引
key: 命中的索引
key_len: 命中索引的长度(受编码选择影响、比实际字段的值大)
extral: 执行情况的描述和说明
select_type: 表示查询的类型
rows: 扫描的行数
count、group by、order by的联合使用,order by 不指明排序方式时,默认增序
SELECT DATE(create_time) AS date, store_id,merchant_id,
count(store_id) AS recipes
FROM recipe_cook_task
GROUP BY date,store_id,merchant_id
ORDER BY date
未指明排序方式
条件in的使用:
SELECT
store_id AS '店铺Id',
count( CASE WHEN STATUS IN ( 3, 4 ) THEN cast( 1 AS signed ) ELSE NULL END ) AS '失败数',
count( CASE WHEN STATUS IN ( 2 ) THEN cast( 1 AS signed ) ELSE NULL END ) AS '成功数',
count( store_id ) AS '次数',
count( DISTINCT task_date ) AS '天数',
count( DISTINCT recipe_id ) AS '菜谱个数'
FROM
recipe_cook_task
WHERE
store_id IN ( '304108006227476480', '283491778840092672' )
GROUP BY
store_id
group by操作中,select后面接的结果集字段只有两种: 要么就只有group by后出现的字段,要么就是group by后出现的字段 + 聚合函数的组合(常用的五种聚合函数: min()求列中最小数值 , max()求列中最大数值 , avg()求平均值 , sum()求列中字段对应数值的总和 , count()求列的总条数 )
left jion 使用
select r.recipe_name
from quantity q
left join step_sentence s on q.sentence_id = s.sentence_id
left join recipe_info r on r.recipe_id = s.recipe_id
where s.related_glossaryid = '19'and par_desc = '时间’ and r.user_id = 'chefTest01'
group by r.recipe_name;
count联合distinct、group by 联合使用
select
store_id, merchant_id, COUNT(DISTINCT DATE(create_time)) AS days
from
recipe_cook_task
GROUP BY
store_id,merchant_id