sql常用语句

删除字段中的空格

先查出是否有空格

select  replace([Position1], ' ', '') as a, [Position1],* from TZFX_DWTXB_ZXXSK 

后删除是否有空

update TZFX_RYXXB 
set [Position] = REPLACE([Position],' ',''）

 

给某一字段后面加统一字

update TZFX_DWTXB_ZXXSK
set DpName = Rtrim(DpName)+ '山塘'
WHERE (TZFX_DWTXB_ZXXSK.DpName NOT LIKE '%水库%') AND
      (TZFX_DWTXB_ZXXSK.DpName NOT LIKE '%山塘%')

 

 

SELECT TZFX_Reservoir.ID,TZFX_Reservoir.code,TZFX_Reservoir.Country,TZFX_Reservoir.Town,
TZFX_Reservoir.Village,TZFX_Reservoir.Reservoir,TZFX_Reservoir.Restp,
TZFX_Reservoir.Management,TZFX_Reservoir.ManagementType,
tablezrr.aname,tablezrr.aFxzw,
tablezrr.bname,tablezrr.bFxzw,
tablezrr.cname,tablezrr.cFxzw,
tablezrr.dname,tablezrr.dFxzw
FROM TZFX_Reservoir
left join
(
select
d.DpName,d.ReservoirType,d.County,
a.Name as aname,a.FXzw as aFxzw,
b.Name as bname,b.FXzw as bFxzw,
c.Name as cname,c.FXzw as cFxzw,
d.Name as dname,d.FXzw as dFxzw
from
(select  DpName,ReservoirType,County,Name,Position,FXzw from TZFX_ZXXSK where Fxzw LIKE '%管理单位责任人%')  d 
left join
(select  DpName,County,Name,Position,FXzw from TZFX_ZXXSK where Fxzw LIKE '%政府总责任人%')  a 
on  d.DpName = a.DpName
left join
(select  DpName,County,Name,Position,FXzw from TZFX_ZXXSK where Fxzw LIKE '%政府分管责任人%')  b 
on  d.DpName = b.DpName
left join
(select  DpName,County,Name,Position,FXzw from TZFX_ZXXSK where Fxzw LIKE '%主管部门责任人%')  c 
on  d.DpName = c.DpName
) as tablezrr

on TZFX_Reservoir.Reservoir = tablezrr.DpName
order by ID