省市县相关校验sql随笔

1.层级校验

要判断一个给定的省、市、区（县）名字是否符合正确的层级关系,假设你的表结构如下：

CREATE TABLE regions (id INT PRIMARY KEY,name VARCHAR(255),parent_id INT,  -- 指向上一级区域的id，例如市的parent_id指向省的id，区的parent_id指向市的idlevel VARCHAR(50)  -- 标记层级，可能的值：'province', 'city', 'district'
);

则可以这么写

SELECT CASE WHEN d.id IS NOT NULL AND c.id IS NOT NULL AND p.id IS NOT NULL AND d.parent_id = c.id AND c.parent_id = p.id THEN '符合层级关系'ELSE '不符合层级关系'END AS result
FROM regions d
JOIN  regions c ON d.parent_id = c.id AND c.level = 'city'
JOIN  regions p ON c.parent_id = p.id AND p.level = 'province'
WHERE d.name = '给定的区名' AND c.name = '给定的市名' AND p.name = '给定的省名' AND d.level = 'district';

查询逻辑

1.自联结：通过 parent_id 字段与同一表中的其他记录联结，以找到区、市、和省的正确层级关系。
2.联结条件：
        d.parent_id = c.id：区的 parent_id 应该等于市的 id。
        c.parent_id = p.id：市的 parent_id 应该等于省的 id。
    WHERE 子句：指定区、市、和省的名字和层级。

可能不走ELSE,可以把JOIN换成LEFT JOIN,通过将 c.regionName = 'xxx' 和 p.regionName = 'xxx' 的条件放在 LEFT JOIN 中，可以避免 WHERE 子句排除掉非匹配的行，从而确保 ELSE 分支能被正确触发。

2.地址校验

查出产品地址是不含"西藏"的产品个数,假如有产品表:product,地址字段:addressName,

地址样例:专供（不含海南、西藏、北京）

给定的省名:西藏自治区

SELECTcount(1)
FROM  product pd
WHERE EXISTS(SELECTCASE  WHEN d.id IS NOT NULL AND c.id IS NOT NULL AND p.id IS NOT NULL  AND   d.parentId  = c.id AND c.parentId = p.id THEN '1' ELSE '0' END AS resultFROM  regions dJOIN  regions c ON d.parent_id = c.id AND c.level = 'city'JOIN  regions p ON c.parent_id = p.id AND p.level = 'province'WHEREd.level = 'district'AND d.regionName   LIKE CONCAT('%', '给定的区名', '%')AND c.regionName   LIKE CONCAT('%', '给定的市名', '%')AND p.regionName   LIKE CONCAT('%', '给定的省名', '%')AND pd.addressName LIKE CONCAT('%不含%', SUBSTRING('给定的省名', 1, 2), '%'))