1.是否有必要写这么多的查询条件? 2.是否可以去掉like使用其它方式替代, like 会极大地降低效率,不是点把点的哈, 3.sql 2000还是2005,如果是2000,把确定的限制条件如(CVID='1234567' and UserSex =1 and HasAbroad =1 and age>30 and age <=35 等) 提前, 2005略过此句. 4.看看执行计划,看能否在相应的3中的一些条件上加上索引,
眼晕 ~ 没看到有括号,但看到有or,还是or like '%xx%',索引已无效 那就无法优化语句要优化的话,还是去优化业务或其它方法来变通吧.
select * from table where Category like %% and NowArea like %220% and AccountState like %9050% and SelectedArea like %16010,16020,125,16080,16070% and RefreshTime like %180% and Category like %dfgdfgdfg% and CurrPositionLevel like %50% and Degree like %3% and MajorCategory like %305% and MajorCategoryName like %fdgdfgfd% and WorkKind like %1% and ExpectSalaryType like %1000% and Experience like %3% and LangType1 like %7% and HasAbroad =1 and age>30 and age<=35 and Category like %dfgdg% or WorkFunction like %dfgdg% or WorkFunction like %dfgdg% or SelfValue like %dfgdg% or SkillSpec like %dfgdg% or Industry like %1107000% or Industry like %3600% or Industry like %800% or Industry like %1500% or Industry like %700% or SelectedCategory like %1009004% or SelectedCategory like %1009013% or SelectedCategory like %1009003% or SelectedCategory like %1009011% or SelectedCategory like %1009012% or SelectedIndustry like %1111000% or SelectedIndustry like %1110000% or SelectedIndustry like %1300% or SelectedIndustry like %3200% or SelectedIndustry like %1119000% or LangType2 like %7% or LangType2 like %7% or Langlevel1 like %2% or Langlevel2 like %2% or Langlevel3 like %2% 我从新发一下,怕大家看得头晕, 帮我出出主意,要怎么改才是最好的. 先谢谢大家了.
select * from table where Category like %% and NowArea like %220% and AccountState like %9050% and SelectedArea like %16010,16020,125,16080,16070% and RefreshTime like %180% and Category like %dfgdfgdfg% --1 and CurrPositionLevel like %50% and Degree like %3% and MajorCategory like %305% and MajorCategoryName like %fdgdfgfd% and WorkKind like %1% and ExpectSalaryType like %1000% and Experience like %3% and LangType1 like %7% and HasAbroad =1 and age>30 and age <=35 and Category like %dfgdg% --2 1,2取一个就行了,or WorkFunction like %dfgdg% or WorkFunction like %dfgdg% --取一个就行了 or SelfValue like %dfgdg% or SkillSpec like %dfgdg% or Industry like %1107000% or Industry like %3600% or Industry like %800% or Industry like %1500% or Industry like %700% or SelectedCategory like %1009004% or SelectedCategory like %1009013% or SelectedCategory like %1009003% -- 简化为 SelectedCategory like %10090% or SelectedCategory like %1009011% or SelectedCategory like %1009012% or SelectedIndustry like %1111000% or SelectedIndustry like %1110000% or SelectedIndustry like %1300% or SelectedIndustry like %3200% or SelectedIndustry like %1119000% or LangType2 like %7% or LangType2 like %7% -- 取一个就行了 or Langlevel1 like %2% or Langlevel2 like %2% or Langlevel3 like %2% ......
日....哪有这个的where条件.楼主我真是佩服啊,全文索引
我经过考虑后又对以前的语句进行了修改,改后的 结果是这 样的 select * from tablename where Category ='123456' and NowArea ='220 and AccountState='9050' and RefreshTime ='180' and Category like %dfgdfgdfg% and CurrPositionLevel ='50' and Degree ='3' and MajorCategory ='305' and MajorCategoryName ='fdgdfgfd' and WorkKind ='1' and ExpectSalaryType ='1000' and Experience ='3' and HasAbroad =1 and between 30 and 35and SelectedArea= '16010' or SelectedArea= '16020' or SelectedArea= '125' or SelectedArea='16080' or SelectedArea= '16070' or Industry ='1107000' or Industry ='3600' or Industry ='800' or Industry ='1500 ' or Industry ='700 ' or SelectedCategory ='1009004' or SelectedCategory ='1009013' or SelectedCategory ='1009003' or SelectedCategory ='1009011' or SelectedCategory ='1009012' or SelectedIndustry ='1111000' or SelectedIndustry ='1110000' or SelectedIndustry ='1300' or SelectedIndustry ='3200' or SelectedIndustry ='1119000' and LangType1 ='7' or LangType2 ='7' or LangType2 ='7' or Langlevel1 ='2' or Langlevel2 ='2' or Langlevel3 ='2' and Category like %dfgdg% or WorkFunction like %dfgdg% or WorkFunction like %dfgdg% or SelfValue like %dfgdg% or SkillSpec like %dfgdg% 请大家多多帮忙看看. 多多留下你们的意见.
试试看 select * from tablename where Category ='123456' and NowArea ='220' and AccountState='9050' and RefreshTime ='180' and Category like %dfgdfgdfg% and CurrPositionLevel ='50' and Degree ='3' and MajorCategory ='305' and MajorCategoryName ='fdgdfgfd' and WorkKind ='1' and ExpectSalaryType ='1000' and Experience ='3' and HasAbroad =1 and between 30 and 35 and SelectedArea in('16010','16020','125' ,'16080','16070') or Industry in('1107000','3600','800', '1500 ','700 ' ) or SelectedCategory in('1009004','1009013' ) and LangType1 ='7' or LangType2 ='7' or LangType2 ='7' or Langlevel1 ='2' or Langlevel2 ='2' or Langlevel3 ='2' and Category like %dfgdg% or WorkFunction like %dfgdg% or WorkFunction like %dfgdg% or SelfValue like %dfgdg% or SkillSpec like %dfgdg% 先将数据量大的查询出来,再进行这样的操作,我想会好很多
select * from tablename where Category ='123456' and NowArea ='220' and AccountState='9050' and RefreshTime ='180' and Category like %dfgdfgdfg% and CurrPositionLevel ='50' and Degree ='3' and MajorCategory ='305' and MajorCategoryName ='fdgdfgfd' and WorkKind ='1' and ExpectSalaryType ='1000' and Experience ='3' and HasAbroad =1 and between 30 and 35 and SelectedArea in('16010','16020','125' ,'16080','16070') or Industry in('1107000','3600','800', '1500 ','700 ' ) or SelectedCategory in('1009004','1009013' ) and LangType1 ='7' or LangType2 ='7' or LangType2 ='7' or Langlevel1 ='2' or Langlevel2 ='2' or Langlevel3 ='2' and Category like %dfgdg% or WorkFunction like %dfgdg% or WorkFunction like %dfgdg% or SelfValue like %dfgdg% or SkillSpec like %dfgdg% 先将数据量大的查询出来,再进行这样的操作,我想会好很多
select * from tablename where Category ='123456' and NowArea ='220' and AccountState='9050' and RefreshTime ='180' and Category like %dfgdfgdfg% and CurrPositionLevel ='50' and Degree ='3' and MajorCategory ='305' and MajorCategoryName ='fdgdfgfd' and WorkKind ='1' and ExpectSalaryType ='1000' and Experience ='3' and HasAbroad =1 and between 30 and 35 and SelectedArea in('16010','16020','125' ,'16080','16070') or Industry in('1107000','3600','800', '1500 ','700 ' ) or SelectedCategory in('1009004','1009013' ) and LangType1 ='7' or LangType2 ='7' or LangType2 ='7' or Langlevel1 ='2' or Langlevel2 ='2' or Langlevel3 ='2' and Category like %dfgdg% or WorkFunction like %dfgdg% or WorkFunction like %dfgdg% or SelfValue like %dfgdg% or SkillSpec like %dfgdg% 先将数据量大的查询出来,再进行这样的操作,我想会好很多
我今天又改了改 准备用这个作为查询 select * from Ly_Resume where exists(select * from Ly_Resume where SelectedArea in('16010','16020','125' ,'16080','16070') or Industry in('1107000','3600','800', '1500 ','700 ' ) or SelectedCategory in('1009004','1009013' ) )and exists(select * from Ly_Resume where Category like '%dfgdg% 'or WorkFunction like '%dfgdg%' or SelfValue like '%dfgdg% 'or SkillSpec like' %dfgdg% ') and exists(select * from Ly_Resume where Category ='123456'and NowArea ='220'and AccountState='9050' --and RefreshTime ='2008-05-06' and Category like '%dfgdfgdfg% 'and CurrPositionLevel ='50' and Degree ='3' and MajorCategory ='305' and MajorCategoryName ='fdgdfgfd' and WorkKind ='1' and ExpectSalaryType ='2' and Experience ='3' and HasAbroad =1 ) and exists(select * from Ly_Resume where LangType1 ='7' or LangType2 ='7' or LangType2 ='7' or Langlevel1 ='2' or Langlevel2 ='2' or Langlevel3 ='2' )请大家看看还有什么问题.有什么地方可以在优化一下.
2.是否可以去掉like使用其它方式替代, like 会极大地降低效率,不是点把点的哈,
3.sql 2000还是2005,如果是2000,把确定的限制条件如(CVID='1234567' and UserSex =1 and HasAbroad =1 and age>30 and age <=35 等)
提前, 2005略过此句.
4.看看执行计划,看能否在相应的3中的一些条件上加上索引,
没看到有括号,但看到有or,还是or like '%xx%',索引已无效
那就无法优化语句要优化的话,还是去优化业务或其它方法来变通吧.
我的想法是把所有的and 语句写在一起查询出的结果集组成一张表
把所有的or语句写在一起查询出的结果集组成另张一张表,然后把两张表中相同的数据过滤
你们帮我想想这样可以吗?还有就是我想要的是模糊查询如果
把所有的like换成子表查询exists
能进行模糊查询吗?
我没有试过这种写法。
是字符串的一个代号而已,没什么的.你不要想得太复杂了
你做当做字符串看就可以了,我数据库用数字替代可以节省空间,用字符怕机器承受不了.
and NowArea like %220%
and AccountState like %9050%
and SelectedArea like %16010,16020,125,16080,16070%
and RefreshTime like %180%
and Category like %dfgdfgdfg%
and CurrPositionLevel like %50%
and Degree like %3%
and MajorCategory like %305%
and MajorCategoryName like %fdgdfgfd%
and WorkKind like %1%
and ExpectSalaryType like %1000%
and Experience like %3%
and LangType1 like %7%
and HasAbroad =1
and age>30
and age<=35
and Category like %dfgdg% or WorkFunction like %dfgdg%
or WorkFunction like %dfgdg%
or SelfValue like %dfgdg%
or SkillSpec like %dfgdg%
or Industry like %1107000%
or Industry like %3600%
or Industry like %800%
or Industry like %1500%
or Industry like %700%
or SelectedCategory like %1009004%
or SelectedCategory like %1009013%
or SelectedCategory like %1009003%
or SelectedCategory like %1009011%
or SelectedCategory like %1009012%
or SelectedIndustry like %1111000%
or SelectedIndustry like %1110000%
or SelectedIndustry like %1300%
or SelectedIndustry like %3200%
or SelectedIndustry like %1119000%
or LangType2 like %7%
or LangType2 like %7%
or Langlevel1 like %2%
or Langlevel2 like %2%
or Langlevel3 like %2%
我从新发一下,怕大家看得头晕,
帮我出出主意,要怎么改才是最好的.
先谢谢大家了.
and NowArea like %220%
and AccountState like %9050%
and SelectedArea like %16010,16020,125,16080,16070%
and RefreshTime like %180%
and Category like %dfgdfgdfg% --1
and CurrPositionLevel like %50%
and Degree like %3%
and MajorCategory like %305%
and MajorCategoryName like %fdgdfgfd%
and WorkKind like %1%
and ExpectSalaryType like %1000%
and Experience like %3%
and LangType1 like %7%
and HasAbroad =1
and age>30
and age <=35
and Category like %dfgdg% --2 1,2取一个就行了,or WorkFunction like %dfgdg%
or WorkFunction like %dfgdg% --取一个就行了
or SelfValue like %dfgdg%
or SkillSpec like %dfgdg%
or Industry like %1107000%
or Industry like %3600%
or Industry like %800%
or Industry like %1500%
or Industry like %700%
or SelectedCategory like %1009004%
or SelectedCategory like %1009013%
or SelectedCategory like %1009003% -- 简化为 SelectedCategory like %10090%
or SelectedCategory like %1009011%
or SelectedCategory like %1009012%
or SelectedIndustry like %1111000%
or SelectedIndustry like %1110000%
or SelectedIndustry like %1300%
or SelectedIndustry like %3200%
or SelectedIndustry like %1119000%
or LangType2 like %7%
or LangType2 like %7% -- 取一个就行了
or Langlevel1 like %2%
or Langlevel2 like %2%
or Langlevel3 like %2% ......
select * from tablename where Category ='123456'
and NowArea ='220
and AccountState='9050'
and RefreshTime ='180'
and Category like %dfgdfgdfg%
and CurrPositionLevel ='50'
and Degree ='3'
and MajorCategory ='305'
and MajorCategoryName ='fdgdfgfd'
and WorkKind ='1'
and ExpectSalaryType ='1000'
and Experience ='3'
and HasAbroad =1
and between 30 and 35and SelectedArea= '16010'
or SelectedArea= '16020'
or SelectedArea= '125'
or SelectedArea='16080'
or SelectedArea= '16070'
or Industry ='1107000'
or Industry ='3600'
or Industry ='800'
or Industry ='1500 '
or Industry ='700 '
or SelectedCategory ='1009004'
or SelectedCategory ='1009013'
or SelectedCategory ='1009003'
or SelectedCategory ='1009011'
or SelectedCategory ='1009012'
or SelectedIndustry ='1111000'
or SelectedIndustry ='1110000'
or SelectedIndustry ='1300'
or SelectedIndustry ='3200'
or SelectedIndustry ='1119000'
and LangType1 ='7'
or LangType2 ='7'
or LangType2 ='7'
or Langlevel1 ='2'
or Langlevel2 ='2'
or Langlevel3 ='2'
and Category like %dfgdg%
or WorkFunction like %dfgdg%
or WorkFunction like %dfgdg%
or SelfValue like %dfgdg%
or SkillSpec like %dfgdg% 请大家多多帮忙看看.
多多留下你们的意见.
select * from tablename where Category ='123456'
and NowArea ='220'
and AccountState='9050'
and RefreshTime ='180'
and Category like %dfgdfgdfg%
and CurrPositionLevel ='50'
and Degree ='3'
and MajorCategory ='305'
and MajorCategoryName ='fdgdfgfd'
and WorkKind ='1'
and ExpectSalaryType ='1000'
and Experience ='3'
and HasAbroad =1
and between 30 and 35
and SelectedArea in('16010','16020','125' ,'16080','16070')
or Industry in('1107000','3600','800', '1500 ','700 ' )
or SelectedCategory in('1009004','1009013' )
and LangType1 ='7'
or LangType2 ='7'
or LangType2 ='7'
or Langlevel1 ='2'
or Langlevel2 ='2'
or Langlevel3 ='2'
and Category like %dfgdg%
or WorkFunction like %dfgdg%
or WorkFunction like %dfgdg%
or SelfValue like %dfgdg%
or SkillSpec like %dfgdg%
先将数据量大的查询出来,再进行这样的操作,我想会好很多
select * from tablename where Category ='123456'
and NowArea ='220'
and AccountState='9050'
and RefreshTime ='180'
and Category like %dfgdfgdfg%
and CurrPositionLevel ='50'
and Degree ='3'
and MajorCategory ='305'
and MajorCategoryName ='fdgdfgfd'
and WorkKind ='1'
and ExpectSalaryType ='1000'
and Experience ='3'
and HasAbroad =1
and between 30 and 35
and SelectedArea in('16010','16020','125' ,'16080','16070')
or Industry in('1107000','3600','800', '1500 ','700 ' )
or SelectedCategory in('1009004','1009013' )
and LangType1 ='7'
or LangType2 ='7'
or LangType2 ='7'
or Langlevel1 ='2'
or Langlevel2 ='2'
or Langlevel3 ='2'
and Category like %dfgdg%
or WorkFunction like %dfgdg%
or WorkFunction like %dfgdg%
or SelfValue like %dfgdg%
or SkillSpec like %dfgdg%
先将数据量大的查询出来,再进行这样的操作,我想会好很多
select * from tablename where Category ='123456'
and NowArea ='220'
and AccountState='9050'
and RefreshTime ='180'
and Category like %dfgdfgdfg%
and CurrPositionLevel ='50'
and Degree ='3'
and MajorCategory ='305'
and MajorCategoryName ='fdgdfgfd'
and WorkKind ='1'
and ExpectSalaryType ='1000'
and Experience ='3'
and HasAbroad =1
and between 30 and 35
and SelectedArea in('16010','16020','125' ,'16080','16070')
or Industry in('1107000','3600','800', '1500 ','700 ' )
or SelectedCategory in('1009004','1009013' )
and LangType1 ='7'
or LangType2 ='7'
or LangType2 ='7'
or Langlevel1 ='2'
or Langlevel2 ='2'
or Langlevel3 ='2'
and Category like %dfgdg%
or WorkFunction like %dfgdg%
or WorkFunction like %dfgdg%
or SelfValue like %dfgdg%
or SkillSpec like %dfgdg%
先将数据量大的查询出来,再进行这样的操作,我想会好很多
我现在很是需要这要的sql语句.
准备用这个作为查询
select * from Ly_Resume where exists(select * from Ly_Resume where SelectedArea in('16010','16020','125' ,'16080','16070')
or Industry in('1107000','3600','800', '1500 ','700 ' )
or SelectedCategory in('1009004','1009013' ) )and exists(select * from Ly_Resume where Category like '%dfgdg% 'or WorkFunction like '%dfgdg%'
or SelfValue like '%dfgdg% 'or SkillSpec like' %dfgdg% ') and exists(select * from Ly_Resume where Category ='123456'and NowArea ='220'and AccountState='9050'
--and RefreshTime ='2008-05-06'
and Category like '%dfgdfgdfg% 'and CurrPositionLevel ='50' and Degree ='3' and MajorCategory ='305'
and MajorCategoryName ='fdgdfgfd' and WorkKind ='1' and ExpectSalaryType ='2' and Experience ='3'
and HasAbroad =1 )
and exists(select * from Ly_Resume where LangType1 ='7' or LangType2 ='7' or LangType2 ='7' or Langlevel1 ='2'
or Langlevel2 ='2' or Langlevel3 ='2' )请大家看看还有什么问题.有什么地方可以在优化一下.