常用Sql 整理

查询SQL如下：
SELECT NAME from SYSOBJECTS WHERE XTYPE=‘U’ ORDER BY NAME

可通过字符串拼接到一个字段里用逗号分隔：
SELECT stuff((select ‘,’+NAME from SYSOBJECTS WHERE XTYPE=‘U’ ORDER BY NAME for xml path(’’)),1,1,’’)

读取指定表的所有列名：
select name from syscolumns where id=(select max(id) from sysobjects where xtype=‘u’ and name=‘表名’)

–查询字段指定字符前面的字符串
select substring(name,CHARINDEX(’|’,name)+1,LEN(name)) from table where name like ‘%|%’

–查询字段指定字符后面的字符串
select substring(name,1,CHARINDEX(’#’,name)-1) from table where name like ‘%#%’

–sql语句将多个空格字符替换成一个空格字符
SELECT REPLACE(REPLACE(
　　REPLACE(
　　REPLACE(
　　LTRIM(RTRIM(name)),
　　’ ‘,’ ‘+’#’), --把两个空格替换成一个空格加上特殊符号#(’ #’)
　　‘#’+’ ‘,’’), --把特殊符号和一个空格(’# ‘)替换成无’’
　　‘#’,’’),’ ‘,’|’) AS NewString --如果空格总数是偶数，则最后会多一个特殊符号#,此时把#替换成无
FROM table

–去除字段：HourseTitle重复的数据 按HourseID排序 只留第一条
DELETE Hourse WHERE HourseID NOT IN(
SELECT HourseID FROM(
SELECT P.*,ROW_NUMBER() OVER (PARTITION BY P.HourseTitle,HourseAddress ORDER BY P.HourseID DESC) AS RowNum
FROM Hourse P
) A
WHERE RowNum = 1
)