sql 语法总结

select case  
when ThamesWorld = '' then 9999999  --如果为空 替换成对应的数组

when CHARINDEX('–',ThamesWorld) > 0 then  LEFT(ThamesWorld,charindex('–',ThamesWorld)-1)

-- 如果包含 - 则截取-之前的字符串

when CHARINDEX('+',ThamesWorld) > 0 then  LEFT(ThamesWorld,charindex('+',ThamesWorld)-1)
-- 如果包含 + 则截取+之前的字符串
	else ThamesWorld 
 end as ThamesWorlds, ThamesWorld, *
from SW_School_Ranking where Model='SchoolRanking' Order By ThamesWorlds asc

-- 带分页的判断

select case  when AlumniAssociation = '' then 9999999 

when CHARINDEX('–',AlumniAssociation) > 0 then  LEFT(AlumniAssociation,charindex('–',AlumniAssociation)-1)

when CHARINDEX('+',AlumniAssociation) > 0 then  LEFT(AlumniAssociation,charindex('+',AlumniAssociation)-1)

	else AlumniAssociation 
 end as AlumniAssociations, *

from  
(SELECT ROW_NUMBER () OVER (ORDER BY ID) RowNum,* from SW_School_Ranking) TA


where Model='SchoolRanking' 

and RowNum BETWEEN ( 1 - 1 ) * 100 + 1 AND 1 * 100

Order By AlumniAssociations