复杂但实用的 SQL 语句

不用递归就能一次性查出不同层级的数据

select * from base_branch where 1=1
and branch_no in (
select '032005' branchNo
union 
SELECT
          branch_no branchNo
        FROM
            admin.base_branch
        WHERE
            upper_branch = '032005'
        union
        select a.branch_no branchNo from admin.base_branch a
        inner join (
        SELECT
            b.branch_no
        FROM
            admin.base_branch b
        WHERE
            upper_branch = '032005'
        ) b
        on a.upper_branch=b.branch_no
)
and branch_no in (
select '030000' branchNo
union 
SELECT
          branch_no branchNo
        FROM
            admin.base_branch
        WHERE
            upper_branch = '030000'
        union
        select a.branch_no branchNo from admin.base_branch a
        inner join (
        SELECT
            b.branch_no
        FROM
            admin.base_branch b
        WHERE
            upper_branch = '030000'
        ) b
        on a.upper_branch=b.branch_no


)

update 语句的字段用子查询结果集赋值

update pr_user_pft_base
set crt_type = 'M',
crt_date = (select ctl_value from admin.base_system where ctl_code='ACT_DATE'),
rcd_status = 'A',
chg_date = '2021-01-01 00:00:00';

跨表 join 删除重复数据

DELETE a
FROM
pr_user_pft_base a
INNER JOIN (
SELECT
GROUP_CONCAT( id ) duplicate_ids,
SUBSTRING_INDEX( GROUP_CONCAT( id ), ',', 1 ) del_id
FROM
( SELECT id, concat( stat_year, acct_date, base_type, brn_no, user_no, crt_type, crt_date ) str FROM pr_user_pft_base ) s
GROUP BY
str
HAVING
count( str ) > 1
) b ON a.id = b.del_id;

判断是否和已有的日期重叠

<if test="effDate != null and expDate != null">
                <![CDATA[
                and (
                   (${effDate} <= eff_date and ${expDate} >= eff_date)
                    or (${effDate} >= eff_date and ${expDate} <= exp_date)
                    or (${effDate} <= exp_date and ${expDate} >= exp_date)
                    or (${effDate} <= eff_date and ${expDate} >= exp_date)
                )
                ]]>
            </if>

传入生效日期effDate和失效日期expDate，丢到以下SQL中判断是否和已有的日期重叠。

假设表中已有的生效日期为20200501，失效日期为202000531，以下传入的日期与已有的日期发生重叠：

20200430，20200502

20200502，20200510

20200502，20200601

20200430，20200601

以下日期组合日期不会重叠：

20200101，20200430

20200601，20200630

根据经纬度定位信息查询附近地点

SELECT zip, primary_city,
       latitude, longitude, distance
  FROM (
 SELECT z.zip,
        z.primary_city,
        z.latitude, z.longitude,
        p.radius,
        p.distance_unit
                 * DEGREES(ACOS(COS(RADIANS(p.latpoint))
                 * COS(RADIANS(z.latitude))
                 * COS(RADIANS(p.longpoint - z.longitude))
                 + SIN(RADIANS(p.latpoint))
                 * SIN(RADIANS(z.latitude)))) AS distance
  FROM zip AS z
  JOIN (   /* these are the query parameters */
        SELECT  42.81  AS latpoint,  -70.81 AS longpoint,
                50.0 AS radius,      111.045 AS distance_unit
    ) AS p ON 1=1
  WHERE z.latitude
     BETWEEN p.latpoint  - (p.radius / p.distance_unit)
         AND p.latpoint  + (p.radius / p.distance_unit)
    AND z.longitude
     BETWEEN p.longpoint - (p.radius / (p.distance_unit * COS(RADIANS(p.latpoint))))
         AND p.longpoint + (p.radius / (p.distance_unit * COS(RADIANS(p.latpoint))))
 ) AS d
 WHERE distance <= radius
 ORDER BY distance
 LIMIT 15