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 * from (
select case  when ThamesWorld = '' then 9999
when CHARINDEX('–',ThamesWorld) > 0 then  LEFT(ThamesWorld,charindex('–',ThamesWorld)-1)
when CHARINDEX('+',ThamesWorld) > 0 then  LEFT(ThamesWorld,charindex('+',ThamesWorld)-1)
else ThamesWorld
end as Collations,
ROW_NUMBER () OVER (ORDER BY ID asc) RowNum, *
 from SW_School_Ranking where Model='SchoolRanking'

　　) as a   
 ORDER BY  Collations