sql问题清单
mybatis之sql问题清单
mysql之sql问题清单
sql执行顺序
书写sql的顺序是
select
from
join on
where
group by
having
order by
limit
mysql执行sql的顺序为
from
join on
where
group by
having
select
order by
limit
mysql创建复杂一点的计算列
CREATE TABLE log_sync_plan_data(
planLogId BIGINT PRIMARY KEY NOT NULL auto_increment,
syncType int NOT NULL DEFAULT 0,
tablesCnt int NOT NULL,
finishedCnt int NOT NULL DEFAULT 0,
test decimal(10,2) GENERATED ALWAYS AS ((`syncType ` * `tablesCnt `)) VIRTUAL
-- test DECIMAL(18,4) generated always AS (syncType *tablesCnt )
beginTime datetime NOT NULL DEFAULT NOW(),
endTime datetime NOT NULL DEFAULT '1900-01-01',
elapsedSeconds INT generated always AS (case when endTime<beginTime then -1 ELSE timestampdiff(SECOND,endTime,beginTime) END) VIRTUAL,
errMsg varchar(4000) NULL,
success INT generated always AS( case when endTime<beginTime OR ifnull(errMsg,'')<>'' OR tablesCnt<>finishedCnt then 0 else 1 end) VIRTUAL
);
mysql5.7后查询多个列+group by就会报sql_mode=only_full_group_by错误
SELECT 要查询的所有字段列表没有包含在 GROUP BY 语句中
SQL 日期函数
1. DATE_FORMAT() 函数格式化时间
https://www.w3school.com.cn/sql/func_date_format.asp
2. datediff()函数计算时间差
https://www.w3school.com.cn/sql/func_datediff_mysql.asp
多条件分组统计
SELECT date_format((time),'%Y.%m') as timeGroup,count(id),emotion from yuqing_option_info
WHERE time BETWEEN '2018-08-08' and '2019-08-08' GROUP BY timeGroup,emotion
SELECT date_format((OD_CREATETIME),'%m-%d') as days,sum(odi_num) as nums,sum(sp_selling_price) as prices FROM (SELECT OD_CREATETIME,odi_num,sp_selling_price from (
select OD_CREATETIME,odi_num,sp_selling_price
from sys_order,sys_order_item,sys_supplies
where sys_order_item.OD_ID=sys_order.ID
AND sys_supplies.ID=sys_order_item.SP_ID
AND sys_order.OD_TYPE=#{orderType}
and sys_order.O_ID=#{hId}
and sys_order.OD_CREATETIME BETWEEN #{startTime} and #{endTime}
) as d ) as c
GROUP BY days
SELECT sum(i.ODI_NUM * s.SP_SELLING_PRICE) AS number,
DATE(o.OD_CREATETIME) AS time
FROM sys_order_item i
LEFT JOIN sys_order o on o.ID=i.OD_ID
LEFT JOIN sys_hospital h on o.F_O_ID=h.ID
LEFT JOIN sys_supplies s on i.SP_ID=s.ID
WHERE o.OD_TYPE =#{orderType}
GROUP BY DATE(o.OD_CREATETIME)
ORDER BY DATE(o.OD_CREATETIME) DESC
LIMIT 30
//模糊查询
@Query(nativeQuery = true,value = “select s.name name,sa.expiring_time expiringTime from subject
s\n” +
" inner JOIN subject_auths sa ON sa.subject_id=s.id \n" +
" inner JOIN subject_favorites sf ON sf.subject_auths_id=sa.id\n" +
" where sa.user_id=?1 and (s.bond_code like CONCAT(’%’,?2,’%’) or s.wind_code like CONCAT(’%’,?2,’%’) OR s.name like CONCAT(’%’,?2,’%’) ) “,countQuery = “select count(*) expiringTime from subject
s\n” +
" inner JOIN subject_auths sa ON sa.subject_id=s.id \n” +
" inner JOIN subject_favorites sf ON sf.subject_auths_id=sa.id\n" +
" where sa.user_id=?1 and (s.comp_bode like CONCAT(’%’,?2,’%’) or s.wind_code like CONCAT(’%’,?2,’%’) OR s.name like CONCAT(’%’,?2,’%’) ) "
)