面试题：SQLite显示类型与存储类：复杂查询中的类型影响

Question

给定一个包含多种显示类型和存储类混合数据的SQLite表，设计一个复杂查询（至少涉及三个表的连接），查询结果需要根据不同存储类的数据进行特定的聚合操作（如对数值类型求和，对文本类型按长度排序等），并解释查询过程中SQLite如何处理不同类型的数据以得出正确结果。

Answer 1

示例表结构及数据

假设我们有三个表：Employees、Salaries 和 Departments。

• Employees 表包含员工信息，有 employee_id（数值类型），name（文本类型），department_id（数值类型，关联 Departments 表）。
• Salaries 表包含员工薪资信息，有 employee_id（数值类型，关联 Employees 表），salary（数值类型）。
• Departments 表包含部门信息，有 department_id（数值类型），department_name（文本类型）。

SQL 查询语句

SELECT 
    d.department_name,
    SUM(s.salary) AS total_salary,
    GROUP_CONCAT(e.name ORDER BY LENGTH(e.name) DESC) AS sorted_names
FROM 
    Employees e
JOIN 
    Salaries s ON e.employee_id = s.employee_id
JOIN 
    Departments d ON e.department_id = d.department_id
GROUP BY 
    d.department_name;

查询解释

1. 连接操作：
   • 通过 JOIN 操作将 Employees 表、Salaries 表和 Departments 表连接起来。Employees 表与 Salaries 表通过 employee_id 进行连接，Employees 表与 Departments 表通过 department_id 进行连接。
2. 聚合操作：
   • 数值类型聚合：对 Salaries 表中的 salary 列（数值类型）使用 SUM 函数进行求和操作，得出每个部门的总薪资。
   • 文本类型聚合：对 Employees 表中的 name 列（文本类型），先使用 GROUP_CONCAT 函数将每个部门的员工名字合并成一个字符串，并且在合并过程中通过 ORDER BY LENGTH(e.name) DESC 按照名字长度从长到短排序。

SQLite 处理不同类型数据的方式

• 数值类型：SQLite 对数值类型数据进行数学运算（如这里的 SUM 求和）时，会按照标准的数学规则进行计算。在存储和计算过程中，SQLite 会根据数据的大小和精度选择合适的内部表示方式。
• 文本类型：对于文本类型，在进行排序（如 ORDER BY LENGTH(e.name)）时，SQLite 会按照定义的排序规则进行操作。LENGTH 函数用于获取文本的长度，SQLite 根据这个长度值对文本进行排序。在 GROUP_CONCAT 操作中，文本按照排序后的顺序进行连接。