亚洲精品国产美女久久_ SQL Server 2005盛宴系列之三：Adventure Works示例數據庫介紹腳本

SQL Server 2005盛宴系列之三：Adventure Works示例數據庫介紹腳本一

USE AdventureWorks
SELECT ContactID,
CASE NameStyle
WHEN 0 THEN N‘先名后姓‘
WHEN 1 THEN N‘先姓后名‘
END AS NameStyle,
Title, FirstName, MiddleName, LastName
FROM Person.Contact

--顯示個(gè)人消費者(CustomerType=‘I‘)客戶(hù)的姓名
USE AdventureWorks
GO
SELECT FirstName, LastName FROM Person.Contact AS C
JOIN Sales.Individual AS I ON C.ContactID = I.ContactID
JOIN Sales.Customer AS Cu ON I.CustomerID = Cu.CustomerID
WHERE Cu.CustomerType = ‘I‘
ORDER BY LastName, FirstName
GO

--顯示個(gè)人客戶(hù)的地址信息
USE AdventureWorks
GO
SELECT I.CustomerID, C.FirstName, C.LastName, A.AddressLine1, A.City,
    SP.Name AS State, CR.Name AS CountryRegion
FROM Person.Contact AS C JOIN Sales.Individual AS I ON C.ContactID = I.ContactID
    JOIN Sales.CustomerAddress AS CA ON CA.CustomerID = I.CustomerID
    JOIN Person.Address AS A ON A.AddressID = CA.AddressID
    JOIN Person.StateProvince SP ON
        SP.StateProvinceID = A.StateProvinceID
    JOIN Person.CountryRegion CR ON
        CR.CountryRegionCode = SP.CountryRegionCode
ORDER BY I.CustomerID
GO

--查看個(gè)人客戶(hù)的地址數據
USE AdventureWorks
GO
SELECT I.CustomerID, C.FirstName, C.LastName, A.AddressLine1, A.City,
    SP.Name AS State, CR.Name AS CountryRegion
FROM Person.Contact AS C
    JOIN Sales.Individual AS I ON C.ContactID = I.ContactID
    JOIN Sales.CustomerAddress AS CA ON CA.CustomerID = I.CustomerID
    JOIN Person.Address AS A ON A.AddressID = CA.AddressID
    JOIN Person.StateProvince SP ON
        SP.StateProvinceID = A.StateProvinceID
    JOIN Person.CountryRegion CR ON
        CR.CountryRegionCode = SP.CountryRegionCode
ORDER BY I.CustomerID
GO

--查看商店客戶(hù)（零售店或批發(fā)店）
USE AdventureWorks;
GO
SELECT [Name]FROM Sales.Store AS S
JOIN Sales.Customer AS C ON S.CustomerID = C.CustomerID
WHERE C.CustomerType = N‘S‘
ORDER BY Name
GO

--按商店查看商店聯(lián)系人
USE AdventureWorks;
GO
SELECT S.Name AS Store, C.FirstName, C.LastName, CT.Name AS Title
FROM Person.Contact AS C
    JOIN Sales.StoreContact AS SC ON C.ContactID = SC.ContactID
    JOIN Person.ContactType AS CT ON
        CT.ContactTypeID = SC.ContactTypeID
    JOIN Sales.Store AS S ON S.CustomerID = SC.CustomerID
ORDER BY S.Name ;
GO

--按商店查看銷(xiāo)售
USE AdventureWorks;
GO
SELECT Name, SalesOrderNumber, OrderDate, TotalDue
FROM Sales.Store AS S
JOIN Sales.SalesOrderHeader AS SO ON S.CustomerID = SO.CustomerID
ORDER BY Name, OrderDate
GO

--按位置查看商店
USE AdventureWorks
GO
SELECT S.CustomerID, S.Name AS Store, A.City, SP.Name AS State, CR.Name
    AS CountryRegion
FROM Sales.Store AS S
    JOIN Sales.CustomerAddress AS CA ON CA.CustomerID = S.CustomerID
    JOIN Person.Address AS A ON A.AddressID = CA.AddressID
    JOIN Person.StateProvince SP ON
        SP.StateProvinceID = A.StateProvinceID
    JOIN Person.CountryRegion CR ON
        CR.CountryRegionCode = SP.CountryRegionCode
ORDER BY S.CustomerID
GO

Re: SQL Server 2005盛宴系列之三：Adventure Works示例數據庫介紹腳本二

--按類(lèi)別、子類(lèi)別和型號查看產(chǎn)品
USE AdventureWorks;
GO
SELECT PC.Name AS Category, PSC.Name AS Subcategory,
    PM.Name AS Model, P.Name AS Product
FROM Production.Product AS P
    FULL JOIN Production.ProductModel AS PM ON PM.ProductModelID = P.ProductModelID
    FULL JOIN Production.ProductSubcategory AS PSC ON PSC.ProductSubcategoryID = P.ProductSubcategoryID
    JOIN Production.ProductCategory AS PC ON PC.ProductCategoryID = PSC.ProductCategoryID
ORDER BY PC.Name, PSC.Name ;
GO

--按產(chǎn)品型號查看產(chǎn)品說(shuō)明
USE AdventureWorks;
GO
SELECT PM.ProductModelID, PM.Name AS [Product Model], Description, PL.CultureID, CL.Name AS Language
FROM Production.ProductModel AS PM
    JOIN Production.ProductModelProductDescriptionCulture AS PL
        ON PM.ProductModelID = PL.ProductModelID
    JOIN Production.Culture AS CL ON CL.CultureID = PL.CultureID
    JOIN Production.ProductDescription AS PD
        ON PD.ProductDescriptionID = PL.ProductDescriptionID
ORDER BY PM.ProductModelID ;
GO

--查看父級產(chǎn)品的單個(gè)單級別物料清單列表
USE AdventureWorks;
GO
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

Re: SQL Server 2005盛宴系列之三：Adventure Works示例數據庫介紹腳本三

--按位置查看供應商
USE AdventureWorks;
GO
SELECT V.VendorID, V.Name AS Vendor, A.AddressLine1, A.AddressLine2, A.City, SP.Name AS State, CR.Name AS Country
FROM Purchasing.Vendor AS V
    JOIN Purchasing.VendorAddress AS VA ON VA.VendorID = V.VendorID
    JOIN Person.Address AS A on A.AddressID = VA.AddressID
    JOIN Person.StateProvince AS SP on SP.StateProvinceID =         A.StateProvinceID
    JOIN Person.CountryRegion AS CR ON CR.CountryRegionCode = SP.CountryRegionCode
GROUP BY V.VendorID, V.Name, A.AddressLine1, A.AddressLine2, A.City, SP.Name, CR.Name
ORDER BY V.VendorID;
GO

--查看供應商提供的產(chǎn)品
USE AdventureWorks;
GO
SELECT P.ProductNumber, P.Name AS Product, V.Name AS Vendor, PV.LastReceiptCost
FROM Production.Product AS P
JOIN Purchasing.ProductVendor AS PV ON P.ProductID = PV.ProductID
JOIN Purchasing.Vendor AS V ON V.VendorID = PV.VendorID
ORDER BY P.Name ;
GO

--按供應商查看供應商聯(lián)系人
GO
SELECT V.Name as Vendor, C.FirstName, C.LastName, CT.Name AS Title
FROM Person.Contact AS C
    JOIN Purchasing.VendorContact VC ON C.ContactID = VC.ContactID
    JOIN Person.ContactType CT ON CT.ContactTypeID = VC.ContactTypeID
    JOIN Purchasing.Vendor V ON V.VendorID = VC.VendorID
ORDER BY V.Name;
GO

--按供應商查看采購
USE AdventureWorks;
GO
SELECT V.Name AS Vendor, SUM(PH.TotalDue)AS [Total Purchase],
    AVG(PH.TotalDue)AS [Average Purchase], MIN(PH.TotalDue)
    AS [Minimum Purchase], MAX(PH.TotalDue)AS [Maximum Purchase]
FROM Purchasing.Vendor AS V
    JOIN Purchasing.PurchaseOrderHeader AS PH ON V.VendorID = PH.VendorID
GROUP BY V.Name
ORDER BY V.Name;
GO

Re: SQL Server 2005盛宴系列之三：Adventure Works示例數據庫介紹腳本四

--查看父級產(chǎn)品的多級別物料清單列表
USE AdventureWorks
GO
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

--查看產(chǎn)品庫存
USE AdventureWorks
GO
SELECT P.[Name] AS Product, L.[Name] AS [Inventory Location],
    SUM(PI.Quantity)AS [Qty Available]
FROM Production.Product AS P
    JOIN Production.ProductInventory AS PI ON P.ProductID = PI.ProductID
    JOIN Production.Location AS L ON PI.LocationID = L.LocationID
GROUP BY P.[Name], L.[Name]
ORDER BY P.[Name]
GO

--按產(chǎn)品查看工作訂單
USE AdventureWorks
GO
SELECT WorkOrderID, P.[Name] AS Product, OrderQty, DueDate
FROM Production.WorkOrder W
JOIN Production.Product P ON W.ProductID = P.ProductID
WHERE P.ProductSubcategoryID IN (1, 2, 3)
ORDER BY P.[Name], DueDate
GO

Re: SQL Server 2005盛宴系列之三：Adventure Works示例數據庫介紹腳本五

--返回單個(gè)銷(xiāo)售人員的銷(xiāo)售訂單數量以及該銷(xiāo)售人員的上級銷(xiāo)售經(jīng)理的銷(xiāo)售訂單數量
USE AdventureWorks;
GO
WITH Sales_CTE (SalesPersonID, NumberOfOrders, MaxDate)
AS
(
    SELECT SalesPersonID, COUNT(*), MAX(OrderDate)
    FROM Sales.SalesOrderHeader
    GROUP BY SalesPersonID
)
SELECT E.EmployeeID, OS.NumberOfOrders, OS.MaxDate,
    E.ManagerID, OM.NumberOfOrders, OM.MaxDate
FROM HumanResources.Employee AS E
    JOIN Sales_CTE AS OS
    ON E.EmployeeID = OS.SalesPersonID
    LEFT OUTER JOIN Sales_CTE AS OM
    ON E.ManagerID = OM.SalesPersonID
ORDER BY E.EmployeeID;
GO

Re: SQL Server 2005盛宴系列之三：Adventure Works示例數據庫介紹腳本六

USE AdventureWorks
IF EXISTS(SELECT [name] FROM sys.tables WHERE [name] = ‘P_C‘)
DROP TABLE P_C
CREATE TABLE P_C (ID int, ParentID int)

INSERT INTO P_C VALUES (0,NULL)
--切忌使用0作為根節點(diǎn)的父節點(diǎn)引用值
--否則會(huì )引起逆向枚舉時(shí)的死循環(huán)而導致超出CTE的自嵌套層數限制
INSERT INTO P_C VALUES (1,0)
INSERT INTO P_C VALUES (2,0)
INSERT INTO P_C VALUES (3,1)
INSERT INTO P_C VALUES (4,2)
INSERT INTO P_C VALUES (5,3)
INSERT INTO P_C VALUES (6,0)
INSERT INTO P_C VALUES (7,4)
INSERT INTO P_C VALUES (8,5)
INSERT INTO P_C VALUES (9,8)
INSERT INTO P_C VALUES (10,4)
INSERT INTO P_C VALUES (11,10)
INSERT INTO P_C VALUES (12,2)
INSERT INTO P_C VALUES (13,3)
INSERT INTO P_C VALUES (14,6)
INSERT INTO P_C VALUES (15,14)
INSERT INTO P_C VALUES (16,11)
INSERT INTO P_C VALUES (17,14)
INSERT INTO P_C VALUES (18,15)
INSERT INTO P_C VALUES (19,18)
INSERT INTO P_C VALUES (20,7)
INSERT INTO P_C VALUES (21,9)
INSERT INTO P_C VALUES (22,9)
INSERT INTO P_C VALUES (23,12)
INSERT INTO P_C VALUES (24,13)
INSERT INTO P_C VALUES (25,12)
INSERT INTO P_C VALUES (26,15)
GO

--CTE的實(shí)現代碼，僅檢索節點(diǎn)5的所有子節點(diǎn)
WITH PCV(P, C, L) AS
(
    SELECT P.ID, P.ParentID, 0
    FROM P_C AS P WHERE P.ParentID = 5
    UNION ALL
    SELECT C.ID, C.ParentID, L + 1
    FROM P_C AS C INNER JOIN PCV ON C.ParentID = PCV.P
)
SELECT * FROM PCV
GO

--利用存儲過(guò)程實(shí)現任意節點(diǎn)子節點(diǎn)的檢索
CREATE PROCEDURE dbo.usp_GetChildrenHierarchy
@ParentID int
AS
WITH PCV(P, C, L) AS
(
    SELECT P.ID, P.ParentID, 0 --初始的層次計數
    FROM P_C AS P WHERE P.ParentID = @ParentID
    UNION ALL
    SELECT C.ID, C.ParentID, L + 1
    FROM P_C AS C INNER JOIN PCV ON C.ParentID = PCV.P
)
SELECT * FROM PCV
GO

EXEC dbo.usp_GetChildrenHierarchy 3
EXEC dbo.usp_GetChildrenHierarchy 5
EXEC dbo.usp_GetChildrenHierarchy 8
EXEC dbo.usp_GetChildrenHierarchy 9

--利用存儲過(guò)程實(shí)現任意節點(diǎn)父節點(diǎn)的檢索
CREATE PROCEDURE dbo.usp_GetParentHierarchy
@ChildrenID int
AS
WITH PCV(C, P, L) AS
(
    SELECT P.ID, P.ParentID, 0 --初始的層次計數
    FROM P_C AS P WHERE P.ID = @ChildrenID
    UNION ALL
    SELECT C.ID, C.ParentID, L - 1
    FROM P_C AS C INNER JOIN PCV ON C.ID = PCV.P
)
SELECT * FROM PCV
GO

EXEC dbo.usp_GetParentHierarchy 3
EXEC dbo.usp_GetParentHierarchy 5
EXEC dbo.usp_GetParentHierarchy 8
EXEC dbo.usp_GetParentHierarchy 9

--清理數據庫
DROP PROCEDURE dbo.usp_GetChildrenHierarchy
DROP PROCEDURE dbo.usp_GetParentHierarchy
DROP TABLE P_C

Re: SQL Server 2005盛宴系列之三：Adventure Works示例數據庫介紹腳本七

USE AdventureWorks
GO

--顯示有哪些表引用了該對象
IF OBJECT_ID(‘dbo.ufn_ListReferencingTables‘) <> 0
DROP FUNCTION dbo.ufn_ListReferencingTables
GO

CREATE FUNCTION dbo.ufn_ListReferencingTables
(@referenced_table AS varchar(200))
RETURNS TABLE AS
RETURN(
SELECT SCHEMA_NAME(CONVERT(int, OBJECTPROPERTYEX(fkeyid, ‘SchemaId‘)))
+ ‘.‘ + OBJECT_NAME(fkeyid)
AS [Referencing Table]
FROM sys.sysreferences
WHERE rkeyid = OBJECT_ID(@referenced_table)
)
GO

本站僅提供存儲服務(wù)，所有內容均由用戶(hù)發(fā)布，如發(fā)現有害或侵權內容，請點(diǎn)擊舉報。

欧美性猛交XXXX免费看蜜桃,成人网18免费韩国,亚洲国产成人精品区综合,欧美日韩一区二区三区高清不卡,亚洲综合一区二区精品久久