精典sql语句

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)