收集SqlServer中的各种Sql Script-CSDN博客

1，引用其他表的字段，建自己的表字段

Create table FieldByReferences
(
    ID int references dbo.STORY(ID) primary key,
    CARDID int references dbo.Card(ID) not NULL,
)

创建的表之后查看建表语句，得如下：

CREATE TABLE [dbo].[FieldByReferences](
    [ID] [int] NOT NULL,
    [CARDID] [int] NOT NULL,
PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[FieldByReferences]  WITH CHECK ADD FOREIGN KEY([CARDID])
REFERENCES [dbo].[CARD] ([ID])
GO
ALTER TABLE [dbo].[FieldByReferences]  WITH CHECK ADD FOREIGN KEY([ID])
REFERENCES [dbo].[STORY] ([ID])
GO

2，更新a表的字段为b表的字段的值（跟Oralce不一样）

UPDATE t 
    SET [Name] = x.[Name]
FROM FieldByReferences t
INNER JOIN CARD x ON t.CARDID = x.ID

等同于

UPDATE FieldByReferences 
    SET [Name] = x.[Name]
FROM FieldByReferences 
INNER JOIN CARD x ON FieldByReferences.CARDID = x.ID

3，用with tablename(fields) 创建Cte(Common Table Expression类似临时表)//来自ms-help://MS.VSCC.v80/MS.VSIPCC.v80/MS.SQLSVR.v9.en/tsqlref9/html/27cfb819-3e8d-4274-8bbe-cbbe4d9c2e23.htm

a, 简单的

WITH abc(ab,[name]) AS
(
    SELECT [Name],id FROM FieldByReferences
)
SELECT * FROM abc

--创建的cte表必须使用，不能不用；字段个数要一致；字段是根据顺序对应的，不是根据字段名字对应的。

b, 限制条数并求平均值

WITH DirReps(Manager, DirectReports) AS 
(
    SELECT ManagerID, COUNT(*) AS DirectReports
    FROM Employee
    GROUP BY ManagerID
) --分组获取每个ManagerID下的Employees数
SELECT AVG(DirectReports) AS [Average Number of Direct Reports]
FROM DirReps 
WHERE DirectReports>= 2 --求平均值的范围是Employees数大于等于2的Manager的
GO
--

c，Cte的递归使用

WITH DirectReports(ManagerID, EmployeeID, EmployeeLevel) AS 
(
    SELECT ManagerID, EmployeeID, 0 AS EmployeeLevel
    FROM HumanResources.Employee
    WHERE ManagerID IS NULL
    UNION ALL
    SELECT e.ManagerID, e.EmployeeID, EmployeeLevel + 1
    FROM HumanResources.Employee e
        INNER JOIN DirectReports d
        ON e.ManagerID = d.EmployeeID 
)
SELECT ManagerID, EmployeeID, EmployeeLevel 
FROM DirectReports
GO

d，Cte的递归使用，用EmployeeLevel限制递归的2层

WITH DirectReports(ManagerID, EmployeeID, EmployeeLevel) AS 
(
    SELECT ManagerID, EmployeeID, 0 AS EmployeeLevel
    FROM HumanResources.Employee
    WHERE ManagerID IS NULL
    UNION ALL
    SELECT e.ManagerID, e.EmployeeID, EmployeeLevel + 1
    FROM HumanResources.Employee e
        INNER JOIN DirectReports d
        ON e.ManagerID = d.EmployeeID 
)
SELECT ManagerID, EmployeeID, EmployeeLevel 
FROM DirectReports 
WHERE EmployeeLevel <= 2 ;
GO

e，使用MAXRECURSION预防无穷递归

--创建一个无限循环
WITH cte (EmployeeID, ManagerID, Title) as
(
    SELECT EmployeeID, ManagerID, Title
    FROM HumanResources.Employee
    WHERE ManagerID IS NOT NULL
  UNION ALL
    SELECT cte.EmployeeID, cte.ManagerID, cte.Title
    FROM cte 
    JOIN  HumanResources.Employee AS e 
        ON cte.ManagerID = e.EmployeeID
)
--使用MAXRECURSION来限制循环层数为2
SELECT EmployeeID, ManagerID, Title
FROM cte
OPTION (MAXRECURSION 2);
GO

当纠正了错误的无限循环后，MAXRECURSION 就可以不再需要了，如下

WITH cte (EmployeeID, ManagerID, Title)
AS
(
    SELECT EmployeeID, ManagerID, Title
    FROM HumanResources.Employee
    WHERE ManagerID IS NOT NULL
  UNION ALL
    SELECT  e.EmployeeID, e.ManagerID, e.Title
    FROM HumanResources.Employee AS e
    JOIN cte ON e.ManagerID = cte.EmployeeID
)
SELECT EmployeeID, ManagerID, Title
FROM cte;
GO

f，使用Cte递归检索树状表的数据

--下例显示ProductAssemblyID=800的下Commponents，以及这些Components下的Components...，并显示层级

WITH Parts(AssemblyID, ComponentID, PerAssemblyQty, EndDate, ComponentLevel) AS
(
    SELECT b.ProductAssemblyID, b.ComponentID, b.PerAssemblyQty,
        b.EndDate, 0 AS ComponentLevel
    FROM Production.BillOfMaterials AS b
    WHERE b.ProductAssemblyID = 800
          AND b.EndDate IS NULL
    UNION ALL
    SELECT bom.ProductAssemblyID, bom.ComponentID, p.PerAssemblyQty,
        bom.EndDate, ComponentLevel + 1
    FROM Production.BillOfMaterials AS bom 
        INNER JOIN Parts AS p
        ON bom.ProductAssemblyID = p.ComponentID
        AND bom.EndDate IS NULL
)
SELECT AssemblyID, ComponentID, Name, PerAssemblyQty, EndDate,
        ComponentLevel 
FROM Parts AS p
    INNER JOIN Production.Product AS pr
    ON p.ComponentID = pr.ProductID
ORDER BY ComponentLevel, AssemblyID, ComponentID;
GO

g，在Update语句里使用Cte

--下例修改所有直接或间接归属于ManagerID=12的所有Employees的VacationHours为1.25倍?。
--Cte返回归属ManagerID=12的Employees，以及这些Employees下的Employees,...,的一系列表。
--只有Cte返回的行才会被修改。

WITH DirectReports(EmployeeID, NewVacationHours, EmployeeLevel)
AS
(SELECT e.EmployeeID, e.VacationHours, 1
  FROM HumanResources.Employee AS e
  WHERE e.ManagerID = 12
  UNION ALL
  SELECT e.EmployeeID, e.VacationHours, EmployeeLevel + 1
  FROM HumanResources.Employee as e
  JOIN DirectReports AS d ON e.ManagerID = d.EmployeeID
)
UPDATE HumanResources.Employee
SET VacationHours = VacationHours * 1.25
FROM HumanResources.Employee AS e
JOIN DirectReports AS d ON e.EmployeeID = d.EmployeeID;
GO

h，找直系亲属列表

--下例返回指定人员的所有直系亲属

--Genealogy(宗族，家谱) table
IF OBJECT_ID('Person','U') IS NOT NULL DROP TABLE Person;
GO
CREATE TABLE Person(ID int, Name varchar(30), Mother int, Father int);
GO
INSERT Person VALUES(1, 'Sue', NULL, NULL);
INSERT Person VALUES(2, 'Ed', NULL, NULL);
INSERT Person VALUES(3, 'Emma', 1, 2);
INSERT Person VALUES(4, 'Jack', 1, 2);
INSERT Person VALUES(5, 'Jane', NULL, NULL);
INSERT Person VALUES(6, 'Bonnie', 5, 4);
INSERT Person VALUES(7, 'Bill', 5, 4);
GO
-- 用Cte返回Bonnie的所有直系长辈
WITH Generation (ID) AS
(
-- 首先，Bonnie的母亲
    SELECT Mother 
    FROM Person
    WHERE Name = 'Bonnie'
UNION
-- 其次，Bonnie的父亲
    SELECT Father 
    FROM Person
    WHERE Name = 'Bonnie'
UNION ALL
-- 递归返回上一级的父亲
    SELECT Person.Father
    FROM Generation, Person
    WHERE Generation.ID=Person.ID
UNION ALL
-- 递归返回上一级的母亲
    SELECT Person.Mother
    FROM Generation, Person
    WHERE Generation.ID=Person.ID
)
SELECT Person.ID, Person.Name, Person.Mother, Person.Father
FROM Generation, Person
WHERE Generation.ID = Person.ID;
GO