sql累積

.

--使用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

另外﹐學習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中心)

http://www.cnblogs.com/jinliangliu/archive/2006/10/31/545684.html