如图如何用一句SQL查询出小麦产量高于水稻产量的省份

表名为farm

用pandas写的
数据库连接用的是sqlalchemy
sql的话我回去找一下

1. df=pd.read_sql('select * from farm', db)
   
2. df1=df[df['crops']!='mianhua']
   
3. df2=df1.pivot(index='province',columns='crops',values='weight')
   
4. df2[df2['xiaomai']>df2['shuidao']]

复制代码

create table temptable select province,group_concat(if(crops='shuidao',weight,NULL)) as 'shuidao',group_concat(if(crops='xiaomai',weight,NULL)) as 'xiaomai' from farm group by province;
select * from temptable where cast(xiaomai as decimal(10,2))  > cast(shuidao as decimal(10,2));
用了两句，参考一下

sheeboard 发表于 2019-11-4 20:45
create table temptable select province,group_concat(if(crops='shuidao',weight,NULL)) as 'shuidao',gr ...

谢谢,要按省分组然后对水稻和小麦的行聚合为列,最后对聚合结果作筛选.

select province,
             MAX(case crops when 'xiaomai' then weight else 0 end) as 'xiaomai',
             MAX(case crops when 'shuidao' then weight else 0 end) as 'shuidao'
      from fuck
      group by province having xiaomai>shuidao

select t1.province from table as t1, table as t2
where t1.crops = 'xiaomai' and t2.crops = 'shuidao' and t1.province = t2.province
and t1.weight > t2.weight