一张表有一个备用字段存的是xml类型数据,然后查询时速度极慢,10万条数据根据条件统计记录数要6秒,彻底疯了我……
select count(id) from table1 where ready1.value('(ExtField/Quality)[1]','varchar(100)') like '%一般%'
select count(id) from table1 where ready1.value('(ExtField/Quality)[1]','varchar(100)') like '%一般%'
table1
id ExtField
2347 <ExtField><Quality>一般</Quality><CityCode>3301</CityCode></ExtField>
2345 <ExtField><Quality>高端</Quality><CityCode>3303</CityCode></ExtField>
2321 <ExtField><Quality>中等</Quality><CityCode>3302</CityCode></ExtField>
--试试exists
create table tb(name xml)
insert into tb
select '<ExtField><Quality>一般</Quality><CityCode>3301</CityCode></ExtField>' union
select '<ExtField><Quality>一般</Quality><CityCode>3301</CityCode></ExtField>' union
select '<ExtField><Quality>高端</Quality><CityCode>3303</CityCode></ExtField>' union
select '<ExtField><Quality>中等</Quality><CityCode>3302</CityCode></ExtField>'
select count(*) from tb where name.exist('(.[ExtField/Quality="一般"])[1]')=1