不用递归就能一次性查出不同层级的数据
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