省市县相关校验sql随笔

1.层级校验

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

CREATE TABLE regions (
    id INT PRIMARY KEY,
    name VARCHAR(255),
    parent_id INT,  -- 指向上一级区域的id，例如市的parent_id指向省的id，区的parent_id指向市的id
    level 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,

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

给定的省名:西藏自治区

SELECT
	count(1)
FROM  product pd
WHERE EXISTS
	(
		SELECT
			CASE  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 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.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), '%')
	)