来自文档: D.3.3 GROUP BY optimization with no ORDER BY This eliminates the sorting step for the grouping operation if GROUP BY columns are the prefix of some base table index. 建立索引消除排序.
这个是我的视图,然后用单位分组,就不显示,不分组就能显示!!!!select* from (select * from sj_house_info a left join sj_community b on a.sc_id=b.sc_id where hi_fwlb=0) a1 left join (select shs_id,spm_id,sps_id,ssi_id,ssi_name,ssi_fs,c.shc_t1,c.shc_t2,ssi_type,jb_id,sp_cnbz,sp_price1,sp_bc,sp_sl,sp_price2 from sj_house_sort_list a left join (select * from sj_price where sp_status = 0 and fwlb_id = 0) b on a.shsl_fwxm = b.ssi_id left join (select * from sj_house_cs where shc_sort = 2) c on b.sps_id = c.shc_id left join sj_service_items d on a.shsl_fwxm = d.ssi_id) b1 on a1.zdylb_id =b1.shs_id and a1.zdyjg_id =b1.spm_id and a1.hi_sfsx =b1.sps_id and ( (b1.shc_t2 = 2 and ( (b1.ssi_type != 2) or (b1.ssi_type = 2 and a1.sc_cnbz = b1.sp_cnbz) ) ) or (b1.shc_t2 = 1 and ( (b1.ssi_type != 2 and a1.sc_level_id = b1.jb_id) or (b1.ssi_type = 2 and a1.sc_cnbz = b1.sp_cnbz) ) ) );
D.3.3 GROUP BY optimization with no ORDER BY
This eliminates the sorting step for the grouping operation if GROUP BY columns are the prefix of some base table index. 建立索引消除排序.
可能主机情况不一样,你的可能是小型机,楼主有可能就跑在1G的windows机器上, 呵呵,随便猜猜。按道理17万数据就算什么索引都排不建,最多也就两三分钟吧,楼主说的根本执行不了确实有些奇怪。可以对那三个字段建上符合索引先试试,楼主没有贴出where条件,如何建索引和这个也有关系。
(select shs_id,spm_id,sps_id,ssi_id,ssi_name,ssi_fs,c.shc_t1,c.shc_t2,ssi_type,jb_id,sp_cnbz,sp_price1,sp_bc,sp_sl,sp_price2 from sj_house_sort_list a
left join (select * from sj_price where sp_status = 0 and fwlb_id = 0) b on a.shsl_fwxm = b.ssi_id
left join (select * from sj_house_cs where shc_sort = 2) c on b.sps_id = c.shc_id
left join sj_service_items d on a.shsl_fwxm = d.ssi_id) b1 on a1.zdylb_id =b1.shs_id
and a1.zdyjg_id =b1.spm_id
and a1.hi_sfsx =b1.sps_id
and (
(b1.shc_t2 = 2
and (
(b1.ssi_type != 2)
or
(b1.ssi_type = 2 and a1.sc_cnbz = b1.sp_cnbz)
)
)
or
(b1.shc_t2 = 1
and (
(b1.ssi_type != 2 and a1.sc_level_id = b1.jb_id)
or
(b1.ssi_type = 2 and a1.sc_cnbz = b1.sp_cnbz)
)
)
);
用视图连接产生大约400万左右的数据
17万的数据变分组居然变成400万左右的数据你厉害,就算简单SELECT出来,在的客户端用个VB写个小程序也不至于做不出来吧
你的SQL没有分组呀,你用什么东西?