sql
凌悦love
从事软件后台开发Java开发
展开
-
mysql 两表数据交叉根据时间显示
select orderId, productId, productName, userId, productType, payMoney, payTime, orderStatus, vipPrice, orderType,discountsPrice,create_timefrom ((select a.id as orderId, a.product_id as productId, a.user_id as userId,a.product_name as produ.原创 2021-02-01 16:33:38 · 270 阅读 · 0 评论 -
sql中in查询改为关联查询
sql中in查询的查询速率很慢,我们可以做成左关联查询,提高查询速率SELECT a.icon_id,count(a.icon_id) as relationCount,d.title as relationWord FROM speak_user_icon aleft join ( SELECT *,count(works_id) from speak_user_icon where...原创 2020-04-21 15:31:28 · 869 阅读 · 0 评论 -
mysql 自动填充每周,每月,每年的日期查询为空补0
按天查询:select * from( SELECT t1.id,@cdate := date_add(@cdate,interval - 1 day) date , 0 as sumfrom (SELECT a.id,DATE_FORMAT(create_time,'%Y-%m-%d') as date,@cdate :=date_add(CURDATE(),interval + ...原创 2020-04-13 11:25:20 · 781 阅读 · 0 评论 -
sql根据时间生成状态排序
根据时间生成状态排序SELECT a.*,(CASEWHEN b.end_time <![CDATA[>=]]> now() AND start_time <![CDATA[<]]> now() THEN 1WHEN b.start_time <![CDATA[>]]> now() THEN 2WHEN b.end_time <...原创 2020-01-14 14:54:04 · 1066 阅读 · 0 评论 -
sql sum的使用
select *,SUM(learned_count) as sumcount from yshx_organization_learned_count WHERE month =5 and year = 2019 group by organization_id order by id desc参考博客https://blog.csdn.net/love_java_cc...原创 2019-06-04 22:19:51 · 4697 阅读 · 0 评论