SQLite下梵语名词变格查询结果重排及参数化实现问题

1. 行列转换实现方案 #

不需要额外创建视图或虚拟表，借助SQLite支持的条件聚合语法即可直接实现行列转换，你需要的数维度是固定的3个枚举值，适配性很高。
具体查询语句如下：

SELECT 
    c.id AS case_id,
    c."case" AS case_name,
    MAX(CASE WHEN nf.number = 1 THEN f.form END) AS singular,
    MAX(CASE WHEN nf.number = 2 THEN f.form END) AS dual,
    MAX(CASE WHEN nf.number = 3 THEN f.form END) AS plural
FROM nouns n
JOIN nounforms nf ON n.id = nf.noun
JOIN forms f ON nf.form = f.id
JOIN cases c ON nf.case = c.id
WHERE n.name = 'rAma'
GROUP BY c.id, c."case"
ORDER BY c.id ASC;

上述查询按格ID分组，每个分组内用CASE WHEN匹配对应数的变格形式，最终返回8行3列的结果，缺失的变格组合会自动返回NULL。
如果你需要频繁复用该查询逻辑，也可以将过滤名词的条件去掉，封装为通用视图，后续查询时直接加过滤条件即可：

-- 创建通用变格查询视图
CREATE VIEW noun_declensions AS
SELECT 
    n.name AS noun_name,
    c.id AS case_id,
    c."case" AS case_name,
    MAX(CASE WHEN nf.number = 1 THEN f.form END) AS singular,
    MAX(CASE WHEN nf.number = 2 THEN f.form END) AS dual,
    MAX(CASE WHEN nf.number = 3 THEN f.form END) AS plural
FROM nouns n
JOIN nounforms nf ON n.id = nf.noun
JOIN forms f ON nf.form = f.id
JOIN cases c ON nf.case = c.id
GROUP BY n.name, c.id, c."case";

-- 后续查询任意名词直接过滤即可
SELECT * FROM noun_declensions WHERE noun_name = 'rAma' ORDER BY case_id;

2. 参数化替代方案 #

SQLite虽然不支持存储过程，但有以下几种纯数据库侧的参数化方案可以选择，都可以实现跨语言/环境复用：

• 预编译语句绑定参数：SQLite原生支持PREPARE预编译语法，所有主流语言的SQLite驱动也都支持参数绑定，既可以防止SQL注入，也能复用查询逻辑：

-- 预编译参数化查询
PREPARE get_declension (TEXT) AS
SELECT 
    c.id AS case_id,
    c."case" AS case_name,
    MAX(CASE WHEN nf.number = 1 THEN f.form END) AS singular,
    MAX(CASE WHEN nf.number = 2 THEN f.form END) AS dual,
    MAX(CASE WHEN nf.number = 3 THEN f.form END) AS plural
FROM nouns n
JOIN nounforms nf ON n.id = nf.noun
JOIN forms f ON nf.form = f.id
JOIN cases c ON nf.case = c.id
WHERE n.name = ?
GROUP BY c.id, c."case"
ORDER BY c.id;

-- 执行时传入目标名词即可
EXECUTE get_declension('rAma');
-- 释放预编译资源
DEALLOCATE get_declension;

• CTE封装参数：如果需要纯SQL脚本、不依赖驱动的参数绑定能力，可以把参数放在查询开头的公用表表达式中，修改参数仅需要调整一处，逻辑完全封装在SQL内：

WITH params AS (
    -- 仅修改此处的值即可查询不同名词
    SELECT 'rAma' AS target_noun
)
SELECT 
    c.id AS case_id,
    c."case" AS case_name,
    MAX(CASE WHEN nf.number = 1 THEN f.form END) AS singular,
    MAX(CASE WHEN nf.number = 2 THEN f.form END) AS dual,
    MAX(CASE WHEN nf.number = 3 THEN f.form END) AS plural
FROM params p
JOIN nouns n ON n.name = p.target_noun
JOIN nounforms nf ON n.id = nf.noun
JOIN forms f ON nf.form = f.id
JOIN cases c ON nf.case = c.id
GROUP BY c.id, c."case"
ORDER BY c.id;

• 高频查询场景下也可以选择注册SQLite自定义函数，用C或者对应宿主语言实现接收名词参数、返回变格结果的逻辑，复用性更强。

内容的提问来源于stack exchange，提问作者Jaldhar