SQL Server實用經典例句之一

SQLServer 中的斷行符號符與分行符號
定位字元：CHAR(9)  分行符號：CHAR(10)  斷行符號符：CHAR(13)
1、將 varchar 值作為條件，查詢Where Id in 列表的值 
declare @a varchar ( 100 )
set @a = '56,58'
-- 正確的： 
select * from tb where charindex ( ',' + ltrim (id) + ',' , ',' + @a + ',' ) > 0
-- 錯誤的： 
select * from tb where id in + @a
2、找出與某id相近的四條記錄
Create table tb(id int ,cName char ( 10 ))
DECLARE @ID INT
SET @ID = 7
SELECT * FROM TB A WHERE id in
( SELECT TOP 4 id FROM TB ORDER BY ABS (id - @id ))
ORDER BY ID 
3、按名稱，規格分組，將單價資料合併成一行，並計算數量
Create table [tb] (tName varchar(4), [tSize] varchar (7), [ tPrice ] int , [ tQty ] int )
insert [ tb ]
select '高瓦' , '880*110'  , 22 , 1 union all select '高瓦' , '880*110' , 25 , 1 union all
select '高瓦' , '880*110' , 22 , 1 union all select '高瓦' , '880*120' , 22 , 1
select   tName, tSize,
  tPrice = stuff (( select ',' + ltrim (tPrice) from tb  
where tName = t.tName and tSize = t.tSize FOR XML PATH('')),1,1,''), sum(tQty) as tQty
from   tb t group by   tName, tSize 
---結果----------------------------- 
tName   tSize     tPrice     tQty
高瓦    880*110 22,25,22 3
高瓦    880*120 22 1
4、根據出生日期,計算出準確的年齡 
SET @A = '2008-08-12'
DECLARE @A DATETIME
SELECT 年齡 =
case when datediff(day,dateadd(year,datediff(year,@A,getdate()),@A), getdate())>= 0
then datediff(year,@A,getdate()) else datediff(YY,@A,getdate())- 1 end
5、找出某目錄列表中所有下級目錄，包括自己

代碼

DECLARE @FolderList varchar ( 800 )
SET @FolderList = '1'
SET NOCOUNT ON
CREATE TABLE # Temp (FolderId int )    
INSERT # Temp
SELECT FolderId FROM Doc_Folder
WHERE CHARINDEX ( ',' + LTRIM (FolderId) + ',' , ',' + @FolderList + ',' ) > 0
WHILE @@Rowcount > 0
BEGIN
INSERT # Temp SELECT FolderId FROM Doc_Folder AS A WHERE
EXISTS ( SELECT 1 FROM # Temp AS B WHERE B. [ FolderId ] = A.ParentFolderId)
AND NOT EXISTS(SELECT 1 FROM # Temp AS B WHERE B. [FolderId] = A.[FolderId])
END

6、簡單靜態資料指標

代碼

DECLARE product_cursor CURSOR STATIC FOR
SELECT cName FROM Product 
OPEN product_cursor
FETCH NEXT FROM product_cursor INTO @product
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @message = ' ' + @product
PRINT @message
FETCH NEXT FROM product_cursor INTO @product
END
CLOSE product_cursor
DEALLOCATE product_cursor

7、要求是取得每個ID對應postId的前三條

代碼

CREATE TABLE [tb] (Id INT ,postId INT )
INSERT INTO [tb]
SELECT 2788 , 45753530 UNION ALL
SELECT 6417 , 46862065 UNION ALL
SELECT 61773 , 47407456 UNION ALL
SELECT 61773 , 47436468 UNION ALL
SELECT 61773 , 47448259 UNION ALL
SELECT 61773 , 47474393 UNION ALL
SELECT 83604 , 41671947 UNION ALL
SELECT 83604 , 45858681 UNION ALL
select id, postid from ( select * ,cid = row_number() over (partition by id order by id) from tb ) as t
where t.cid <= 3

8、實現編號自動成長

代碼

--下面的代碼產生長度為8的編號，編號以BH開頭，其餘6位為流水號。
-- 得到新編號的函數 
CREATE FUNCTION f_NextBH()
RETURNS char ( 8 )
AS
BEGIN
RETURN (SELECT 'BH' +RIGHT(1000001 + ISNULL(RIGHT(MAX(BH),6),0),6) FROM tb WITH(XLOCK,PAGLOCK))
END
GO
-- 在表中應用函數 
CREATE TABLE tb(
BH char (8) PRIMARY KEY DEFAULT dbo.f_NextBH(),col int)
-- 插入資料 
BEGIN TRAN
INSERT tb(col) VALUES ( 1 )
INSERT tb(col) VALUES ( 2 )
INSERT tb(col) VALUES ( 4 )
INSERT tb(BH,col) VALUES (dbo.f_NextBH(), 14 )
COMMIT TRAN

9、表Age_AssignedNum中的欄位CallRecordId，其值有三種：K、N、null,
   按PlanId分組統計出這三種值各有多少行

代碼

SELECT PlanId,sK=count(CASE CallRecordId WHEN 'K' then CallRecordId end),
    sN=count(CASE CallRecordId WHEN 'N' then CallRecordId end),
    sNull=count( CASE isnull(CallRecordId,'') WHEN '' then '1' end)
FROM Age_AssignedNum GROUP BY PlanId