技术问询：行对列表转扁平行列表及SQLite查找价差小于5美元的商品对

嘿，我来帮你搞定这两个SQLite的需求，直接上完整的实现方案和细节解释：

这个需求其实就是通过自连接把表中的每一行和其他行进行配对，生成所有可能的行对组合（包括自身配对，若不需要可额外加条件排除）。以下是完整的实现代码：

import sqlite3

# 创建内存数据库连接
db = sqlite3.connect(':memory:')
c = db.cursor()

# 创建商品表
c.execute('CREATE TABLE mytable (id integer, price integer, name text)')

# 初始化商品数据
NAMES = ['Item A', 'Item B', 'Item C', 'Item D', 'Item E', 'Item F']
PRICES = [100, 101, 102, 189, 190, 229]

# 插入数据
for i in range(len(NAMES)):
    c.execute('INSERT INTO mytable VALUES (?, ?, ?)', (i, PRICES[i], NAMES[i]))

# 生成扁平化的行对列表（所有可能的行组合）
c.execute('SELECT mt1.*, mt2.* FROM mytable mt1, mytable mt2')
row_pairs = c.fetchall()

# 打印前5组结果示例
for pair in row_pairs[:5]:
    print(pair)

db.close()

这段代码的核心是SELECT mt1.*, mt2.* FROM mytable mt1, mytable mt2，它通过交叉连接（CROSS JOIN）生成所有行的配对，把每行的所有字段扁平化输出成一行，比如(0,100,'Item A',0,100,'Item A')就是Item A和自身的配对结果。

要实现这个需求，我们依然用自连接，但需要加上价格差的过滤条件，同时为了避免重复的配对（比如Item A和Item B、Item B和Item A算同一对），可以添加mt1.id < mt2.id的条件。完整代码如下：

import sqlite3

db = sqlite3.connect(':memory:')
c = db.cursor()
c.execute('CREATE TABLE mytable (id integer, price integer, name text)')

NAMES = ['Item A', 'Item B', 'Item C', 'Item D', 'Item E', 'Item F']
PRICES = [100, 101, 102, 189, 190, 229]

for i in range(len(NAMES)):
    c.execute('INSERT INTO mytable VALUES (?, ?, ?)', (i, PRICES[i], NAMES[i]))

# 核心查询：找出价格差<5的商品对，且避免重复
query = '''
SELECT mt1.name AS item1, mt1.price AS price1, 
       mt2.name AS item2, mt2.price AS price2,
       ABS(mt1.price - mt2.price) AS price_diff
FROM mytable mt1
JOIN mytable mt2 ON mt1.id < mt2.id
WHERE ABS(mt1.price - mt2.price) < 5
'''

c.execute(query)
result = c.fetchall()

# 打印结果
print("价格差小于5美元的商品对：")
for row in result:
    print(f"{row[0]}(${row[1]}) 和 {row[2]}(${row[3]})，差价：${row[4]}")

db.close()

运行这段代码会输出：

价格差小于5美元的商品对：
Item A($100) 和 Item B($101)，差价：$1
Item A($100) 和 Item C($102)，差价：$2
Item B($101) 和 Item C($102)，差价：$1
Item D($189) 和 Item E($190)，差价：$1

这里的关键点：

• JOIN mytable mt2 ON mt1.id < mt2.id确保每个商品对只出现一次，避免重复
• ABS(mt1.price - mt2.price) < 5精确过滤出差价小于5美元的组合
• 用别名（AS）让查询结果的字段更易读

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