从围棋收官到秦楚大战的数据库SQL实现(中）

2

量地制谋，攻取有方

秦孝公：吾儿所言甚是，来，且让我用SQL神功验证一番。

WITH capture_process AS (
SELECT 
        c.id, c.name, c.types, c.sizes,
CASE
WHEN c.types ='双先'THEN'秦'
WHEN c.types ='秦先手'THEN'秦'
WHEN c.types ='楚先手'THEN
CASE 
WHENMOD(ROW_NUMBER() OVER (PARTITIONBY c.types ORDERBY c.sizes DESC), 2) =1THEN'秦' 
ELSE'楚' 
END
WHEN c.types ='后手'THEN
CASE 
WHENMOD(ROW_NUMBER() OVER (PARTITIONBY c.types ORDERBY c.sizes DESC), 2) =1THEN'楚' 
ELSE'秦' 
END
ENDAS captured_by,
CASE
WHEN c.types ='双先'THEN1
WHEN c.types ='秦先手'THEN2
WHEN c.types ='楚先手'THEN3
WHEN c.types ='后手'THEN4
ENDAS type_order,
ROW_NUMBER() OVER (PARTITIONBY c.types ORDERBY c.sizes DESC) AS size_rank
FROM cities c
),
ordered_cities AS (
SELECT 
        captured_by,
        name ||' ('|| types ||', 大小: '|| TO_CHAR(sizes) ||')'AS city_info,
        type_order,
CASE 
WHEN types IN ('楚先手', '后手') THEN size_rank
ELSE0
ENDAS sub_order,
        sizes  -- 保留sizes字段，用于后续计算总面积
FROM capture_process
)
SELECT
    captured_by AS category,
COUNT(*) AS count,
SUM(sizes) AS total_size,  -- 新增：计算每个国家占领城池的总面积
LISTAGG(city_info, ', ') WITHINGROUP (
ORDERBY type_order, sub_order, sizes DESC
    ) AS cities
FROM ordered_cities
GROUPBY captured_by
ORDERBY 
CASEWHEN captured_by ='秦'THEN1ELSE2END,
COUNT(*) DESC

SQL 核心逻辑说明：

1. capture_process 子查询
   此查询依据城池类型与大小，推断各城归属。规则为：
        a. 双先和秦先手的城池由秦直接占领。
        b. 楚先手之城以大小为序，交替分配给秦楚。通过ROW_NUMBER() 函数对大小排名，并通过MOD() 函数将其分配给两国。
         c. 后手的城池同样依据大小顺序交替分配。

2. ordered_cities 子查询
   此部分对城池按类型顺序及大小进行排序，并为最终结果展示做准备。这里使用了 LISTAGG() 进行字符串聚合，输出每一方占领的城池详情。

3. 最终查询

   据城池归属聚合数据，列出秦楚各得城池，依大小排序呈现结果。

商鞅：大王的SQL可真厉害，围棋正是如此对弈，恭喜两位公子学有所成！

秦孝公：商君，心服否？寡人近得高人指点，棋艺大进。诸位容我引荐，此乃来自两千年之后的围棋职业五段高手，胡傲华老师。

商鞅：收官？我等可刚总结过收官策略，还据此在八城之争中取得显著成果。

胡傲华：商君，八城之争其实还有提升空间。

商鞅、嬴驷、嬴华闻言，更觉困惑。

秦孝公：胡老师所言不虚。我大秦应该改抢山水2，而非高原2。

商鞅：不可，如此我军仅得五城，非六城矣。

嬴华：然也。若不逆收，楚军将连取山水1、山水2，再得高原1，共三城。我军仅得五城。

胡傲华：诸位不妨再细想一番。

嬴驷：哦，高原2面积180，即便我军只得五城，总疆域仍会增加。

嬴华：确实如此！我略作计算，秦军五城疆域可达470，楚军三城仅为200。我军将较楚军多出270之地（而非之前的190），见表如下。

商鞅：妙哉！270-190=80，攻城顺序一变，我大秦竟可多得80土地！

秦孝公：围棋收官亦是如此。寡人曾弃逆收而取后手，商君可曾注意？

商鞅：想起来了，大王高明！

胡傲华：一般而言，逆收优先于后手。然有一例外：若后手价值逾逆收两倍，可优先取之。

嬴驷：懂了，楚先手山水2面积为85，倍之也仅170，而后手的高原2面积为180，超山水2两倍有余，故优先取之。

胡傲华：驷公子慧眼如炬，围棋收官正合此理。我在先前诸位梳理的流程图上略作改进，若言之前图示为业余3段水平，新图则已达业余4段之境。如下所示，请诸位反复细品，定有收获。

秦孝公：诸位，胡老师教我等，后手大于逆收两倍者可先取。今寡人以SQL神功演之，看结果如何。

WITH city_data AS (
    SELECT 
        id, name, types, sizes,
CASE
            WHEN types = '双先' THEN 1
            WHEN types = '秦先手' THEN 2
            WHEN types = '楚先手' THEN 3
            WHEN types = '后手' THEN 4
        END AS type_order,
        ROW_NUMBER() OVER (PARTITION BY types ORDER BY sizes DESC) AS size_rank
    FROM cities
),
strategic_choice AS (
    SELECT 
        id,name,types,sizes,type_order,size_rank,
        MAX(CASE WHEN types = '后手' THEN sizes ELSE 0 END) OVER () AS max_后手_size,
        MAX(CASE WHEN types = '楚先手' THEN sizes ELSE 0 END) OVER () AS max_楚先手_size
    FROM city_data
),
capture_process AS (
    SELECT 
        id, name, types, sizes, type_order, size_rank,
CASE
            WHEN types IN ('双先', '秦先手') THEN '秦'
            WHEN types = '后手' AND sizes = max_后手_size 
                 AND sizes > 2 * max_楚先手_size THEN '秦'
            WHEN types = '楚先手' AND 
                 NOT EXISTS (SELECT 1 FROM strategic_choice 
                             WHERE types = '后手' AND sizes > 2 * max_楚先手_size) THEN '秦'
            ELSE '楚'
        END AS captured_by
    FROM strategic_choice
)
SELECT
    captured_by AS category,
    COUNT(*) AS count,
    SUM(sizes) AS total_size,
    LISTAGG(name || ' ('|| types || ', 大小: '|| TO_CHAR(sizes) || ')', ', ') 
    WITHIN GROUP (ORDER BY type_order, sizes DESC) AS cities
FROM capture_process
GROUP BY captured_by
ORDER BY 
    CASE WHEN captured_by = '秦' THEN 1 ELSE 2 END;

SQL 核心逻辑说明（突出后手价值大于逆收两倍的实现）：

1. 在strategic_choice子查询中，我们计算了关键值：

2. MAX(CASEWHEN types ='后手'THEN sizes ELSE0END) OVER () AS max_后手_size,
   MAX(CASEWHEN types ='楚先手'THEN sizes ELSE0END) OVER () AS max_楚先手_size
   

   这里计算了最大的后手城池大小和最大的楚先手城池大小。

3. 在capture_process子查询中，关键的判断逻辑如下：

4. CASE
   WHEN types ='后手'AND sizes = max_后手_size 
   AND sizes >2* max_楚先手_size THEN'秦'
   WHEN types ='楚先手'AND 
   NOTEXISTS (SELECT1FROM strategic_choice 
   WHERE types ='后手'AND sizes >2* max_楚先手_size) THEN'秦'
   ELSE'楚'
   ENDAS captured_by

这个CASE语句实现了核心逻辑：

• 如果是后手城池，且其大小等于最大后手城池大小，并且大于最大楚先手城池大小的两倍，则秦国占领。

• 如果是楚先手城池，但不存在大于楚先手两倍的后手城池，则秦国占领。

• 其他情况下，楚国占领。

• 这种实现方式确保了当后手城池的价值超过楚先手城池两倍时，秦国会优先选择后手城池，而不是按常规顺序选择楚先手城池。

商鞅：我悟了！大王，可再战一局？