SQL Server
文章平均质量分 60
bradleydan
这个作者很懒,什么都没留下…
展开
-
SQL处理语句步骤
翻译 2012-09-05 09:09:17 · 321 阅读 · 0 评论 -
数字辅助表
set nocount on;if object_id('dbo.nums') is not nulldrop table dbo.numsgocreate table dbo.nums( n int not null primary key);declare @max as int,@rc as int;set @max=100000set @rc=1;insert原创 2012-09-05 18:10:41 · 581 阅读 · 0 评论 -
分页
方式一:SET NOCOUNT ON;USE tempdb;GOIF OBJECT_ID('dbo.Sales') IS NOT NULL DROP TABLE dbo.Sales;GOCREATE TABLE dbo.Sales( empid VARCHAR(10) NOT NULL PRIMARY KEY, mgrid VARCHAR(10) NOT NULL,原创 2012-09-05 15:10:23 · 322 阅读 · 0 评论 -
In ,Exists,Some,Any
In 和 Exists区别:val in (val1,val2,...Null) 永远不会返回false,而是返回TRUE和Unknown当存在NULL时, not in 和 not exists不等价! v vy or v v vz 具有最小OrderID的值SELECT OrderID, CustomerID, EmployeeID, OrderDateFRO原创 2012-09-06 11:29:45 · 377 阅读 · 0 评论 -
Row_Number(),Rank和 Dense_Rank以及NTLINE
表创建SET NOCOUNT ON;USE tempdb;GOIF OBJECT_ID('dbo.Sales') IS NOT NULL DROP TABLE dbo.Sales;GOCREATE TABLE dbo.Sales( empid VARCHAR(10) NOT NULL PRIMARY KEY, mgrid VARCHAR(10) NOT NULL,原创 2012-09-05 15:54:52 · 416 阅读 · 0 评论 -
最小缺失值(Exists,COALESCE)
1. 创建表IF OBJECT_ID('dbo.T1') IS NOT NULL DROP TABLE dbo.T1;GOCREATE TABLE dbo.T1( keycol INT NOT NULL PRIMARY KEY CHECK(keycol > 0), datacol VARCHAR(10) NOT NULL);INSERT INTO dbo.原创 2012-09-06 13:04:52 · 414 阅读 · 0 评论 -
游标
使用游标计算行号DECLARE @SalesRN TABLE(empid VARCHAR(5), qty INT, rn INT);DECLARE @empid AS VARCHAR(5), @qty AS INT, @rn AS INT;DECLARE rncursor CURSOR FAST_FORWARD FOR SELECT empid, qty FROM dbo.Sales原创 2012-09-06 16:35:40 · 326 阅读 · 0 评论 -
SQL基本查询语句(Northwind数据库)
1. 每月最后实际订单日期发生的订单SELECT OrderID, CustomerID, EmployeeID, OrderDateFROM dbo.OrdersWHERE OrderDate IN (SELECT MAX(OrderDate) FROM dbo.Orders GROUP BY CONVERT(CHAR(6), OrderDate, 112));GO原创 2012-09-06 10:02:33 · 3461 阅读 · 0 评论