精妙Sql语句收藏二 |
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() |
精妙Sql语句收藏二
最新推荐文章于 2024-10-16 17:36:30 发布