复杂sql语句——统计（列值种数确定）、分组、筛选、排序、统计天数、按小时查询、时间戳转日期、判断（or、count、cast、distinct、in、order by、jion、group by等）

 

 

统计总条目数

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