Skip to main content
 首页 » 编程设计

python之从一对行的列表到一个平面的行列表

2025年01月19日10kerrycode

我想查找价格相差小于 5 美元的几件商品。它适用于:

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 WHERE ABS(mt1.price - mt2.price) < 5 AND mt1.id < mt2.id') 
 
for e in c.fetchall(): print e 

(0, 100, u'Item A', 1, 101, u'Item B')
(0, 100, u'Item A', 2, 102, u'Item C')
(1, 101, u'Item B', 2, 102, u'Item C')
(3, 189, u'Item D', 4, 190, u'Item E')

如何获得一个平面列表而不是一对列表?即:

(0, 100, u'Item A')      # 1st item of couple #1   
(1, 101, u'Item B')      # 2nd item of couple #1   
(0, 100, u'Item A')      # 1st item of couple #2   
(2, 102, u'Item C')      # 2nd item of couple #2   
(1, 101, u'Item B')      # 1st item of couple #3   
(2, 102, u'Item C')      # 2nd item of couple #3   
(3, 189, u'Item D')      # 1st item of couple #4   
(4, 190, u'Item E')      # 2nd item of couple #4   

请您参考如下方法:

你可以这样做:

select mt.* 
from mytable mt 
where exists (select 1 from mytable mt2 where abs(mt.price - mt2.price) < 5 and mt.id <> mt2.id); 

不过,这并没有按任何特定顺序排列它们。

如果您确实希望它们按顺序排列,那么反旋转也许是最好的选择:

SELECT (CASE WHEN n = 1 THEN mt1.id ELSE mt2.id END) as id, 
       (CASE WHEN n = 1 THEN mt1.price ELSE mt2.price END) as price, 
       (CASE WHEN n = 1 THEN mt1.name ELSE mt2.name END) as name 
 FROM mytable mt1 JOIN 
     mytable mt2  
     ON ABS(mt1.price - mt2.price) < 5 AND mt1.id < mt2.id CROSS JOIN 
     (SELECT 1 as n UNION ALL SELECT 2) n 
ORDER BY mt1.id, mt2.id;