SQL的一些東東

 ★获取字段类型
SELECT syscolumns.name AS 列名, systypes.name AS 数据类型
FROM syscolumns,sysobjects,systypes
WHERE syscolumns.id = sysobjects.id and syscolumns.xtype = systypes.xtype{ and systypes.name <> 'sysname'{系统提供的用户定义数据类型,功能上相当于nvarchar(128),用于引用数据库对象名称}} and sysobjects.name = N'表名'
ORDER BY syscolumns.colid

 

★测试SQL语句性能
SET STATISTICS io ON
SET STATISTICS time ON
go
select  * from TB  ---你要测试的sql语句
go
SET STATISTICS profile OFF
SET STATISTICS io OFF
SET STATISTICS time OFF

 

★SQL语句查询结果额外添加一列序号自动增加
id为惟一列：
SELECT (SELECT COUNT(*) FROM s_phonemain m1 WHERE m1.id <= m2.id) AS seq, CompDepa, floor, ItemTeam
FROM S_PhoneMain m2 ORDER BY seq
id+compdepa组成惟一列：
SELECT (SELECT COUNT(*) FROM s_phonemain m1 WHERE (convert(nvarchar(10),m1.id)+m1.compdepa) <= (convert(nvarchar(10),m2.id)+m2.compdepa)) AS seq, CompDepa, floor, ItemTeam
FROM S_PhoneMain m2 ORDER BY seq

 

★SQL存储过程自动回滚

create proc  
  ...  
  as  
  begin  
      set xact_abort on  --设置出错时自动回滚事务 
      begin transaction    
       
      insert tabel1 vaule(...)  
      if @@error<>0 then  
      begin  
          rollback transaction  
          return  
      end  
      insert tabel2 vaule(...)  
      if  @@error<>0 then  
      begin  
          rollback transaction  
          return  
      end  
       
      commit transaction  
      return

 

★删除重复数据

如果该表需要删除重复的记录（重复记录保留1条）：

select distinct * into #Tmp from tableName
drop table tableName
select * into tableName from #Tmp
drop table #Tmp

 

另一种方法是一个字段重复,ID不重复：

delete 表 where id not in(SELECT MAX(id) AS id FROM 表 GROUP BY rows) --- 删除重复行

select * from 表 where id in(SELECT MAX(id) AS id FROM 表 GROUP BY rows) --重复行只查询一条

SELECT COUNT(DISTINCT 字段) AS Expr1, COUNT(*) AS Expr2 FROM 表 --统计

 

关于学生成绩：http://www.cnblogs.com/tenghoo/archive/2007/06/11/779240.html