在项目中运用到的部分mysql方法
时间截取
有时候需要group by date ,但是呢,日期是yyyy-MM-dd HH:mm:ss格式的,这样每个date都不一样,就不能Group by 了,所以需要截取一下
SELECT DATE_FORMAT(u.create_time, '%Y-%m-%d') time,
截取内容
有时候一个字段里面存储的东西代表多行意义,SUBSTRING:
SELECT
SUBSTRING(u.reason,1,1) a,
SUBSTRING(u.reason,2,1) b,
SUBSTRING(u.reason,3,1) c,
SUBSTRING(u.reason,4,1) d,
SUBSTRING(u.reason,5,1) e ,
FROM uuuuuu u
WHERE u.id='00000002'
mysql+mybatis动态获取时间
这个应该大家都会用:
<trim>
<if test="JKRQQ !=null and JKRQQ !='' ">
<![CDATA[
and t.time >=str_to_date(#{JKRQQ},'%Y-%m-%d')
]]>
</if>
<if test="JKRQZ !=null and JKRQZ !='' ">
<![CDATA[
and t.time <=str_to_date(#{JKRQZ},'%Y-%m-%d')
]]>
</if>
</trim>
mysql行转列
行转列,使用case when ,然后用sum方法,或者max
SELECT IFNULL(SUM(CASE WHEN t.a THEN 1 ELSE 0 END),0) a,
IFNULL(SUM(CASE WHEN t.b THEN 1 ELSE 0 END),0) b,
IFNULL(SUM(CASE WHEN t.c THEN 1 ELSE 0 END),0) c,
IFNULL(SUM(CASE WHEN t.d THEN 1 ELSE 0 END),0) d,
IFNULL(SUM(CASE WHEN t.e THEN 1 ELSE 0 END),0) e
FROM
(SELECT
SUBSTRING(u.reason,1,1) a,
SUBSTRING(u.reason,2,1) b,
SUBSTRING(u.reason,3,1) c,
SUBSTRING(u.reason,4,1) d,
SUBSTRING(u.reason,5,1) e
FROM uuuuuu u
WHERE u.id='2') t
WHERE 1=1
如果想对每个id进行统计
SELECT s.sid,s.sname,
MAX(s.chinese),MAX(s.math),MAX(s.english)
FROM
(SELECT sid,sname,
IFNULL((CASE WHEN cid=1001 THEN grade ELSE 0 END),0) chinese,
IFNULL((CASE WHEN cid=1002 THEN grade ELSE 0 END),0) math,
IFNULL((CASE WHEN cid=1003 THEN grade ELSE 0 END),0) english
FROM test)s
GROUP BY sid;