如题,下面这个SQL语句,当数据很大时,就会卡死崩溃,请大家看看如何能优化。Select areaid, areaname, 'houseid' as hid, year, fzname,if(houproperties='0','公','私') as houproperties,projectid,project,sfyid,FORMAT(projectfee,2) as projectfee,
FORMAT(projectfee - Jan, 2) As Jan, FORMAT(projectfee - Feb, 2) As Feb, FORMAT(projectfee - Mar, 2) As Mar, FORMAT(projectfee - Apr, 2) As Apr,
FORMAT(projectfee - May, 2) As May, FORMAT(projectfee - Jun, 2) As Jun, FORMAT(projectfee - Jul, 2) As Jul, FORMAT(projectfee - Aug, 2) As Aug,
FORMAT(projectfee - Sep, 2) As Sep, FORMAT(projectfee - Oct, 2) As Oct, FORMAT(projectfee - Nov, 2) As Nov, FORMAT(projectfee - Dece, 2) As Dece,
FORMAT(projectfee*12 - (Case When sumyear is null Then 0 Else sumyear End), 2) As total
From (
Select tmpMain.*, areaname, fzname,houproperties,project,sfyid,
(Case tmpMain.projectid When '1001' Then rent When '1002' Then price*constarea Else price End) As projectfee /* Calculate the ShouldRent */
From (
Select h.areaid, h.houseid, h.year, projectid,
SUM(Case SUBSTR(sfqj, -2) When '01' Then jfje Else 0 End) As Jan,
SUM(Case SUBSTR(sfqj, -2) When '02' Then jfje Else 0 End) As Feb,
SUM(Case SUBSTR(sfqj, -2) When '03' Then jfje Else 0 End) As Mar,
SUM(Case SUBSTR(sfqj, -2) When '04' Then jfje Else 0 End) As Apr,
SUM(Case SUBSTR(sfqj, -2) When '05' Then jfje Else 0 End) As May,
SUM(Case SUBSTR(sfqj, -2) When '06' Then jfje Else 0 End) As Jun,
SUM(Case SUBSTR(sfqj, -2) When '07' Then jfje Else 0 End) As Jul,
SUM(Case SUBSTR(sfqj, -2) When '08' Then jfje Else 0 End) As Aug,
SUM(Case SUBSTR(sfqj, -2) When '09' Then jfje Else 0 End) As Sep,
SUM(Case SUBSTR(sfqj, -2) When '10' Then jfje Else 0 End) As Oct,
SUM(Case SUBSTR(sfqj, -2) When '11' Then jfje Else 0 End) As Nov,
SUM(Case SUBSTR(sfqj, -2) When '12' Then jfje Else 0 End) As Dece,
SUM(jfje) As sumyear /* All rent in the year */
From (
Select * From (
Select houseid,fzname,usearea,houproperties,sfyid,rent,areaid,year From house, ( Select distinct year From ( Select distinct SUBSTR(sfqj, 1, 4) as year From charge Union All Select CAST(YEAR(sysdate()) As CHAR) as year ) tmp11 /* Get all years, and include current year */) tmp1 /* That's cross join, try to get all years */
) hh, project p /* That's cross join, try to get all projects */
Where ((hh.houproperties = '0' and (projectid ='1001' or projectid = '1003')) /* Deal with the relationship between houproperties and projectid */
or (hh.houproperties = '1' and projectid ='1002'))
) h
Left Outer Join charge c On c.areaid = h.areaid and c.hid = h.houseid and c.project = h.projectid and h.year = SUBSTR(sfqj, 1, 4)
Group By h.areaid, h.houseid, h.year, projectid
) tmpMain /* Here we got the main result */
Left Outer Join house hh On tmpMain.areaid = hh.areaid and tmpMain.houseid = hh.houseid /* For calculate the ShouldRent */
Left Outer Join project pp On tmpMain.projectid = pp.projectid /* For calculate the ShouldRent, too */
Join area a On tmpMain.areaid = a.rowid /* For get the area's name */
) tmpAll
/* Here you can write some condition */
Order By areaid, houseid, year, projectid;
FORMAT(projectfee - Jan, 2) As Jan, FORMAT(projectfee - Feb, 2) As Feb, FORMAT(projectfee - Mar, 2) As Mar, FORMAT(projectfee - Apr, 2) As Apr,
FORMAT(projectfee - May, 2) As May, FORMAT(projectfee - Jun, 2) As Jun, FORMAT(projectfee - Jul, 2) As Jul, FORMAT(projectfee - Aug, 2) As Aug,
FORMAT(projectfee - Sep, 2) As Sep, FORMAT(projectfee - Oct, 2) As Oct, FORMAT(projectfee - Nov, 2) As Nov, FORMAT(projectfee - Dece, 2) As Dece,
FORMAT(projectfee*12 - (Case When sumyear is null Then 0 Else sumyear End), 2) As total
From (
Select tmpMain.*, areaname, fzname,houproperties,project,sfyid,
(Case tmpMain.projectid When '1001' Then rent When '1002' Then price*constarea Else price End) As projectfee /* Calculate the ShouldRent */
From (
Select h.areaid, h.houseid, h.year, projectid,
SUM(Case SUBSTR(sfqj, -2) When '01' Then jfje Else 0 End) As Jan,
SUM(Case SUBSTR(sfqj, -2) When '02' Then jfje Else 0 End) As Feb,
SUM(Case SUBSTR(sfqj, -2) When '03' Then jfje Else 0 End) As Mar,
SUM(Case SUBSTR(sfqj, -2) When '04' Then jfje Else 0 End) As Apr,
SUM(Case SUBSTR(sfqj, -2) When '05' Then jfje Else 0 End) As May,
SUM(Case SUBSTR(sfqj, -2) When '06' Then jfje Else 0 End) As Jun,
SUM(Case SUBSTR(sfqj, -2) When '07' Then jfje Else 0 End) As Jul,
SUM(Case SUBSTR(sfqj, -2) When '08' Then jfje Else 0 End) As Aug,
SUM(Case SUBSTR(sfqj, -2) When '09' Then jfje Else 0 End) As Sep,
SUM(Case SUBSTR(sfqj, -2) When '10' Then jfje Else 0 End) As Oct,
SUM(Case SUBSTR(sfqj, -2) When '11' Then jfje Else 0 End) As Nov,
SUM(Case SUBSTR(sfqj, -2) When '12' Then jfje Else 0 End) As Dece,
SUM(jfje) As sumyear /* All rent in the year */
From (
Select * From (
Select houseid,fzname,usearea,houproperties,sfyid,rent,areaid,year From house, ( Select distinct year From ( Select distinct SUBSTR(sfqj, 1, 4) as year From charge Union All Select CAST(YEAR(sysdate()) As CHAR) as year ) tmp11 /* Get all years, and include current year */) tmp1 /* That's cross join, try to get all years */
) hh, project p /* That's cross join, try to get all projects */
Where ((hh.houproperties = '0' and (projectid ='1001' or projectid = '1003')) /* Deal with the relationship between houproperties and projectid */
or (hh.houproperties = '1' and projectid ='1002'))
) h
Left Outer Join charge c On c.areaid = h.areaid and c.hid = h.houseid and c.project = h.projectid and h.year = SUBSTR(sfqj, 1, 4)
Group By h.areaid, h.houseid, h.year, projectid
) tmpMain /* Here we got the main result */
Left Outer Join house hh On tmpMain.areaid = hh.areaid and tmpMain.houseid = hh.houseid /* For calculate the ShouldRent */
Left Outer Join project pp On tmpMain.projectid = pp.projectid /* For calculate the ShouldRent, too */
Join area a On tmpMain.areaid = a.rowid /* For get the area's name */
) tmpAll
/* Here you can write some condition */
Order By areaid, houseid, year, projectid;
解决方案 »
- MYSQL所在机器磁盘满了以后,写入数据库会阻塞吗?
- 存储过程的问题 感谢
- mysql存储过程中如何lock table锁表?
- ...........
- 求一条SQL语句,各位大哥帮忙看看。。
- 一个mysql服务器里,新建一个数据库1000张表或10个数据库每个数据库100张表,哪个将来查询效率高
- 在线等待!!!MYSQL4.04-bate版的配置问题(送分)。。。。
- 请机器上有mysql的帮忙看看(116分)
- 系统崩溃,重装mysql5.0,直接覆盖data出错
- mysql已经修改为utf8编码,但是插入带有默认的汉字字段时插入不进去
- mysql多线程查询实现思路请教
- insert的时候,如何判断表是否存在
INSERT INTO `qftjb` VALUES ('38', '1', '南小巷', 'houseid', '2012', '李敏', '公', '1003', '卫生费', null, '6.00', '6.00', '6.00', '6.00', '6.00', '6.00', '6.00', '6.00', '6.00', '6.00', '6.00', '6.00', '6.00', '72.00');
INSERT INTO `qftjb` VALUES ('39', '2', '北区', 'houseid', '2011', '冯小彦', '私', '1002', '设施费', null, '14.38', '14.38', '14.38', '14.38', '14.38', '14.38', '14.38', '14.38', '14.38', '14.38', '14.38', '14.38', '14.38', '172.54');
INSERT INTO `qftjb` VALUES ('37', '1', '南小巷', 'houseid', '2012', '李敏', '公', '1001', '房租', null, '135.00', '135.00', '135.00', '135.00', '135.00', '135.00', '135.00', '135.00', '135.00', '135.00', '135.00', '135.00', '135.00', '1,620.00');
INSERT INTO `qftjb` VALUES ('38', '1', '南小巷', 'houseid', '2012', '李敏', '公', '1003', '卫生费', null, '6.00', '6.00', '6.00', '6.00', '6.00', '6.00', '6.00', '6.00', '6.00', '6.00', '6.00', '6.00', '6.00', '72.00');
INSERT INTO `qftjb` VALUES ('39', '2', '北区', 'houseid', '2011', '冯小彦', '私', '1002', '设施费', null, '14.38', '14.38', '14.38', '14.38', '14.38', '14.38', '14.38', '14.38', '14.38', '14.38', '14.38', '14.38', '14.38', '172.54');
修改了一下SQL。
insert into qftjb (areaid,areaname,hid,year,fzname,houproperties,projectid,project,sfyid,projectfee,Jan,Feb,Mar,Apr,May,Jun,Jul,Aug,Sep,Oct,Nov,Dece,total)
Select areaid, areaname, houseid as hid, year, fzname,if(houproperties='0','公','私') as houproperties,projectid,project,sfyid,FORMAT(projectfee,2) as projectfee,
FORMAT(projectfee - Jan, 2) As Jan, FORMAT(projectfee - Feb, 2) As Feb, FORMAT(projectfee - Mar, 2) As Mar, FORMAT(projectfee - Apr, 2) As Apr,
FORMAT(projectfee - May, 2) As May, FORMAT(projectfee - Jun, 2) As Jun, FORMAT(projectfee - Jul, 2) As Jul, FORMAT(projectfee - Aug, 2) As Aug,
FORMAT(projectfee - Sep, 2) As Sep, FORMAT(projectfee - Oct, 2) As Oct, FORMAT(projectfee - Nov, 2) As Nov, FORMAT(projectfee - Dece, 2) As Dece,
FORMAT(projectfee*12 - (Case When sumyear is null Then 0 Else sumyear End), 2) As total
From (
Select tmpMain.*, areaname, fzname,houproperties,project,sfyid,
(Case tmpMain.projectid When '1001' Then rent When '1002' Then price*constarea Else price End) As projectfee /* Calculate the ShouldRent */
From (
Select h.areaid, h.houseid, h.year, projectid,
SUM(Case SUBSTR(sfqj, -2) When '01' Then jfje Else 0 End) As Jan,
SUM(Case SUBSTR(sfqj, -2) When '02' Then jfje Else 0 End) As Feb,
SUM(Case SUBSTR(sfqj, -2) When '03' Then jfje Else 0 End) As Mar,
SUM(Case SUBSTR(sfqj, -2) When '04' Then jfje Else 0 End) As Apr,
SUM(Case SUBSTR(sfqj, -2) When '05' Then jfje Else 0 End) As May,
SUM(Case SUBSTR(sfqj, -2) When '06' Then jfje Else 0 End) As Jun,
SUM(Case SUBSTR(sfqj, -2) When '07' Then jfje Else 0 End) As Jul,
SUM(Case SUBSTR(sfqj, -2) When '08' Then jfje Else 0 End) As Aug,
SUM(Case SUBSTR(sfqj, -2) When '09' Then jfje Else 0 End) As Sep,
SUM(Case SUBSTR(sfqj, -2) When '10' Then jfje Else 0 End) As Oct,
SUM(Case SUBSTR(sfqj, -2) When '11' Then jfje Else 0 End) As Nov,
SUM(Case SUBSTR(sfqj, -2) When '12' Then jfje Else 0 End) As Dece,
SUM(jfje) As sumyear /* All rent in the year */
From (
Select * From (
Select houseid,fzname,usearea,houproperties,sfyid,rent,areaid,year From house, ( Select distinct year From ( Select distinct SUBSTR(sfqj, 1, 4) as year From charge Union All Select CAST(YEAR(sysdate()) As CHAR) as year ) tmp11 /* Get all years, and include current year */) tmp1 /* That's cross join, try to get all years */
) hh, project p /* That's cross join, try to get all projects */
Where ((hh.houproperties = '0' and (projectid ='1001' or projectid = '1003')) /* Deal with the relationship between houproperties and projectid */
or (hh.houproperties = '1' and projectid ='1002'))
) h
Left Outer Join charge c On c.areaid = h.areaid and c.hid = h.houseid and c.project = h.projectid and h.year = SUBSTR(sfqj, 1, 4)
Group By h.areaid, h.houseid, h.year, projectid
) tmpMain /* Here we got the main result */
Left Outer Join house hh On tmpMain.areaid = hh.areaid and tmpMain.houseid = hh.houseid /* For calculate the ShouldRent */
Left Outer Join project pp On tmpMain.projectid = pp.projectid /* For calculate the ShouldRent, too */
Join area a On tmpMain.areaid = a.rowid /* For get the area's name */
) tmpAll
/* Here you can write some condition */
Order By areaid, houseid, year, projectid;
结果:INSERT INTO `qftjb` VALUES ('36', '1', '南小巷', '1-2-12', '2011', '李敏', '公', '1003', '卫生费', null, '6.00', '6.00', '6.00', '6.00', '6.00', '6.00', '6.00', '6.00', '6.00', '6.00', '6.00', '6.00', '6.00', '72.00');
INSERT INTO `qftjb` VALUES ('37', '1', '南小巷', '1-2-12', '2012', '李敏', '公', '1001', '房租', null, '135.00', '135.00', '135.00', '135.00', '135.00', '135.00', '135.00', '135.00', '135.00', '135.00', '135.00', '135.00', '135.00', '1,620.00');
INSERT INTO `qftjb` VALUES ('39', '2', '北区', '1-2-13', '2011', '冯小彦', '私', '1002', '设施费', null, '14.38', '14.38', '14.38', '14.38', '14.38', '14.38', '14.38', '14.38', '14.38', '14.38', '14.38', '14.38', '14.38', '172.54');
2 DERIVED <derived3> ALL 8376
2 DERIVED hh ALL 2764
2 DERIVED pp eq_ref PRIMARY PRIMARY 12 tmpMain.projectid 1
2 DERIVED a eq_ref PRIMARY PRIMARY 4 tmpMain.areaid 1
3 DERIVED <derived4> ALL 8628 Using temporary; Using filesort
3 DERIVED c ALL 36
4 DERIVED p range PRIMARY PRIMARY 12 3 Using where
4 DERIVED <derived5> ALL 5338 Using where
5 DERIVED <derived6> ALL 2
5 DERIVED house ALL 2764
6 DERIVED <derived7> ALL 3 Using temporary
7 DERIVED charge ALL 36 Using temporary
8 UNION No tables used
UNION RESULT <union7,8> ALL 好了,贴好了
晕,最上面的贴不出来了,依次是:
id select_type table type possible_keys key key_len ref rows Extra
) hh, project p /* That's cross join, try to get all projects */
Where ((hh.houproperties = '0' and (projectid ='1001' or projectid = '1003')) /* Deal with the relationship between houproperties and projectid */
or (hh.houproperties = '1' and projectid ='1002'))