ITPub博客

首页 > Linux操作系统 > Linux操作系统 > Select

Select

原创 Linux操作系统 作者:keeking 时间:2009-07-23 21:24:13 0 删除 编辑

一.隨機查詢表

SELECT FirstName,LastName
FROM Person.Contact TABLESAMPLE SYSTEM
(2 PERCENT)

二.PIVOT軸查詢應用(可以方便的轉換不含主索引鍵資料表的格式)

SELECT s.Name ShiftName,
h.EmployeeID,
d.Name DepartmentName
FROM HumanResources.EmployeeDepartmentHistory h
INNER JOIN HumanResources.Department d ON
h.DepartmentID = d.DepartmentID
INNER JOIN HumanResources.Shift s ON
h.ShiftID = s.ShiftID
WHERE EndDate IS NULL AND
d.Name IN ('Production', 'Engineering', 'Marketing')
ORDER BY ShiftName

-------------------------------------------------
SELECT ShiftName,
Production,
Engineering,
Marketing
FROM
(SELECT s.Name ShiftName,
h.EmployeeID,
d.Name DepartmentName
FROM HumanResources.EmployeeDepartmentHistory h
INNER JOIN HumanResources.Department d ON
h.DepartmentID = d.DepartmentID
INNER JOIN HumanResources.Shift s ON
h.ShiftID = s.ShiftID
WHERE EndDate IS NULL AND
d.Name IN ('Production', 'Engineering', 'Marketing')) AS a
PIVOT
(
COUNT(EmployeeID)
FOR DepartmentName IN ([Production], [Engineering], [Marketing]))
AS b
ORDER BY ShiftName

三.UNPIVOT

CREATE TABLE dbo.Contact
(EmployeeID int NOT NULL,
PhoneNumber1 bigint,
PhoneNumber2 bigint,
PhoneNumber3 bigint)
GO
INSERT dbo.Contact
(EmployeeID, PhoneNumber1, PhoneNumber2, PhoneNumber3)
VALUES( 1, 2718353881, 3385531980, 5324571342)
INSERT dbo.Contact
(EmployeeID, PhoneNumber1, PhoneNumber2, PhoneNumber3)
VALUES( 2, 6007163571, 6875099415, 7756620787)
INSERT dbo.Contact
(EmployeeID, PhoneNumber1, PhoneNumber2, PhoneNumber3)
VALUES( 3, 9439250939, NULL, NULL)

SELECT EmployeeID,
PhoneType,
PhoneValue
FROM
(SELECT EmployeeID, PhoneNumber1, PhoneNumber2, PhoneNumber3
FROM dbo.Contact) c
UNPIVOT
(PhoneValue FOR PhoneType IN ([PhoneNumber1], [PhoneNumber2], [PhoneNumber3])
) AS p

四.交叉與排除

SELECT ProductID,
Name
FROM TableA
EXCEPT
SELECT ProductID,
Name
FROM TableB
-----------------
SELECT ProductID,
Name
FROM TableA a

where exists(
SELECT ProductID,
Name
FROM TableB where a.ProductID=TableB.ProductID)
-------------等價
SELECT ProductID,
Name
FROM TableA
INTERSECT
SELECT ProductID,
Name
FROM TableB

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/7899089/viewspace-610104/,如需转载,请注明出处,否则将追究法律责任。

上一篇: 视图
下一篇: 表锁
请登录后发表评论 登录
全部评论

注册时间:2009-07-23

  • 博文量
    194
  • 访问量
    241432