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

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()

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