MySQL 经典语句

王林-wlin

已于 2022-07-22 10:13:24 修改

阅读量277

点赞数 1

分类专栏： MySQL 经典语句文章标签： mysql 数据库 database

于 2022-03-15 17:59:46 首次发布

本文为CSDN博主「王林」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。

本文链接：https://blog.csdn.net/qq_45912025/article/details/123508372

版权

MySQL 经典语句专栏收录该内容

1 篇文章 0 订阅

订阅专栏

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'
    
   -- and UNIX_TIMESTAMP(info.endDate) < UNIX_TIMESTAMP(NOW()) =true 
    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) &lt; 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 &gt;= #{queryDto.startDate}
            </if>
            <if test="queryDto.endDate !=null and queryDto.endDate !=''">
                and info.endDate &lt;= #{queryDto.endDate}
            </if>
            <if test="queryDto.isWarning  !=null and queryDto.isWarning">
                and UNIX_TIMESTAMP(info.endDate) &lt; UNIX_TIMESTAMP(NOW()) =true
            </if>
            <if test="queryDto.isWarning  !=null and !queryDto.isWarning">
                and UNIX_TIMESTAMP(info.endDate) &lt; UNIX_TIMESTAMP(NOW()) =false
            </if>
        </where>
    </select>

    <select id="getFacilityMaintenanceList" resultType="com.vortex.cloud.jcss.reborn.vo.FacilityMaintenanceVO">
        SELECT
        info.*,UNIX_TIMESTAMP(info.endDate) &lt; 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 类

  /**
     * 分页查询
     *
     * @param page
     * @param queryDto
     * @return
     */
    IPage<FacilityMaintenanceVO> getFacilityMaintenanceList(Page<FacilityMaintenanceVO> page, @Param("queryDto") FacilityMaintenanceQueryDTO queryDto);
     /**
     * 获取所有预警设施数据
     *
     * @param ew
     * @return
     */
    List<FacilityMaintenanceVO> getFacilityMaintenanceList(@Param("ew") QueryWrapper<FacilityMaintenanceQueryDTO> ew);

三、集合条件查询

xml 文件

 <select id="getStoreroomListByIds" parameterType="string"
            resultType="com.xjrsoft.module.customer.inv_shift_head.invShiftHead.dto.InvStoreroomDto">
        SELECT info.*
        FROM (
        SELECT isr.F_Id AS storeroom_id,
        isr.OPERATING_UNIT_ID as unit_id,
        mou.NAME as unit_name
        FROM `inv_storeroom` isr
        LEFT JOIN `md_operating_unit` AS mou ON isr.OPERATING_UNIT_ID = mou.F_Id
        WHERE isr.F_DeleteMark = 0
        AND isr.F_EnabledMark = 1
        AND mou.F_DeleteMark = 0
        AND mou.F_EnabledMark = 1
        ) AS info
        <where>
            <if test="ids != null and ids != ''">
                info.storeroom_id in
                <foreach collection="ids" item="item" close=")" open="(" separator=",">
                    #{item}
                </foreach>
            </if>
        </where>
    </select>

mapper 类

    /**
     * 根据库房id获取经营单元
     *
     * @param ids
     * @return
     */
    List<InvStoreroomDto> getStoreroomListByIds(@Param("ids") List<String> ids);

四、分页日期状态查询–case(when)

SQL语句

SELECT F_Id as id,
        `TYPE`,
        `NUMBER`,
        `NAME`,
        OPERATING_UNIT_NAME,
        OPERATING_UNIT_ID,
        CONTRACT_LEADER_ID,
        PICTURE_ID,
        AMOUNT,
        CURRENCY_CODE,
        ACCOUNT_USER_ID,
        ACCOUNT_USER_NAME,
        CUSTOMER_ID,
        CUSTOMER_NAME,
        DEFAULT_STOREROOM_ID,
        DEFAULT_STOREROOM_NAME,
        DEFAULT_CARRIER_CODE,
        DEFAULT_CARRIER_NAME,
        SIGN_DATE,
        BEGIN_DATE,
        END_DATE,
        CONTENT_DESCRIPTION,
        EXCEPTION_DESCRIPTION,
        DESCRIPTION,
        VERSION,
        IS_ARCHIVED,
        ARCHIVE_TIME,
        ARCHIVE_USER_ID,
        ARCHIVE_USER_NAME,
        IS_SUBMIT,
        SUBMIT_USER_ID,
        SUBMIT_USER_NAME,
        SUBMIT_TIME,
        IS_APPROVED,
        APPROVE_USER_ID,
        APPROVE_USER_NAME,
        APPROVE_TIME,
        APPROVE_RESULT,
        FILE_NUMBER,
        ROW_VERSION,
        F_DeleteMark,
        F_EnabledMark,
        F_CreateDate,
        F_CreateUserId,
        F_CreateUserName,
        F_ModifyDate,
        F_ModifyUserId,
        F_ModifyUserName,
			  case 
				when UNIX_TIMESTAMP(sc.BEGIN_DATE) > UNIX_TIMESTAMP(CURDATE()) then 0
				when UNIX_TIMESTAMP(sc.BEGIN_DATE) <= UNIX_TIMESTAMP(CURDATE()) and UNIX_TIMESTAMP(CURDATE()) <= UNIX_TIMESTAMP(sc.END_DATE) then 1
				when UNIX_TIMESTAMP(CURDATE()) > UNIX_TIMESTAMP(sc.END_DATE) then 2
				else 3 end as `STATUS`
        FROM `sm_contract` as sc
		where sc.F_DeleteMark = 0

mapper文件

  /**
     * 分页
     *
     * @param page
     * @param ew
     * @param queryDto
     * @return
     */
    List<SmContractListVo> selectPageList(IPage<SmContractListVo> page, @Param("ew") QueryWrapper<SmContract> ew,
                                          @Param("queryDto") SmContractListDto queryDto);

xml文件

   <select id="selectPageList"
            parameterType="com.xjrsoft.module.customer.sm_contract.sm_contract.dto.SmContractListDto"
            resultType="com.xjrsoft.module.customer.sm_contract.sm_contract.vo.SmContractListVo">
        SELECT F_Id as id,
        `TYPE`,
        `NUMBER`,
        `NAME`,
        OPERATING_UNIT_NAME,
        OPERATING_UNIT_ID,
        CONTRACT_LEADER_ID,
        PICTURE_ID,
        AMOUNT,
        CURRENCY_CODE,
        ACCOUNT_USER_ID,
        ACCOUNT_USER_NAME,
        CUSTOMER_ID,
        CUSTOMER_NAME,
        DEFAULT_STOREROOM_ID,
        DEFAULT_STOREROOM_NAME,
        DEFAULT_CARRIER_CODE,
        DEFAULT_CARRIER_NAME,
        SIGN_DATE,
        BEGIN_DATE,
        END_DATE,
        CONTENT_DESCRIPTION,
        EXCEPTION_DESCRIPTION,
        DESCRIPTION,
        VERSION,
        IS_ARCHIVED,
        ARCHIVE_TIME,
        ARCHIVE_USER_ID,
        ARCHIVE_USER_NAME,
        IS_SUBMIT,
        SUBMIT_USER_ID,
        SUBMIT_USER_NAME,
        SUBMIT_TIME,
        IS_APPROVED,
        APPROVE_USER_ID,
        APPROVE_USER_NAME,
        APPROVE_TIME,
        APPROVE_RESULT,
        FILE_NUMBER,
        ROW_VERSION,
        F_DeleteMark,
        F_EnabledMark,
        F_CreateDate,
        F_CreateUserId,
        F_CreateUserName,
        F_ModifyDate,
        F_ModifyUserId,
        F_ModifyUserName,
        case
        when UNIX_TIMESTAMP(sc.BEGIN_DATE) &gt; UNIX_TIMESTAMP(#{queryDto.nowTime}) then 0
        when UNIX_TIMESTAMP(sc.BEGIN_DATE) &lt;= UNIX_TIMESTAMP(#{queryDto.nowTime}) and
        UNIX_TIMESTAMP(#{queryDto.nowTime})&lt;= UNIX_TIMESTAMP(sc.END_DATE) then 1
        when UNIX_TIMESTAMP(#{queryDto.nowTime}) > UNIX_TIMESTAMP(sc.END_DATE) then 2
        else 3 end as `STATUS`
        FROM `sm_contract` as sc
        where F_DeleteMark = 0
        <if test="queryDto.status !=null and queryDto.status != ''">
            <if test="queryDto.status == 0">
                and UNIX_TIMESTAMP(sc.BEGIN_DATE) &gt; UNIX_TIMESTAMP(#{queryDto.nowTime})
                and UNIX_TIMESTAMP(sc.BEGIN_DATE) &lt;= UNIX_TIMESTAMP(sc.END_DATE)
            </if>

            <if test="queryDto.status == 1">
                and UNIX_TIMESTAMP(sc.BEGIN_DATE) &lt;= UNIX_TIMESTAMP(#{queryDto.nowTime})
                and UNIX_TIMESTAMP(#{queryDto.nowTime}) &lt;= UNIX_TIMESTAMP(sc.END_DATE)
            </if>

            <if test="queryDto.status == 2">
                and UNIX_TIMESTAMP(#{queryDto.nowTime}) &gt; UNIX_TIMESTAMP(sc.END_DATE)
            </if>

            <if test="queryDto.status == 3">
                and STATUS = 3
            </if>
        </if>
        <if test="ew.sqlSegment !=null and ew.sqlSegment != ''">
            and ${ew.sqlSegment}
        </if>
        <if test="queryDto.BEGIN_DATE !=null and queryDto.BEGIN_DATE != ''">
            and UNIX_TIMESTAMP(sc.BEGIN_DATE) &gt; UNIX_TIMESTAMP(#{queryDto.BEGIN_DATE})
        </if>
        <if test="queryDto.END_DATE !=null and queryDto.END_DATE != ''">
            and UNIX_TIMESTAMP(sc.END_DATE) &lt;= UNIX_TIMESTAMP(#{queryDto.END_DATE})
        </if>
        order by F_CreateDate desc
    </select>