以下這些內容﹐是本人當初對sql的一些小小積累﹐保存的是.sql文件﹐可以直接在查詢分析器中執行。當然﹐這些內容有些是本人自己寫的﹐有些是從網上收集到的﹐由于當初沒有完整的記下出處地址﹐請源作者見諒不能給出完整的地址﹐當然﹐收集到的這些腳本﹐可能并不是完全照抄原文的﹐因為我是邊學習邊在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
)--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