SQL开发实用经典例句

• Posted by Admin on 十二月 15, 2009
• 0 comments

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 83604 , 45887599 UNION ALL

SELECT 83604 , 45917692 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

http://www.cnblogs.com/fredx/archive/2009/12/12/1622616.html