SQL 大全
1. sql 替换字段中的部分字符,替换指定字符
update stn set PZRQ = replace(PZRQ,'-','/');
2. 截取并更新
update stn set PZRQ=mid(PZRQ,1, instr(PZRQ,',')-1 ) where PZRQ like '*,*'
3. 排除重复数据
update stn set ZCFData=-1 where OBJECTID in (select min(OBJECTID) from stn group by
DK_MC,PWH,Shape_Area,Shape_Length having count(*)>=1)
4. 按条件进行查询数据
update stn,tai set tai.WCFData=1 , tai.BSM= stn.INDEX_ID where stn.DK_MC=tai. 项目名称
and abs(stn.Shape_Area/10000.0000-tai.总面积)<0.0002 and stn.PWH=tai.批准文号 and
( stn.DK_MC is not null or stn.PWH is not null) and (tai.批次 is not null and tai.项目名称 is
not null) and tai.ZCFData is not null
5. 查找结果并复制到新表中
Select * into 新建表 from 表 where 条件
6. 查自重复数据
Select DJQDM from ST_DJZQ group by DJQDM having count(*)>1
7. 字段长度加在左边或者右边添加字段
8.UPDATE表名 SET字段名=字段名+'00'WHERElen(字段名)=1
9. 查找不重复的数据
select DISTINCT amount from checks;
10. 求个数
SELECT COUNT(*) FROM TEAMSTATS WHERE HITS/AB 〉35
11. 求总数
SELECT SUM(SINGLES) TOTAL_SINGLES FROM TEAMSTATS
12. 求平均值
SELECT AVG(SO) AVE_STRIKE_OUTS FROM TEAMSTATS
13. 求最大值
14. 求最小值
SELECT MIN(AB) FROM TEAMSTATS
15.
评论0
最新资源