sql 查询重复数据

select   college_code   from   nyytest 

where groupid=7       

group   by   college_code     having   count(*)>1 

 

select * from courseinfo where coursename in (select   coursename   from   courseinfo 

where schoolid=29       

group   by   coursename     having   count(*)>1 )

 

 

delete from [采集地址].[dbo].[CNKI博士学位]

where ID not in (select MAX(id) from [采集地址].[dbo].[CNKI博士学位]

 group by filename)

 

SQL_修改表字段类型

/*判断该表是否已经存在*/
if exists ( select * from sys.objects where name ='表名')
print '表存在'
else
print '表不存在'

if exists(select * from syscolumns where name='字段名' and id=object_id('表名'))
print '字段名存在'
else
print '字段名不存在'

--修改表字段类型
ALTER TABLE 表名 ALTER COLUMN 字段名 数据类型
ALTER TABLE User ALTER COLUMN name varchar(150)

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[存储过程名]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[存储过程名] --删除存储过程

select * into counseBF from course where schoolid=29