1 //根据人口数量查询
2 select * from tb_gdp g , tb_province p where g.provinceId=p.id and p.pnum > 10 and p.pnum<200000 and provinceId=1
3
4 //计算总利润率
5 select * from tb_gdp g , tb_province p where g.provinceId=p.id and p.pnum > 10 and p.pnum<200000 and provinceId=1
6
7 // 根据计算字段进行范围查询
8 select *, (g.agIncome + g.industryIncome - g.agInvest - g.industryInvest)/( g.agInvest + g.industryInvest )*100 as rate
9 from tb_gdp g , tb_province p
10 where g.provinceId=p.id and p.pnum > 10 and p.pnum<200000 and provinceId=1
11 and ((g.agIncome + g.industryIncome - g.agInvest - g.industryInvest)/( g.agInvest + g.industryInvest )*100) > 1 and ( (g.agIncome + g.industryIncome - g.agInvest - g.industryInvest)/( g.agInvest + g.industryInvest )*100 )< 100
12
13 //使用子查询的方式
14 select * from ( select g.*, p.`name` ,p.pnum, (g.agIncome + g.industryIncome - g.agInvest - g.industryInvest)/( g.agInvest + g.industryInvest )*100 as rate
15 from tb_gdp g , tb_province p
16 where g.provinceId=p.id ) as myview
17 where pnum > 10 and pnum<200000 and provinceId=1
18 and rate > 1 and rate < 100
19
20
21 //根据汇总数据查询明细
22 select * from ( select g.*, p.`name` ,p.pnum, (g.agIncome + g.industryIncome - g.agInvest - g.industryInvest)/( g.agInvest + g.industryInvest )*100 as rate
23 from tb_gdp g , tb_province p
24 where g.provinceId=p.id ) as myview
25
26 根据分组做条件查询明细
27 select * from ( select g.*, p.`name` ,p.pnum, (g.agIncome + g.industryIncome - g.agInvest - g.industryInvest)/( g.agInvest + g.industryInvest )*100 as rate
28 from tb_gdp g , tb_province p
29 where g.provinceId=p.id ) as myview
30 WHERE provinceId in (
31 SELECT g.provinceId from tb_gdp g GROUP BY g.provinceId HAVING sum(g.agIncome + g.industryIncome) > 1000
32 )
33
34
35 //汇总
36 select sum(pnum),sum(industryIncome),sum(agInvest) sum_ai, myview.month from ( select g.*, p.`name` ,p.pnum, (g.agIncome + g.industryIncome - g.agInvest - g.industryInvest)/( g.agInvest + g.industryInvest )*100 as rate
37 from tb_gdp g , tb_province p
38 where g.provinceId=p.id ) as myview
39
40 WHERE myview.month >2 and myview.month < 4
41 GROUP BY myview.month
42 having sum_ai>100 and sum_ai<200000
