复杂sql
时间：2021-07-01 10:21:17
帮助过：5人阅读
//根据人口数量查询
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
复杂sql
标签：class com sele eid div 数据查询 根据 分组 code
