题目:
青岛,96,50,56,55,43,21
北京,74,16,96,29,54,4
南京,5,52,18,82,18,83
答案:
1.建表
drop table if exists t_index;
create table t_index(
city string,
od int,
sc int,
pc int,
hc int,
gc int,
ghc int
) row format delimited fields terminated by ',';
青岛,96,50,56,55,43,21
北京,74,16,96,29,54,4
南京,5,52,18,82,18,83
2.加载数据
load data locla inpath ‘/root/data/t_index.txt’ into table t_index;
3.SQL查询
select
tmp.city,
sum(tmp.t1 + tmp.t2 + tmp.t3 + tmp.t4 + tmp.t5 +tmp.t6) as s
from
(
select
city,
if(od>=50,1,0) as t1,
if(sc>=50,1,0) as t2,
if(pc>=50,1,0) as t3,
if(hc>=50,1,0) as t4,
if(gc>=50,1,0) as t5,
if(ghc>=50,1,0) as t6
from
t_index
) tmp
group by
tmp.city
having s>=4;
4.结果展示
tmp.city s
青岛 4