面试题:使用SQL选出下表中6个指标至少4个大于50的城市

题目：
青岛,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