MySQL 经典语句
一、查询语句
使用技术
- 多表查询，
- case when函数，
- if() 函数，
- unix_timestamp()函数，
- date_add()函数，
- 左连接
SQL语句
SELECT
info.*,UNIX_TIMESTAMP(info.endDate) < UNIX_TIMESTAMP(NOW()) AS isWarning
FROM
(
SELECT
jf.id AS facility_Id,
jf.`name` AS facility_name,
jft.name AS
facility_class_name,
jf.`type_id` AS facility_class_id,
jf.`division_id`,
jf.`manage_unit_id`,
jft.maintain_period,
jft.is_regular_maintenance,
jf.`recent_maintain_date`,
jf.`default_maintain_date`,
jf.address,
jf.geo_location,
CASE
WHEN jft.`maintain_period` = 'WEEK' THEN DATE_ADD(IF(jf.`recent_maintain_date` IS NULL, jf.`default_maintain_date`, jf.`recent_maintain_date`), INTERVAL 1 WEEK)
WHEN jft.`maintain_period` = 'MONTH' THEN DATE_ADD(IF(jf.`recent_maintain_date` IS NULL, jf.`default_maintain_date`, jf.`recent_maintain_date`), INTERVAL 1 MONTH)
WHEN jft.`maintain_period` = 'QUARTER' THEN DATE_ADD(IF(jf.`recent_maintain_date` IS NULL, jf.`default_maintain_date`, jf.`recent_maintain_date`), INTERVAL 3 MONTH)
WHEN jft.`maintain_period` = 'HALF_YEAR' THEN DATE_ADD(IF(jf.`recent_maintain_date` IS NULL, jf.`default_maintain_date`, jf.`recent_maintain_date`), INTERVAL 6 WEEK)
WHEN jft.`maintain_period` = 'YEAR' THEN DATE_ADD(IF(jf.`recent_maintain_date` IS NULL, jf.`default_maintain_date`, jf.`recent_maintain_date`), INTERVAL 1 YEAR)
END AS endDate
FROM
`jcss_facility` jf
LEFT JOIN `jcss_facility_type` jft ON
jf.`type_id` = jft.`id`
WHERE
jft.`is_regular_maintenance` = TRUE
AND jf.`deleted` = FALSE
AND jft.`deleted` = FALSE) AS info
二、分页条件查询
使用技术
- 多表查询，
- case when函数，
- if() 函数，
- unix_timestamp()函数，
- date_add()函数，
- 左连接
mysql 语句
sql 语句一
SELECT
info.*,UNIX_TIMESTAMP(info.endDate) < UNIX_TIMESTAMP(NOW()) AS isWarning
FROM
(
SELECT
jf.id AS facility_Id,
jf.`name` AS facility_name,
jft.name AS
facility_class_name,
jf.`type_id` AS facility_class_id,
jf.`division_id`,
jf.`manage_unit_id`,
jft.maintain_period,
jft.is_regular_maintenance,
jf.`recent_maintain_date`,
jf.`default_maintain_date`,
jf.address,
jf.geo_location,
jf.deleted as facility_deleted,
CASE
WHEN jft.`maintain_period` = 'WEEK' THEN DATE_ADD(IF(jf.`recent_maintain_date` IS NULL, jf.`default_maintain_date`, jf.`recent_maintain_date`), INTERVAL 1 WEEK)
WHEN jft.`maintain_period` = 'MONTH' THEN DATE_ADD(IF(jf.`recent_maintain_date` IS NULL, jf.`default_maintain_date`, jf.`recent_maintain_date`), INTERVAL 1 MONTH)
WHEN jft.`maintain_period` = 'QUARTER' THEN DATE_ADD(IF(jf.`recent_maintain_date` IS NULL, jf.`default_maintain_date`, jf.`recent_maintain_date`), INTERVAL 3 MONTH)
WHEN jft.`maintain_period` = 'HALF_YEAR' THEN DATE_ADD(IF(jf.`recent_maintain_date` IS NULL, jf.`default_maintain_date`, jf.`recent_maintain_date`), INTERVAL 6 WEEK)
WHEN jft.`maintain_period` = 'YEAR' THEN DATE_ADD(IF(jf.`recent_maintain_date` IS NULL, jf.`default_maintain_date`, jf.`recent_maintain_date`), INTERVAL 1 YEAR)
END AS endDate
FROM
`jcss_facility` jf
LEFT JOIN `jcss_facility_type` jft ON
jf.`type_id` = jft.`id`
WHERE
jft.`is_regular_maintenance` = TRUE
AND jf.`deleted` = FALSE
AND jft.`deleted` = FALSE) AS info
where info.endDate > '2020-04.-12' and info.endDate <'2023-01.-12'
and UNIX_TIMESTAMP(info.endDate) < UNIX_TIMESTAMP(NOW()) =true
sql 语句二
SELECT
info.*,UNIX_TIMESTAMP(info.endDate) < UNIX_TIMESTAMP(NOW()) AS isWarning
FROM
(
SELECT
jf.id AS facility_Id,
jf.tenant_id,
jf.`name` AS facility_name,
jft.name AS
facility_class_name,
jf.`type_id` AS facility_class_id,
jf.`division_id`,
jf.`manage_unit_id`,
jft.maintain_period,
jft.is_regular_maintenance,
ifnull(jf.`recent_maintain_date`, jf.`default_maintain_date`) as recent_maintain_date,
jf.`default_maintain_date`,
jf.address,
jf.geo_location,
jf.deleted as facility_deleted,
CASE
WHEN jft.`maintain_period` = 'WEEK' THEN DATE_ADD(IF(jf.`recent_maintain_date` IS NULL, jf.`default_maintain_date`, jf.`recent_maintain_date`), INTERVAL 1 WEEK)
WHEN jft.`maintain_period` = 'MONTH' THEN DATE_ADD(IF(jf.`recent_maintain_date` IS NULL, jf.`default_maintain_date`, jf.`recent_maintain_date`), INTERVAL 1 MONTH)
WHEN jft.`maintain_period` = 'QUARTER' THEN DATE_ADD(IF(jf.`recent_maintain_date` IS NULL, jf.`default_maintain_date`, jf.`recent_maintain_date`), INTERVAL 3 MONTH)
WHEN jft.`maintain_period` = 'HALF_YEAR' THEN DATE_ADD(IF(jf.`recent_maintain_date` IS NULL, jf.`default_maintain_date`, jf.`recent_maintain_date`), INTERVAL 6 WEEK)
WHEN jft.`maintain_period` = 'YEAR' THEN DATE_ADD(IF(jf.`recent_maintain_date` IS NULL, jf.`default_maintain_date`, jf.`recent_maintain_date`), INTERVAL 1 YEAR)
END AS endDate
FROM
`jcss_facility` jf
LEFT JOIN `jcss_facility_type` jft ON
jf.`type_id` = jft.`id`
WHERE
jft.`is_regular_maintenance` = TRUE
AND jf.`deleted` = FALSE
AND jft.`deleted` = FALSE) AS info
where info.endDate > '2020-04.-12' and info.endDate <'2023-01.-12'
order by info.default_maintain_date desc
xml 文件
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.vortex.cloud.jcss.reborn.dao.FacilityMaintenanceMapper">
<select id="getFacilityMaintenancePage" resultType="com.vortex.cloud.jcss.reborn.vo.FacilityMaintenanceVO"
parameterType="com.vortex.cloud.jcss.reborn.dto.query.FacilityMaintenanceQueryDTO">
SELECT
info.*,UNIX_TIMESTAMP(info.endDate) < UNIX_TIMESTAMP(NOW()) AS isWarning
FROM
(
SELECT
jf.id AS facility_Id,
jf.tenant_id,
jf.`name` AS facility_name,
jft.name AS
facility_class_name,
jf.`type_id` AS facility_class_id,
jf.`division_id`,
jf.`manage_unit_id`,
jft.maintain_period,
jft.is_regular_maintenance,
ifnull(jf.`recent_maintain_date`, jf.`default_maintain_date`) as recent_maintain_date,
jf.`default_maintain_date`,
jf.address,
jf.geo_location,
jf.deleted as facility_deleted,
CASE
WHEN jft.`maintain_period` = 'WEEK' THEN DATE_ADD(IF(jf.`recent_maintain_date` IS NULL,
jf.`default_maintain_date`, jf.`recent_maintain_date`), INTERVAL 1 WEEK)
WHEN jft.`maintain_period` = 'MONTH' THEN DATE_ADD(IF(jf.`recent_maintain_date` IS NULL,
jf.`default_maintain_date`, jf.`recent_maintain_date`), INTERVAL 1 MONTH)
WHEN jft.`maintain_period` = 'QUARTER' THEN DATE_ADD(IF(jf.`recent_maintain_date` IS NULL,
jf.`default_maintain_date`, jf.`recent_maintain_date`), INTERVAL 3 MONTH)
WHEN jft.`maintain_period` = 'HALF_YEAR' THEN DATE_ADD(IF(jf.`recent_maintain_date` IS NULL,
jf.`default_maintain_date`, jf.`recent_maintain_date`), INTERVAL 6 WEEK)
WHEN jft.`maintain_period` = 'YEAR' THEN DATE_ADD(IF(jf.`recent_maintain_date` IS NULL,
jf.`default_maintain_date`, jf.`recent_maintain_date`), INTERVAL 1 YEAR)
END AS endDate
FROM
`jcss_facility` jf
LEFT JOIN `jcss_facility_type` jft ON
jf.`type_id` = jft.`id`
WHERE
jft.`is_regular_maintenance` = TRUE
AND jf.`deleted` = FALSE
AND jft.`deleted` = FALSE) AS info
<where>
info.facility_deleted = false
<if test="queryDto.facilityClassId !=null and queryDto.facilityClassId !=''">
and info.facility_class_id =#{queryDto.facilityClassId}
</if>
<if test="queryDto.facilityName !=null and queryDto.facilityName !=''">
and info.facility_name LIKE concat("%",#{queryDto.facilityName},"%")
</if>
<if test="queryDto.divisionId !=null and queryDto.divisionId !=''">
and info.division_id =#{queryDto.divisionId}
</if>
<if test="queryDto.manageUnitId !=null and queryDto.manageUnitId !=''">
and info.manage_unit_id =#{queryDto.manageUnitId}
</if>
<if test="queryDto.startDate !=null and queryDto.startDate !=''">
and info.endDate >= #{queryDto.startDate}
</if>
<if test="queryDto.endDate !=null and queryDto.endDate !=''">
and info.endDate <= #{queryDto.endDate}
</if>
<if test="queryDto.isWarning !=null and queryDto.isWarning">
and UNIX_TIMESTAMP(info.endDate) < UNIX_TIMESTAMP(NOW()) =true
</if>
<if test="queryDto.isWarning !=null and !queryDto.isWarning">
and UNIX_TIMESTAMP(info.endDate) < UNIX_TIMESTAMP(NOW()) =false
</if>
</where>
</select>
<select id="getFacilityMaintenanceList" resultType="com.vortex.cloud.jcss.reborn.vo.FacilityMaintenanceVO">
SELECT
info.*,UNIX_TIMESTAMP(info.endDate) < UNIX_TIMESTAMP(NOW()) AS isWarning
FROM
(
SELECT
jf.id AS facility_Id,
jf.tenant_id,
jf.`name` AS facility_name,
jft.name AS
facility_class_name,
jf.`type_id` AS facility_class_id,
jf.`division_id`,
jf.`manage_unit_id`,
jft.maintain_period,
jft.is_regular_maintenance,
ifnull(jf.`recent_maintain_date`, jf.`default_maintain_date`) as recent_maintain_date,
jf.`default_maintain_date`,
jf.address,
jf.geo_location,
jf.deleted as facility_deleted,
CASE
WHEN jft.`maintain_period` = 'WEEK' THEN DATE_ADD(IF(jf.`recent_maintain_date` IS NULL,
jf.`default_maintain_date`, jf.`recent_maintain_date`), INTERVAL 1 WEEK)
WHEN jft.`maintain_period` = 'MONTH' THEN DATE_ADD(IF(jf.`recent_maintain_date` IS NULL,
jf.`default_maintain_date`, jf.`recent_maintain_date`), INTERVAL 1 MONTH)
WHEN jft.`maintain_period` = 'QUARTER' THEN DATE_ADD(IF(jf.`recent_maintain_date` IS NULL,
jf.`default_maintain_date`, jf.`recent_maintain_date`), INTERVAL 3 MONTH)
WHEN jft.`maintain_period` = 'HALF_YEAR' THEN DATE_ADD(IF(jf.`recent_maintain_date` IS NULL,
jf.`default_maintain_date`, jf.`recent_maintain_date`), INTERVAL 6 WEEK)
WHEN jft.`maintain_period` = 'YEAR' THEN DATE_ADD(IF(jf.`recent_maintain_date` IS NULL,
jf.`default_maintain_date`, jf.`recent_maintain_date`), INTERVAL 1 YEAR)
END AS endDate
FROM
`jcss_facility` jf
LEFT JOIN `jcss_facility_type` jft ON
jf.`type_id` = jft.`id`
WHERE
jft.`is_regular_maintenance` = TRUE
AND jf.`deleted` = FALSE
AND jft.`deleted` = FALSE) AS info
<where>
info.facility_deleted = false
<if test="ew.sqlSegment !=null and ew.sqlSegment !=''">
and ${ew.sqlSegment}
</if>
</where>
</select>
</mapper>
mapper 类
IPage<FacilityMaintenanceVO> getFacilityMaintenanceList(Page<FacilityMaintenanceVO> page, @Param("queryDto") FacilityMaintenanceQueryDTO queryDto);
List<FacilityMaintenanceVO> getFacilityMaintenanceList(@Param("ew") QueryWrapper<FacilityMaintenanceQueryDTO> ew);
关注林哥，持续更新哦！！！★,°:.☆(￣▽￣)/$:.°★ 。
|