记: 复杂的sql语句使用——统计（列值种数确定）、分组、筛选、排序、统计天数、按小时查询、时间戳转日期、条件判断

统计总条目数

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;

 

统计天数(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： 命中索引的长度（受编码选择影响、比实际字段的值大）

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 

 

未指明排序方式

 

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