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