两条植物分布记录:1. “四川。云南。贵州。印度。缅甸。不丹。尼泊尔。老挝。越南。泰国。栽培于云南。”2. “欧洲。西亚。尼泊尔。栽培于广东。”3. “北京。栽培于辽宁。”栽培之前的分布是野生分布信息,我要得到的记录是野生分布地在中国(分布在中国的任何省和地区),即第1和3条记录。我写的语句如下,能实现功能,但很繁冗,请问有没有更简单的语句?谢谢。
select * from 植物分布 where substring (distribution, 1, charindex ('栽培', distribution)) like '%北京%'
or
substring (distribution, 1, charindex ('栽培', distribution)) like '%新疆%' or
substring (distribution, 1, charindex ('栽培', distribution)) like '%辽宁%' or
substring (distribution, 1, charindex ('栽培', distribution)) like '%山西%' or
substring (distribution, 1, charindex ('栽培', distribution)) like '%山东%' or
substring (distribution, 1, charindex ('栽培', distribution)) like '%河南%' or
substring (distribution, 1, charindex ('栽培', distribution)) like '%河北%' or
substring (distribution, 1, charindex ('栽培', distribution)) like '%湖南%' or
substring (distribution, 1, charindex ('栽培', distribution)) like '%湖北%' or
substring (distribution, 1, charindex ('栽培', distribution)) like '%广东%' or
substring (distribution, 1, charindex ('栽培', distribution)) like '%广西%' or
substring (distribution, 1, charindex ('栽培', distribution)) like '%黑龙江%' or
substring (distribution, 1, charindex ('栽培', distribution)) like '%浙江%' or
substring (distribution, 1, charindex ('栽培', distribution)) like '%安徽%' or
substring (distribution, 1, charindex ('栽培', distribution)) like '%江苏%' or
substring (distribution, 1, charindex ('栽培', distribution)) like '%福建%' or
substring (distribution, 1, charindex ('栽培', distribution)) like '%甘肃%' or
substring (distribution, 1, charindex ('栽培', distribution)) like '%江西%' or
substring (distribution, 1, charindex ('栽培', distribution)) like '%云南%' or
substring (distribution, 1, charindex ('栽培', distribution)) like '%贵州%' or
substring (distribution, 1, charindex ('栽培', distribution)) like '%四川%' or
substring (distribution, 1, charindex ('栽培', distribution)) like '%青海%' or
substring (distribution, 1, charindex ('栽培', distribution)) like '%陕西%' or
substring (distribution, 1, charindex ('栽培', distribution)) like '%吉林%' or
substring (distribution, 1, charindex ('栽培', distribution)) like '%宁夏%' or
substring (distribution, 1, charindex ('栽培', distribution)) like '%海南%' or
substring (distribution, 1, charindex ('栽培', distribution)) like '%台湾%' or
substring (distribution, 1, charindex ('栽培', distribution)) like '%西藏%' or
substring (distribution, 1, charindex ('栽培', distribution)) like '%内蒙古%'
select * from 植物分布 where substring (distribution, 1, charindex ('栽培', distribution)) like '%北京%'
or
substring (distribution, 1, charindex ('栽培', distribution)) like '%新疆%' or
substring (distribution, 1, charindex ('栽培', distribution)) like '%辽宁%' or
substring (distribution, 1, charindex ('栽培', distribution)) like '%山西%' or
substring (distribution, 1, charindex ('栽培', distribution)) like '%山东%' or
substring (distribution, 1, charindex ('栽培', distribution)) like '%河南%' or
substring (distribution, 1, charindex ('栽培', distribution)) like '%河北%' or
substring (distribution, 1, charindex ('栽培', distribution)) like '%湖南%' or
substring (distribution, 1, charindex ('栽培', distribution)) like '%湖北%' or
substring (distribution, 1, charindex ('栽培', distribution)) like '%广东%' or
substring (distribution, 1, charindex ('栽培', distribution)) like '%广西%' or
substring (distribution, 1, charindex ('栽培', distribution)) like '%黑龙江%' or
substring (distribution, 1, charindex ('栽培', distribution)) like '%浙江%' or
substring (distribution, 1, charindex ('栽培', distribution)) like '%安徽%' or
substring (distribution, 1, charindex ('栽培', distribution)) like '%江苏%' or
substring (distribution, 1, charindex ('栽培', distribution)) like '%福建%' or
substring (distribution, 1, charindex ('栽培', distribution)) like '%甘肃%' or
substring (distribution, 1, charindex ('栽培', distribution)) like '%江西%' or
substring (distribution, 1, charindex ('栽培', distribution)) like '%云南%' or
substring (distribution, 1, charindex ('栽培', distribution)) like '%贵州%' or
substring (distribution, 1, charindex ('栽培', distribution)) like '%四川%' or
substring (distribution, 1, charindex ('栽培', distribution)) like '%青海%' or
substring (distribution, 1, charindex ('栽培', distribution)) like '%陕西%' or
substring (distribution, 1, charindex ('栽培', distribution)) like '%吉林%' or
substring (distribution, 1, charindex ('栽培', distribution)) like '%宁夏%' or
substring (distribution, 1, charindex ('栽培', distribution)) like '%海南%' or
substring (distribution, 1, charindex ('栽培', distribution)) like '%台湾%' or
substring (distribution, 1, charindex ('栽培', distribution)) like '%西藏%' or
substring (distribution, 1, charindex ('栽培', distribution)) like '%内蒙古%'
解决方案 »
- 这个算不算SQLServer2008express的一个小bug
- SQL语句,字符相加,数字相加如何
- 菜鸟小声问一下?
- 怎样查看在sqlce中建的表格内容?先谢谢啦
- 請問我的SQL數據庫原來是1000MB經過我壓后變成50MB,請問這會不會對數據庫的速度有影響?
- 急救:如何把ACCESS数据库转换成SQLSERVER数据库
- 大数据量的表上的一列有大量相同数据,怎么提高以该列为条件时查询速度
- 在win98下如何启动mssqlserver服务?我需要命令行的方式。等待中!!
- 能否通过角本实现两台sql server服务器的数据复制?
- 请问如何将记录集(结果集)导入到Excel
- 急!大家来看看
- 自建表提示信息显示有错,哪些设置有问题?如何修改?
如表citys
id cityname
1 北京
2 新疆
3 辽宁
语句就这样:select a.* from 植物分布 a,citys b where
charindex(','+b.cityname+',',','+substring (distribution, 1, charindex ('栽培', distribution)+',')>0--or:
select * from 植物分布 a,citys b where
substring (distribution, 1, charindex ('栽培', distribution)) like '%'+b.cityname+'%'
substring (a.distribution, 1, charindex ('栽培', a.distribution)) like '%'+b.NAME+'%'功能是前面的语句是一样的。
select distinct a.* from 植物分布 a, citys b where
substring (a.distribution, 1, charindex ('栽培', a.distribution)) like '%'+b.cityname+'%'