查询三

最新推荐文章于 2023-08-07 16:02:58 发布

刘大明白0l

最新推荐文章于 2023-08-07 16:02:58 发布

阅读量313

点赞数

文章标签： mysql

本文链接：https://blog.csdn.net/weixin_45345555/article/details/107708325

版权

多条件排序

select * from biz_order  where status = 1 order by   jiaji desc , id desc

每个地方平均驻留时间

 SELECT b.name_ ,
  ROUND(AVG(TIMESTAMPDIFF(MINUTE,edate,sdate)),2) as timediff from wify_info a left join biz_ziyuan b on a.zyid = b.id left join
wify_clint_his  c on a.bid = c.bid_   GROUP BY b.id having timediff is not null

时间单位是分钟.查看更多:https://blog.csdn.net/xzy565143480/article/details/90550871
在这里插入图片描述

查一天的数据

SELECT * FROM biz_order  WHERE  create_date > DATE_ADD(NOW(), INTERVAL -1 day) and status = 1

A表的两个主键对应的name

select a.*,b.name ,c.name
from a 
left join b on a.mid= b.id
left join b c on a.pid = c.id

在这里插入图片描述
所有的出去时间减去所有的进去时间的平均值

 SELECT b.name_ ,
  ROUND(AVG(TIMESTAMPDIFF(MINUTE,edate,sdate)),2) as timediff from wify_info a left join biz_ziyuan b on a.zyid = b.id left join
wify_clint_his  c on a.bid = c.bid_   GROUP BY b.id having timediff is not null

在这里插入图片描述

--查询学校卡片激活的报表
select x.*,ifnull(y.num1,0) as num1,CONVERT(ifnull((num1/num)*100,0), DECIMAL(10,2)) as fee
from 
(select count(a.id) as num,b.merchant_name as name
,a.school_id  from biz_card  a left join biz_merchant b on a.school_id = b.id group by a.school_id) x left join (
select count(a.id) as num1,b.merchant_name ,a.school_id  from biz_card  a left join biz_merchant b on a.school_id = b.id where a.status= 2 group by a.school_id
) y
on x.school_id = y.school_id

在这里插入图片描述
删除 You can’t specify target table ‘biz_card’ for update in FROM clause

delete from biz_card where card_id IN (select a.card_id  from(SELECT card_id  FROM biz_card GROUP BY card_id HAVING COUNT(1) > 1) a )  and status =1 and balance = 0

链接: 参考https://blog.csdn.net/u012767761/article/details/84997962.
#卡片查重
SELECT a.*,b.merchant_name FROM biz_card a, biz_merchant b WHERE card_id IN (SELECT card_id FROM biz_card GROUP BY card_id HAVING COUNT(1) > 1) and a.school_id= b.id order by a.card_id ;

#学生查重
SELECT a.*,b.merchant_name FROM biz_student a,biz_merchant b WHERE card_id IN (SELECT card_id FROM biz_student GROUP BY card_id HAVING COUNT(1) > 1) and a.school_id= b.id order by a.card_id ;
去除左侧空格
select replace(ltrim(replace(‘001000’,‘0’,’ ‘)),’ ',‘0’)

先把0替换为空格，再去掉左边的空格，再把空格替换为0

select substring_index('换卡成功,老卡号:100036953新卡号:100036985','新卡号:',-1) //-1就是后面 结果是:100036985
select substring_index(substring_index('换卡成功,老卡号:100036953新卡号:100036985','老卡号:',-1),'新卡号',1);//老卡号:后面 新卡号前面 结果是:100036953