以下這些內容﹐是本人當初對sql的一些小小積累﹐保存的是.sql文件﹐可以直接在查詢分析器中執行。當然﹐這些內容有些是本人自己寫的﹐有些是從網上收集到的﹐由于當初沒有完整的記下出處地址﹐請源作者見諒不能給出完整的地址﹐當然﹐收集到的這些腳本﹐可能并不是完全照抄原文的﹐因為我是邊學習邊在sql中實踐的。但畢竟是借鑒人家思想的﹐還請源作者見到后﹐能本著交流學習的態度﹐給我郵件告知你的鏈接地址和網名﹐我將盡快給加上你的大名﹐或直接在回復中指出。本人鄭重承諾以下內容不會用于商業用途。
本內容將不斷更新中....
另外﹐學習sql﹐可以去以下几個地方逛逛﹐轉轉﹕
http://blog.csdn.net/zjcxc(CSDN鄒建的專欄)
http://blog.csdn.net/leimin(CSDN thinking黃山光明頂)
http://www.sqlmag.com(SQL Server雜志)
http://www.sqlservercentral.com(SQL Server中心)
本內容將不斷更新中....
--使用Sql常用的語句(不斷總結更新中
)
--1﹑獲得某一欄位的重復記錄的﹕
DECLARE @tableName TABLE(IntValue1 INT,IntValue2 INT,VarContent VARCHAR(20))
INSERT @tableName VALUES(2,3,'abc')
INSERT @tableName VALUES(2,4,'bb')
INSERT @tableName VALUES(2,5,'abc')
INSERT @tableName VALUES(3,6,'ccc')
INSERT @tableName VALUES(3,7,'ccc')
INSERT @tableName VALUES(6,3,'dd')
--方法一:
SELECT * FROM @tableName WHERE VarContent in
(SELECT VarContent FROM @tableName GROUP BY VarContent
HAVING COUNT(VarContent)>1)
--方法二:
SELECT DISTINCT * FROM @tableName t WHERE
(SELECT COUNT(VarContent) FROM @tableName WHERE t.VarContent=VarContent)>1
--2﹑找出分組中數據第二大的值:
DECLARE @SectableName TABLE (IntValue INT ,bitGroup BIT)
INSERT INTO @SectableName SELECT 2006001,0
UNION ALL SELECT 2006002,0
UNION ALL SELECT 2006003,0
UNION ALL SELECT 2006101,1
UNION ALL SELECT 2006102,1
UNION ALL SELECT 2006103,1
SELECT * FROM @SectableName WHERE IntValue IN
(SELECT MAX(IntValue) AS SecPerGroup FROM @SectableName WHERE IntValue NOT IN
(SELECT MAX(IntValue) AS MaxPerGroup FROM @SectableName GROUP BY bitGroup)
GROUP BY bitGroup)
--3﹑找出分組中數據最大的值:
DECLARE @SectableName TABLE (IntValue INT ,SortValue INT,bitGroup BIT)
INSERT INTO @SectableName SELECT 2006001,1,0
UNION ALL SELECT 2006002,1,0
UNION ALL SELECT 2006003,1,0
UNION ALL SELECT 2006101,2,1
UNION ALL SELECT 2006102,2,1
UNION ALL SELECT 2006103,2,1
-- 方法一:
SELECT A.* FROM @SectableName A,
(SELECT MAX(IntValue) IntValue, MAX(SortValue) SortValue FROM @SectableName GROUP BY bitGroup) B
WHERE A.IntValue = B.IntValue AND A.SortValue=B.SortValue
-- 方法二:
SELECT B.IntValue,A.SortValue,B.bitGroup From @SectableName A
INNER JOIN
(
SELECT MAX(IntValue) IntValue,bitGroup FROM @SectableName GROUP BY bitGroup
) B ON A.IntValue = B.IntValue AND A.bitGroup= B.bitGroup ORDER BY bitGroup
--4﹑找出排序隊列中斷缺的數字
DECLARE @ThrtableName TABLE (IntId INT)
INSERT INTO @ThrtableName SELECT 1
UNION ALL SELECT 2
UNION ALL SELECT 3
UNION ALL SELECT 4
UNION ALL SELECT 5
UNION ALL SELECT 7
UNION ALL SELECT 8
UNION ALL SELECT 9
UNION ALL SELECT 10
--方法一:
SELECT MIN(IntId) AS IntIdNotInQueue FROM
(SELECT IntId+1 AS IntId FROM @ThrtableName WHERE IntId+1 NOT IN
(SELECT * FROM @ThrtableName)
) t
--方法二:
DECLARE @IntId INT
SELECT @IntId = MIN(IntId) FROM @ThrtableName
WHILE EXISTS(SELECT 1 FROM @ThrtableName WHERE IntId = @IntId)
BEGIN
SET @IntId = @IntId + 1
END
SELECT @IntId AS IntIdNotInQueue
--方法三:
SELECT MIN(t.a) AS IntIdNotInQueue FROM
(SELECT IntId+1 a FROM @ThrtableName t WHERE NOT EXISTS
(SELECT 1 FROM @ThrtableName WHERE IntId = t.IntId+1)
) t
--5﹑查詢庫存的范例
DECLARE @Item TABLE(ItemID INT, ItemName VARCHAR(20))--Material表
DECLARE @Stock TABLE(ItemID INT,Quantity INT)--庫存表
DECLARE @Sale TABLE(ItemID INT, Quantity INT)--出庫表
DECLARE @InCome TABLE(ItemID INT, Quantity INT)--入庫表
INSERT INTO @Item SELECT 1, '筆記本'UNION ALL SELECT 2, '台式機'UNION ALL SELECT 3, '移動PC'
INSERT INTO @Stock SELECT 1, 10 UNION ALL SELECT 2, 22
INSERT INTO @InCome SELECT 1, 80 UNION ALL SELECT 2, 100
INSERT INTO @Sale SELECT 1, 81 UNION ALL SELECT 2, 101
--計算庫存
SELECT
MAX(B.ItemID) ItemID,
MAX(B.ItemName) ItemName,
SUM(ISNULL(Quantity, 0)) Quantity
FROM
(
SELECT ItemID, ISNULL(Quantity, 0) Quantity FROM @Stock -- 庫存表
UNION ALL
SELECT ItemID, SUM(ISNULL(Quantity, 0)) Quantity FROM @InCome GROUP BY ItemID -- 入庫表
UNION ALL
SELECT ItemID, -SUM(ISNULL(Quantity, 0)) Quantity FROM @Sale GROUP BY ITEMID -- 出庫表
) A
RIGHT JOIN @Item B ON A.ItemID = B.ItemID
GROUP BY A.ItemID ORDER BY A.ItemID ASC
--6﹑測試采購報表
DECLARE @ TABLE(
InvoiceNo VARCHAR(4),
InvoiceDate DATETIME,
Supplier CHAR(2),
Material VARCHAR(20),
Invoice_UnitPrice DECIMAL(18,2),
Currency CHAR(3)
)
INSERT INTO @ (InvoiceNo , InvoiceDate , Supplier, Material, Invoice_UnitPrice, Currency)
SELECT '1001', '2006-05-01', 'SH' , 'MaterialA1' , 1.5 , 'RMB' UNION
SELECT '1001', '2006-05-01', 'SH', 'MaterialA2', 2.5, 'RMB' UNION
SELECT '1002', '2006-05-11', 'BJ', 'MaterialB1', 0.8, 'RMB' UNION
SELECT '1002', '2006-05-11', 'BJ', 'MaterialB2', 1.8, 'RMB' UNION
SELECT '1002', '2006-05-11', 'BJ', 'MaterialB3', 2.0, 'RMB' UNION
SELECT '1003', '2006-05-13', 'HB', 'MaterialA1', 1.9, 'USD' UNION
SELECT '1003', '2006-05-13', 'HB', 'MaterialA2', 2.3, 'USD' UNION
SELECT '1003', '2006-05-13', 'HB', 'MaterialB1', 1.0, 'USD' UNION
SELECT '1004', '2006-05-15', 'SD', 'MaterialB1', 1.2, 'USD' UNION
SELECT '1004', '2006-05-15', 'SD', 'MaterialB2', 1.5, 'RMB' UNION
SELECT '1004', '2006-05-15', 'SD', 'MaterialB3', 2.1, 'USD'
SELECT * FROM @
SELECT
InvoiceNo , InvoiceDate , Supplier, Material, Invoice_UnitPrice, Currency,
ISNULL(
(SELECT TOP 1 Invoice_UnitPrice FROM @ B WHERE A.Material = B.Material AND A.InvoiceDate > B.InvoiceDate)
,0) AS Previous_Invoice_UnitPrice,
ISNULL(
(SELECT TOP 1 Currency FROM @ B WHERE A.Material = B.Material AND A.InvoiceDate > B.InvoiceDate)
,NULL) AS Previous_Invoice_Currency
FROM
@ A
--7﹑一條語句刪除表中相同的記錄
DECLARE @ TABLE(id INT IDENTITY, sName VARCHAR(10)
)
INSERT INTO @ SELECT '張三'
UNION ALL SELECT '王二'
UNION ALL SELECT '張三'
UNION ALL SELECT '李四'
UNION ALL SELECT '王二'
DELETE FROM @ WHERE NOT id IN (SELECT MAX(id) FROM @ GROUP BY sName)
SELECT * FROM @
--8﹑如下這種查詢﹐看看似簡單﹐實際上..呵呵
DECLARE @ TABLE(fName VARCHAR(10), fID INT)
DECLARE @SaleInfo TABLE(fID INT, fQuantity INT)
INSERT INTO @ SELECT'上海XX公司', 1 UNION ALL SELECT'上海YY公司', 2
INSERT INTO @SaleInfo SELECT 1, 120
UNION ALL SELECT 1, 130
UNION ALL SELECT 2, 10
UNION ALL SELECT 2, 13000
--SELECT * FROM @
--SELECT * FROM @saleinfo
SELECT A.fID, fName, fQuantity, IDENTITY(INT, 1,1) AS ID INTO # FROM @ A
LEFT JOIN
@saleinfo B ON A.fId = B.fId
SELECT * FROM #
SELECT fName=CASE WHEN EXISTS(SELECT 1 FROM # WHERE fname=T.fname AND ID<t.ID) THEN '' ELSE fname END,
fQuantity,
ID
FROM # T
DROP TABLE #
--9﹑SQL實現樹查詢
CREATE TABLE treeTable(employee_id VARCHAR(10),employee_name VARCHAR(10),employee_age INT,employee_salary NUMERIC(9,0),manager_id VARCHAR(10))
INSERT INTO treeTable SELECT 'E9901','張建平',50,8000,NULL
UNION ALL SELECT 'E9902','余順景',35,5300,'E9901'
UNION ALL SELECT 'E9903','鄭可可',38,5000,'E9901'
UNION ALL SELECT 'E9904','劉小青',32,3600,'E9902'
UNION ALL SELECT 'E9905','謝偉', 36,3400,'E9902'
UNION ALL SELECT 'E9906','肖遙', 26,3350,'E9903'
UNION ALL SELECT 'E9907','黃菁菁',22,2800,'E9906'
SELECT * FROM treeTable
--a﹑取某個員工的上Ｎ級的信息﹐即某節點的父類信息
CREATE FUNCTION dbo.GetManager( @employee_id AS VARCHAR(10),@level AS INT = 1)
RETURNS VARCHAR(10) AS
BEGIN
IF @level = 0 --如果@level=0﹐表示已經找到其上司號碼
RETURN @employee_id
RETURN dbo.GetManager(
(SELECT manager_id FROM treeTable WHERE employee_id = @employee_id),
@level -1) -- 如果 @level > 0，則返回直接上司的@level-1級的上司號碼
END
--調用方法:
SELECT * FROM treeTable WHERE employee_id =dbo.GetManager('E9907', 2)
--b_1﹑取某職工的下級員工的工資﹐即某節點的統計信息
CREATE FUNCTION dbo.GetTotalSalary(@manager_id AS VARCHAR(10))
RETURNS INT AS
BEGIN
RETURN (
SELECT employee_salary FROM treeTable WHERE employee_id = @manager_id) +
CASE
WHEN EXISTS(SELECT * FROM treeTable WHERE manager_id = @manager_id) THEN
( SELECT SUM(dbo.GetTotalSalary(employee_id)) FROM treeTable WHERE manager_id = @manager_id
)
ELSE 0
END
END
--調用方法:
SELECT dbo.GetTotalSalary('E9902')
--b_2﹑取某個職工共有几級下屬。即某節點的縱深級別(深度)
CREATE FUNCTION dbo.GetUnderlyingLevel(@manager_id AS VARCHAR(10))
RETURNS INT AS
BEGIN
RETURN
CASE
WHEN EXISTS(SELECT * FROM treeTable WHERE manager_id = @manager_id)
THEN 1 + (SELECT MAX(dbo.GetUnderlyingLevel(employee_id)) FROM treeTable WHERE manager_id = @manager_id)
ELSE 1
END
END
--調用方法:
SELECT dbo.GetUnderlyingLevel('E9901')
--c_1﹑取某個職工及其下屬的所有信息。即某節點的所有信息。
CREATE FUNCTION dbo.GetSubtreeInfo1(@manager_id AS VARCHAR(10))
RETURNS @treeinfo TABLE(
employee_id VARCHAR(10) NOT NULL,
employee_name VARCHAR(10) NOT NULL,
employee_age INT NOT NULL,
employee_salary NUMERIC(9,0) NOT NULL,
manager_id VARCHAR(10) NULL,
level INT NOT NULL
) AS
BEGIN
DECLARE @level AS INT
SELECT @level = 0
INSERT INTO @treeinfo
SELECT employee_id, employee_name, employee_age, employee_salary, manager_id, @level
FROM treeTable
WHERE employee_id = @manager_id
WHILE @@ROWCOUNT > 0
BEGIN
SET @level = @level + 1
INSERT INTO @treeinfo
SELECT E.employee_id, E.employee_name, E.employee_age, E.employee_salary, E.manager_id, @level
FROM treeTable AS E JOIN @treeinfo AS T
ON E.manager_id = T.employee_id AND T.level = @level - 1
END
RETURN
END
--調用方法:
SELECT * FROM dbo.GetSubtreeInfo('E9903')
--c_1﹑取某個職工及其下屬的所有信息。即某節點的所有信息。
CREATE FUNCTION dbo.GetSubtreeInfo2(@manager_id AS VARCHAR(10))
RETURNS @treeinfo TABLE(
employee_id VARCHAR(10) NOT NULL,
employee_name VARCHAR(10) NOT NULL,
employee_age INT NOT NULL,
employee_salary NUMERIC(9,0) NOT NULL,
manager_id VARCHAR(10) NULL,
level INT NOT NULL,
flag VARCHAR(200) NOT NULL
) AS
BEGIN
DECLARE @level AS INT,@path AS VARCHAR(200)
SELECT @level = 0,@path='NULL'
INSERT INTO @treeinfo
SELECT employee_id, employee_name, employee_age, employee_salary, manager_id, @level,'NULL->'+ employee_id
FROM treeTable
WHERE employee_id = @manager_id
WHILE @@ROWCOUNT > 0
BEGIN
SET @level = @level + 1
INSERT INTO @treeinfo
SELECT E.employee_id, E.employee_name, E.employee_age, E.employee_salary, E.manager_id, @level,T.flag + '->'+ E.employee_id
FROM treeTable AS E JOIN @treeinfo AS T
ON E.manager_id = T.employee_id AND T.level = @level - 1
END
RETURN
END
--調用方法:
SELECT REPLICATE (' | ', level) + employee_name AS Orginization_Level FROM dbo.GetSubtreeInfo2('E9906') ORDER BY flag
去掉日期型后面的時分秒可以使用簡便的語句:)--1﹑獲得某一欄位的重復記錄的﹕
DECLARE @tableName TABLE(IntValue1 INT,IntValue2 INT,VarContent VARCHAR(20))
INSERT @tableName VALUES(2,3,'abc')
INSERT @tableName VALUES(2,4,'bb')
INSERT @tableName VALUES(2,5,'abc')
INSERT @tableName VALUES(3,6,'ccc')
INSERT @tableName VALUES(3,7,'ccc')
INSERT @tableName VALUES(6,3,'dd')
--方法一:
SELECT * FROM @tableName WHERE VarContent in
(SELECT VarContent FROM @tableName GROUP BY VarContent
HAVING COUNT(VarContent)>1)
--方法二:
SELECT DISTINCT * FROM @tableName t WHERE
(SELECT COUNT(VarContent) FROM @tableName WHERE t.VarContent=VarContent)>1
--2﹑找出分組中數據第二大的值:
DECLARE @SectableName TABLE (IntValue INT ,bitGroup BIT)
INSERT INTO @SectableName SELECT 2006001,0
UNION ALL SELECT 2006002,0
UNION ALL SELECT 2006003,0
UNION ALL SELECT 2006101,1
UNION ALL SELECT 2006102,1
UNION ALL SELECT 2006103,1
SELECT * FROM @SectableName WHERE IntValue IN
(SELECT MAX(IntValue) AS SecPerGroup FROM @SectableName WHERE IntValue NOT IN
(SELECT MAX(IntValue) AS MaxPerGroup FROM @SectableName GROUP BY bitGroup)
GROUP BY bitGroup)
--3﹑找出分組中數據最大的值:
DECLARE @SectableName TABLE (IntValue INT ,SortValue INT,bitGroup BIT)
INSERT INTO @SectableName SELECT 2006001,1,0
UNION ALL SELECT 2006002,1,0
UNION ALL SELECT 2006003,1,0
UNION ALL SELECT 2006101,2,1
UNION ALL SELECT 2006102,2,1
UNION ALL SELECT 2006103,2,1
-- 方法一:
SELECT A.* FROM @SectableName A,
(SELECT MAX(IntValue) IntValue, MAX(SortValue) SortValue FROM @SectableName GROUP BY bitGroup) B
WHERE A.IntValue = B.IntValue AND A.SortValue=B.SortValue
-- 方法二:
SELECT B.IntValue,A.SortValue,B.bitGroup From @SectableName A
INNER JOIN
(
SELECT MAX(IntValue) IntValue,bitGroup FROM @SectableName GROUP BY bitGroup
) B ON A.IntValue = B.IntValue AND A.bitGroup= B.bitGroup ORDER BY bitGroup
--4﹑找出排序隊列中斷缺的數字
DECLARE @ThrtableName TABLE (IntId INT)
INSERT INTO @ThrtableName SELECT 1
UNION ALL SELECT 2
UNION ALL SELECT 3
UNION ALL SELECT 4
UNION ALL SELECT 5
UNION ALL SELECT 7
UNION ALL SELECT 8
UNION ALL SELECT 9
UNION ALL SELECT 10
--方法一:
SELECT MIN(IntId) AS IntIdNotInQueue FROM
(SELECT IntId+1 AS IntId FROM @ThrtableName WHERE IntId+1 NOT IN
(SELECT * FROM @ThrtableName)
) t
--方法二:
DECLARE @IntId INT
SELECT @IntId = MIN(IntId) FROM @ThrtableName
WHILE EXISTS(SELECT 1 FROM @ThrtableName WHERE IntId = @IntId)
BEGIN
SET @IntId = @IntId + 1
END
SELECT @IntId AS IntIdNotInQueue
--方法三:
SELECT MIN(t.a) AS IntIdNotInQueue FROM
(SELECT IntId+1 a FROM @ThrtableName t WHERE NOT EXISTS
(SELECT 1 FROM @ThrtableName WHERE IntId = t.IntId+1)
) t
--5﹑查詢庫存的范例
DECLARE @Item TABLE(ItemID INT, ItemName VARCHAR(20))--Material表
DECLARE @Stock TABLE(ItemID INT,Quantity INT)--庫存表
DECLARE @Sale TABLE(ItemID INT, Quantity INT)--出庫表
DECLARE @InCome TABLE(ItemID INT, Quantity INT)--入庫表
INSERT INTO @Item SELECT 1, '筆記本'UNION ALL SELECT 2, '台式機'UNION ALL SELECT 3, '移動PC'
INSERT INTO @Stock SELECT 1, 10 UNION ALL SELECT 2, 22
INSERT INTO @InCome SELECT 1, 80 UNION ALL SELECT 2, 100
INSERT INTO @Sale SELECT 1, 81 UNION ALL SELECT 2, 101
--計算庫存
SELECT
MAX(B.ItemID) ItemID,
MAX(B.ItemName) ItemName,
SUM(ISNULL(Quantity, 0)) Quantity
FROM
(
SELECT ItemID, ISNULL(Quantity, 0) Quantity FROM @Stock -- 庫存表
UNION ALL
SELECT ItemID, SUM(ISNULL(Quantity, 0)) Quantity FROM @InCome GROUP BY ItemID -- 入庫表
UNION ALL
SELECT ItemID, -SUM(ISNULL(Quantity, 0)) Quantity FROM @Sale GROUP BY ITEMID -- 出庫表
) A
RIGHT JOIN @Item B ON A.ItemID = B.ItemID
GROUP BY A.ItemID ORDER BY A.ItemID ASC
--6﹑測試采購報表
DECLARE @ TABLE(
InvoiceNo VARCHAR(4),
InvoiceDate DATETIME,
Supplier CHAR(2),
Material VARCHAR(20),
Invoice_UnitPrice DECIMAL(18,2),
Currency CHAR(3)
)
INSERT INTO @ (InvoiceNo , InvoiceDate , Supplier, Material, Invoice_UnitPrice, Currency)
SELECT '1001', '2006-05-01', 'SH' , 'MaterialA1' , 1.5 , 'RMB' UNION
SELECT '1001', '2006-05-01', 'SH', 'MaterialA2', 2.5, 'RMB' UNION
SELECT '1002', '2006-05-11', 'BJ', 'MaterialB1', 0.8, 'RMB' UNION
SELECT '1002', '2006-05-11', 'BJ', 'MaterialB2', 1.8, 'RMB' UNION
SELECT '1002', '2006-05-11', 'BJ', 'MaterialB3', 2.0, 'RMB' UNION
SELECT '1003', '2006-05-13', 'HB', 'MaterialA1', 1.9, 'USD' UNION
SELECT '1003', '2006-05-13', 'HB', 'MaterialA2', 2.3, 'USD' UNION
SELECT '1003', '2006-05-13', 'HB', 'MaterialB1', 1.0, 'USD' UNION
SELECT '1004', '2006-05-15', 'SD', 'MaterialB1', 1.2, 'USD' UNION
SELECT '1004', '2006-05-15', 'SD', 'MaterialB2', 1.5, 'RMB' UNION
SELECT '1004', '2006-05-15', 'SD', 'MaterialB3', 2.1, 'USD'
SELECT * FROM @
SELECT
InvoiceNo , InvoiceDate , Supplier, Material, Invoice_UnitPrice, Currency,
ISNULL(
(SELECT TOP 1 Invoice_UnitPrice FROM @ B WHERE A.Material = B.Material AND A.InvoiceDate > B.InvoiceDate)
,0) AS Previous_Invoice_UnitPrice,
ISNULL(
(SELECT TOP 1 Currency FROM @ B WHERE A.Material = B.Material AND A.InvoiceDate > B.InvoiceDate)
,NULL) AS Previous_Invoice_Currency
FROM
@ A
--7﹑一條語句刪除表中相同的記錄
DECLARE @ TABLE(id INT IDENTITY, sName VARCHAR(10)
)
INSERT INTO @ SELECT '張三'
UNION ALL SELECT '王二'
UNION ALL SELECT '張三'
UNION ALL SELECT '李四'
UNION ALL SELECT '王二'
DELETE FROM @ WHERE NOT id IN (SELECT MAX(id) FROM @ GROUP BY sName)
SELECT * FROM @
--8﹑如下這種查詢﹐看看似簡單﹐實際上
..呵呵DECLARE @ TABLE(fName VARCHAR(10), fID INT)
DECLARE @SaleInfo TABLE(fID INT, fQuantity INT)
INSERT INTO @ SELECT'上海XX公司', 1 UNION ALL SELECT'上海YY公司', 2
INSERT INTO @SaleInfo SELECT 1, 120
UNION ALL SELECT 1, 130
UNION ALL SELECT 2, 10
UNION ALL SELECT 2, 13000
--SELECT * FROM @
--SELECT * FROM @saleinfo
SELECT A.fID, fName, fQuantity, IDENTITY(INT, 1,1) AS ID INTO # FROM @ A
LEFT JOIN
@saleinfo B ON A.fId = B.fId
SELECT * FROM #
SELECT fName=CASE WHEN EXISTS(SELECT 1 FROM # WHERE fname=T.fname AND ID<t.ID) THEN '' ELSE fname END,
fQuantity,
ID
FROM # T
DROP TABLE #
--9﹑SQL實現樹查詢
CREATE TABLE treeTable(employee_id VARCHAR(10),employee_name VARCHAR(10),employee_age INT,employee_salary NUMERIC(9,0),manager_id VARCHAR(10))
INSERT INTO treeTable SELECT 'E9901','張建平',50,8000,NULL
UNION ALL SELECT 'E9902','余順景',35,5300,'E9901'
UNION ALL SELECT 'E9903','鄭可可',38,5000,'E9901'
UNION ALL SELECT 'E9904','劉小青',32,3600,'E9902'
UNION ALL SELECT 'E9905','謝偉', 36,3400,'E9902'
UNION ALL SELECT 'E9906','肖遙', 26,3350,'E9903'
UNION ALL SELECT 'E9907','黃菁菁',22,2800,'E9906'
SELECT * FROM treeTable
--a﹑取某個員工的上Ｎ級的信息﹐即某節點的父類信息
CREATE FUNCTION dbo.GetManager( @employee_id AS VARCHAR(10),@level AS INT = 1)
RETURNS VARCHAR(10) AS
BEGIN
IF @level = 0 --如果@level=0﹐表示已經找到其上司號碼
RETURN @employee_id
RETURN dbo.GetManager(
(SELECT manager_id FROM treeTable WHERE employee_id = @employee_id),
@level -1) -- 如果 @level > 0，則返回直接上司的@level-1級的上司號碼
END
--調用方法:
SELECT * FROM treeTable WHERE employee_id =dbo.GetManager('E9907', 2)
--b_1﹑取某職工的下級員工的工資﹐即某節點的統計信息
CREATE FUNCTION dbo.GetTotalSalary(@manager_id AS VARCHAR(10))
RETURNS INT AS
BEGIN
RETURN (
SELECT employee_salary FROM treeTable WHERE employee_id = @manager_id) +
CASE
WHEN EXISTS(SELECT * FROM treeTable WHERE manager_id = @manager_id) THEN
( SELECT SUM(dbo.GetTotalSalary(employee_id)) FROM treeTable WHERE manager_id = @manager_id
)
ELSE 0
END
END
--調用方法:
SELECT dbo.GetTotalSalary('E9902')
--b_2﹑取某個職工共有几級下屬。即某節點的縱深級別(深度)
CREATE FUNCTION dbo.GetUnderlyingLevel(@manager_id AS VARCHAR(10))
RETURNS INT AS
BEGIN
RETURN
CASE
WHEN EXISTS(SELECT * FROM treeTable WHERE manager_id = @manager_id)
THEN 1 + (SELECT MAX(dbo.GetUnderlyingLevel(employee_id)) FROM treeTable WHERE manager_id = @manager_id)
ELSE 1
END
END
--調用方法:
SELECT dbo.GetUnderlyingLevel('E9901')
--c_1﹑取某個職工及其下屬的所有信息。即某節點的所有信息。
CREATE FUNCTION dbo.GetSubtreeInfo1(@manager_id AS VARCHAR(10))
RETURNS @treeinfo TABLE(
employee_id VARCHAR(10) NOT NULL,
employee_name VARCHAR(10) NOT NULL,
employee_age INT NOT NULL,
employee_salary NUMERIC(9,0) NOT NULL,
manager_id VARCHAR(10) NULL,
level INT NOT NULL
) AS
BEGIN
DECLARE @level AS INT
SELECT @level = 0
INSERT INTO @treeinfo
SELECT employee_id, employee_name, employee_age, employee_salary, manager_id, @level
FROM treeTable
WHERE employee_id = @manager_id
WHILE @@ROWCOUNT > 0
BEGIN
SET @level = @level + 1
INSERT INTO @treeinfo
SELECT E.employee_id, E.employee_name, E.employee_age, E.employee_salary, E.manager_id, @level
FROM treeTable AS E JOIN @treeinfo AS T
ON E.manager_id = T.employee_id AND T.level = @level - 1
END
RETURN
END
--調用方法:
SELECT * FROM dbo.GetSubtreeInfo('E9903')
--c_1﹑取某個職工及其下屬的所有信息。即某節點的所有信息。
CREATE FUNCTION dbo.GetSubtreeInfo2(@manager_id AS VARCHAR(10))
RETURNS @treeinfo TABLE(
employee_id VARCHAR(10) NOT NULL,
employee_name VARCHAR(10) NOT NULL,
employee_age INT NOT NULL,
employee_salary NUMERIC(9,0) NOT NULL,
manager_id VARCHAR(10) NULL,
level INT NOT NULL,
flag VARCHAR(200) NOT NULL
) AS
BEGIN
DECLARE @level AS INT,@path AS VARCHAR(200)
SELECT @level = 0,@path='NULL'
INSERT INTO @treeinfo
SELECT employee_id, employee_name, employee_age, employee_salary, manager_id, @level,'NULL->'+ employee_id
FROM treeTable
WHERE employee_id = @manager_id
WHILE @@ROWCOUNT > 0
BEGIN
SET @level = @level + 1
INSERT INTO @treeinfo
SELECT E.employee_id, E.employee_name, E.employee_age, E.employee_salary, E.manager_id, @level,T.flag + '->'+ E.employee_id
FROM treeTable AS E JOIN @treeinfo AS T
ON E.manager_id = T.employee_id AND T.level = @level - 1
END
RETURN
END
--調用方法:
SELECT REPLICATE (' | ', level) + employee_name AS Orginization_Level FROM dbo.GetSubtreeInfo2('E9906') ORDER BY flag
-- 0 is a date (01/01/1900 00:00:00.000)
Declare @StartDate datetime
SELECT @StartDate = DATEADD(dd,DATEDIFF(dd,0,@StartDate),0)
另外﹐學習sql﹐可以去以下几個地方逛逛﹐轉轉﹕
http://blog.csdn.net/zjcxc(CSDN鄒建的專欄)
http://blog.csdn.net/leimin(CSDN thinking黃山光明頂)
http://www.sqlmag.com(SQL Server雜志)
http://www.sqlservercentral.com(SQL Server中心)
