在 Swift 中使用 SQL 组合人员和地址数据

摘要

在本篇文章中，我们将讨论如何结合两个表——Person 和 Address，以便生成包含每个人的姓名和地址信息的结果表。如果某人的地址信息不存在，则对应的城市和州返回为 null。我们将用 Swift 和 SQLite 数据库实现这一功能，并详细分析其逻辑。

描述

问题描述

我们有两张表：

Person 表：

PersonId 是主键，用于存储每个人的基本信息，包括姓和名。

Address 表：

AddressId 是主键，存储每个人的城市和州信息，PersonId 是外键关联到 Person 表。

目标： 报告 Person 表中每个人的 FirstName、LastName、City 和 State。如果某人的地址信息在 Address 表中缺失，则其 City 和 State 返回 null。

示例输入与输出

输入

Person 表：

Address 表：

输出

解释

• PersonId = 1 在 Address 表中没有对应的地址信息，返回 null。
• PersonId = 2 在 Address 表中找到其地址信息。

Swift 代码解决方案

以下是用 Swift 和 SQLite 数据库实现的代码：

import SQLite3

def fetchPersonWithAddress() {
    // Database setup
    var db: OpaquePointer?
    let databasePath = ":memory:" // Use in-memory database for demo
    if sqlite3_open(databasePath, &db) != SQLITE_OK {
        print("Failed to open database")
        return
    }

    // Create tables
    let createPersonTable = """
    CREATE TABLE Person (
        PersonId INTEGER PRIMARY KEY,
        FirstName TEXT,
        LastName TEXT
    );
    """

    let createAddressTable = """
    CREATE TABLE Address (
        AddressId INTEGER PRIMARY KEY,
        PersonId INTEGER,
        City TEXT,
        State TEXT
    );
    """

    if sqlite3_exec(db, createPersonTable, nil, nil, nil) != SQLITE_OK ||
       sqlite3_exec(db, createAddressTable, nil, nil, nil) != SQLITE_OK {
        print("Failed to create tables")
        sqlite3_close(db)
        return
    }

    // Insert sample data
    let insertPersonData = """
    INSERT INTO Person (PersonId, FirstName, LastName) VALUES
    (1, 'Allen', 'Wang'),
    (2, 'Bob', 'Alice');
    """

    let insertAddressData = """
    INSERT INTO Address (AddressId, PersonId, City, State) VALUES
    (1, 2, 'New York City', 'New York'),
    (2, 3, 'Leetcode', 'California');
    """

    if sqlite3_exec(db, insertPersonData, nil, nil, nil) != SQLITE_OK ||
       sqlite3_exec(db, insertAddressData, nil, nil, nil) != SQLITE_OK {
        print("Failed to insert data")
        sqlite3_close(db)
        return
    }

    // Query data with LEFT JOIN
    let query = """
    SELECT Person.FirstName, Person.LastName, Address.City, Address.State
    FROM Person
    LEFT JOIN Address ON Person.PersonId = Address.PersonId;
    """

    var statement: OpaquePointer?
    if sqlite3_prepare_v2(db, query, -1, &statement, nil) == SQLITE_OK {
        print("FirstName | LastName | City          | State")
        while sqlite3_step(statement) == SQLITE_ROW {
            let firstName = String(cString: sqlite3_column_text(statement, 0))
            let lastName = String(cString: sqlite3_column_text(statement, 1))
            let city = sqlite3_column_text(statement, 2).flatMap { String(cString: $0) } ?? "Null"
            let state = sqlite3_column_text(statement, 3).flatMap { String(cString: $0) } ?? "Null"
            print("\(firstName) | \(lastName) | \(city) | \(state)")
        }
    } else {
        print("Failed to execute query")
    }

    sqlite3_finalize(statement)
    sqlite3_close(db)
}

fetchPersonWithAddress()

代码分析

1. 表创建与数据插入

   • 使用 SQL 创建 Person 和 Address 表，并插入示例数据。

2. 数据查询

   • 通过 LEFT JOIN 查询数据。左连接确保即使 Address 表中没有对应的 PersonId，Person 表的记录也会出现在结果中。

3. 结果展示

   • 使用 sqlite3_step 遍历查询结果，并处理可能的 null 值。

示例测试及结果

输出结果

FirstName | LastName | City          | State
Allen     | Wang     | Null          | Null
Bob       | Alice    | New York City | New York

时间复杂度

• 查询操作: LEFT JOIN 的时间复杂度为 O(n + m)，其中 n 和 m 分别是 Person 和 Address 表的大小。

空间复杂度

• 额外空间: 用于存储查询结果，复杂度为 O(k)，其中 k 是结果行数。

总结

本文通过 Swift 和 SQLite 实现了对两个表的合并查询，并处理了地址缺失的情况。代码逻辑清晰，适合实际应用场景如用户数据整合或报告生成。