1、复制表(只复制结构,源表名:a 新表名:b) (Access可用)
法一:select * into b from a where 1<>1
法二:select top 0 * into b from a
2、显示文章、提交人和最后回复时间
select a.title,a.username,b.adddate from table a,(select max(adddate) adddate from table where table.title=a.title) b
3、两张关联表,删除主表中已经在副表中没有的信息
delete from table1 where not exists ( select * from table2 where table1.field1=table2.field1 )
4、随机取出10条数据
select top 10 * from tablename order by newid()
5、随机选择记录
select newid()
6、删除重复记录
Delete from tablename where id not in (select max(id) from tablename group by col1,col2,...)
7、列出数据库里所有的表名
select name from sysobjects where type='U'
8、列出表里的所有的
select name from syscolumns where id=object_id('TableName')
9、行累加
Declare @autoId varchar(1000)
Set @autoId=''
Select @autoId=@autoId+','+Convert(varchar(50),autoId) From FlowSource_tab Where chartId='kkk'
--print(Stuff(@autoId,1,1,''))
print @autoId
10、分页取31-404条
解1: select top 10 * from A where id not in (select top 30 id from A)
解2: select top 10 * from A where id > (select max(id) from (select top 30 id from A )as A)
10、条件判断
select * from A where (a.field=@fieldValue or @fieldValue is null)