记录日常写的一些SQL

MySQL的相关SQL

1. group by返回多列

select DEPT_CODE deptCode,DEPT_NAME deptName,DEPT_TYPE deptType 
from PATIENT 
where ID in (select max(ID) from PATIENT group by DEPT_CODE) 
order by DEPT_CODE

2. mybatis中使用转义字符（不用between and是因为其不包含右边界）
   其中 lt： 小于 ，gt： 大于

<select id="querySchByCondition" resultMap="BaseResultMap">
        SELECT a.*,b.NAME from a
        LEFT JOIN b on a.CODE = b.ID
        <where>
            1=1
            <if test="sch.startDate != null and sch.startDate != ''">
                AND a.DATE &gt;= CONCAT(#{sch.startDate}, ' 00:00:00')
            </if>
            <if test="sch.endDate != null and sch.endDate != ''">
                AND a.DATE &lt;= CONCAT(#{sch.endDate}, ' 23:59:59')
            </if>
        </where>
        ORDER BY a.DATE asc
    </select>

Oracle的相关SQL

1. case when的使用

select PATIENT_NAME as name,CASE WHEN(GENDER = '男性') THEN 1 WHEN(GENDER='女性') THEN 0 END AS GENDER, BED as bedNo 
from PATIENT

2. between and和日期连用

select DEPT_CODE deptCode,count(*) count from PATIENT 
where OUT_TIME BETWEEN TO_DATE(#{beginTime}, 'YYYY-MM-DD HH24:MI:SS') 
AND TO_DATE(#{endTime}, 'YYYY-MM-DD HH24:MI:SS')  
group by DEPT_CODE

3. like和concat连用

select * from PATIENT 
where NAME like concat(concat('%',#{name}),'%')