我该如何表达以下陈述:
SELECT column1 FROM db WHERE country in ('USA','BRA','CHN') and commodity='pork'
返回
value1, value2, value3 (where value1 is for USA, value2 for BRA, value3 for CHN)
到目前为止,我得到的最好结果是一个单列,其中所有的值都按照从数据库中获取的方式呈现。
Select a.column1,b.column2,c.column3 from
(SELECT column1 FROM db WHERE country ='USA' and commodity='pork') as a,
(SELECT column1 FROM db WHERE country ='BRA' and commodity='pork') as b,
(SELECT column1 FROM db WHERE country ='CHN' and commodity='pork') as c
这是一个演示问题。 让Python处理它更容易:
import sqlite3
conn = sqlite3.connect('/path/to/your.db')
curs = conn.cursor()
results = curs.execute("""SELECT column1
FROM db
WHERE country in ('USA','BRA','CHN')
AND commodity='pork';""").fetchall()
', '.join(str(result[0]) for result in results)
类似这样:
SELECT column1 as value1 FROM db WHERE country='USA' and commodity='pork'
UNION
SELECT column1 as value2 FROM db WHERE country='BRA' and commodity='pork'
UNION
SELECT column1 as value3 FROM db WHERE country='CHN' and commodity='pork'
这应该可以工作(在mysql中测试过)
SELECT GROUP_CONCAT(column1) FROM db WHERE country in ('USA','BRA','CHN') and commodity='pork'
我的测试是这样的(在mysql中)
SELECT GROUP_CONCAT( uid ) FROM pages where title in ('Home','Footer','home')
结果:
1,3,10
这是正确的。
select max(case country
when 'USA' then column1
else null
end) USA,
max(case country
when 'BRA' then column1
else null
end) BRA,
max(case country
when 'CHN' then column1
else null
end) CHN
FROM db
WHERE country in ('USA','BRA','CHN')
and commodity='pork'
对数据进行一次单独的遍历(单个查询),并聚合三行,换句话说什么也没有。
select
base.commodity
,USA_db.column1 as Commodity_USA
,BRA_db.column1 as Commodity_BRA
,CHN_db.column1 as Commodity_CHA
from (select 'pork' as commodity) as base
left join db as USA_db on base.commodity = USA_db.commodity and USA_db.country = 'USA'
left join db as BRA_db on base.commodity = BRA_db.commodity and BRA_db.country = 'BRA'
left join db as CHN_db on base.commodity = CHN_db.commodity and CHN_db.country = 'USA'
;
输出将是:
commodity Commodity_USA Commodity_BRA Commodity_CHA
---------- ------------- ------------- -------------
pork USA-pork BRA-pork USA-pork