[转贴]精妙Sql 15句

1. 判断a表中有而b表中没有的记录
select a.* from tbl1 a
left join tbl2 b
on a.key = b.key
where b.key is null
         虽然使用in也可以实现,但是这种方法的效率更高一些
2. 新建一个与某个表相同结构的表
select * into b
from a where 1<>1
3.between的用法,between限制查询数据范围时包括了边界值,not between不包括
select * from table1 where time between time1 and time2
select a,b,c, from table1 where a not between 数值1 and 数值2
4. 说明:包括所有在 TableA 中但不在 TableB和TableC 中的行并消除所有重复行而派生出一个结果表
(select a from tableA ) except (select a from tableB ) except (select a from tableC )
5. 初始化表,可以将自增长表的字增长字段置为1
TRUNCATE TABLE table1
6.多语言设置数据库或者表或者order by的排序规则
         --修改用户数据库的排序规则
ater database dbname collate SQL_Latin1_General_CP1_CI_AS
--修改字段的排序规则
alter table a alter column c2 varchar(50) collate SQL_Latin1_General_CP1_CI_AS
--按姓氏笔画排序
select * from 表名 order by 列名 Collate Chinese_PRC_Stroke_ci_as
--按拼音首字母排序
select * from 表名 order by 列名 Collate Chinese_PRC_CS_AS_KS_WS
7.列出所有的用户数据表:
SELECT TOP 100 PERCENT o.name AS 表名
FROM dbo.syscolumns c INNER JOIN
      dbo.sysobjects o ON o.id = c.id AND objectproperty(o.id, N'IsUserTable') = 1 AND
      o.name <> 'dtproperties' LEFT OUTER JOIN
      dbo.sysproperties m ON m.id = o.id AND m.smallid = c.colorder
WHERE (c.colid = 1)
ORDER BY o.name, c.colid
 
8.列出所有的用户数据表及其字段信息:
SELECT TOP 100 PERCENT c.colid AS 序号, o.name AS 表名, c.name AS 列名,
      t.name AS 类型, c.length AS 长度, c.isnullable AS 允许空,
      CAST(m.[value] AS Varchar(100)) AS 说明
FROM dbo.syscolumns c INNER JOIN
      dbo.sysobjects o ON o.id = c.id AND objectproperty(o.id, N'IsUserTable') = 1 AND
      o.name <> 'dtproperties' INNER JOIN
      dbo.systypes t ON t.xusertype = c.xusertype LEFT OUTER JOIN
      dbo.sysproperties m ON m.id = o.id AND m.smallid = c.colorder
ORDER BY o.name, c.colid
9.Left,right Join的另外一种简洁的写法
select * from a,b where a.id *= b.id --(*= 相当于 LEFT JOIN)  
select * from a,b where a.id =* b.id --(=* 相当于 Right JOIN)  
10.Update from 和 delete from
11.得到表中最小的未使用的ID号
         SELECT (CASE WHEN EXISTS(SELECT * FROM Handle b WHERE b.HandleID = 1)
THEN MIN(HandleID) + 1 ELSE 1 END) as HandleID
FROM Handle
WHERE NOT HandleID IN (SELECT a.HandleID - 1 FROM Handle a)
12.随机取得记录
         SELECT TOP 10 * FROM T1 ORDER BY NEWID()

 

13. 两张结构相同的表,使他们的数据同步      
      create table #A(id int, y vachar(10))
      create table #B(id int, y varchar(10))
      
      insert into #A 
         select 1, 'a1'
         union
         select 2, 'a2'
      select * from #A
      
      insert into #B
         select 1, 'b1'
         union
         select 2, 'b2'
      select * from #B

      update #B set y=#A.y from #B,#A where #A.id=#B.id
      
      select * from #B
      
      drop table #A
      drop table #B

14.对于一张表的一列,查找出不重复的记录
      create table #A(x int,y varchar(10))      
      insert into #A 
         select 1, '张三'
         union  
         select 2, '张三'
         union
         select 3, '李四'
      select distinct y,count(y) [count] from #A group by y
      drop table #A

15.将表A查询成下面的样子
表A

id    x
1    abc
2    xyz
3    abc
4    abc

B
abc  xyz
3     1  

   select * into #temp from( select
      1 id,    'abc' x
      union
      select 2,    'xyz'
       union
      select 3,    'abc'
      union
      select 4,    'abc'
      ) A

     select * from #temp
    declare @sql varchar(8000)
      set @sql = 'select '

    select @sql = @sql + ltrim(str(f2)) + ' ' + f1 + ', ' from 
   (
       select x f1, count(*) f2 from #temp group by x
    ) A


     set @sql =  substring(@sql, 1, len(@sql) - 1)

   select @sql

   exec( @sql )

   drop table #temp

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值