原文: 常用SQL收藏
MSSQL Split表字段
--拆分字元串之後比對結果集合
CREATE FUNCTION [dbo].[fnSplit](
@sInputList VARCHAR(8000) -- List of delimited items
, @sDelimiter VARCHAR(8000) = ',' -- delimiter that separates items
) RETURNS @List TABLE (item VARCHAR(8000))
BEGIN
DECLARE @sItem VARCHAR(8000)
WHILE CHARINDEX(@sDelimiter,@sInputList,0) <> 0
BEGIN
SELECT
@sItem=RTRIM(LTRIM(SUBSTRING(@sInputList,1,CHARINDEX(@sDelimiter,@sInputList,0)-1))),
@sInputList=RTRIM(LTRIM(SUBSTRING(@sInputList,CHARINDEX(@sDelimiter,@sInputList,0)+LEN(@sDelimiter),LEN(@sInputList))))
IF LEN(@sItem) > 0
INSERT INTO @List SELECT @sItem
END
IF LEN(@sInputList) > 0
INSERT INTO @List SELECT @sInputList -- Put the last item in
RETURN
END
----------------------使用方法
if object_id('tempdb..#Tmp') is not null
drop table #Tmp
create table #Tmp --建立臨時表#Tmp
(
ID VARCHAR(100)
);
DECLARE @iid VARCHAR(100)
declare @name varchar(500)
declare cursor1 cursor for --定義遊标cursor1
select iid,props from Iteminfos --使用遊标的對象
open cursor1 --打開遊标
fetch next from cursor1 into @iid,@name --将遊标向下移1行，擷取的資料放入之前定義的變量@iid,@name中
while @@fetch_status=0 --判斷是否成功擷取資料
begin
IF((select COUNT(*) FROM fnSplit(@name, ';') WHERE item = '20000:20090')>0)
INSERT INTO #Tmp (ID) VALUES (@iid)
fetch next from cursor1 into @iid,@name --将遊标向下移1行
end
close cursor1 --關閉遊标
deallocate cursor1
SELECT * FROM dbo.Iteminfos WHERE iid IN( SELECT ID FROM #Tmp)
查詢節點的函數
create table tb(id varchar(3) , pid varchar(3) , name varchar(10))
insert into tb values('001' , null , '廣東省')
insert into tb values('002' , '001' , '廣州市')
insert into tb values('003' , '001' , '深圳市')
insert into tb values('004' , '002' , '天河區')
insert into tb values('005' , '003' , '羅湖區')
insert into tb values('006' , '003' , '福田區')
insert into tb values('007' , '003' , '寶安區')
insert into tb values('008' , '007' , '西鄉鎮')
insert into tb values('009' , '007' , '龍華鎮')
insert into tb values('010' , '007' , '松崗鎮')
go
-------------建立方法
create function f_cid(@ID varchar(3)) returns @t_level table(id varchar(3) , level int)
as
begin
declare @level int
set @level = 1
insert into @t_level select @id , @level
while @@ROWCOUNT > 0
begin
set @level = @level + 1
insert into @t_level select a.id , @level
from tb a , @t_Level b
where a.pid = b.id and b.level = @level - 1
end
return
END
GO
--------------使用方法
select a.* from tb a , f_cid('001') b where a.id = b.id order by a.id
--------------調用函數查詢(廣州市)及其所有子節點
select a.* from tb a , f_cid('002') b where a.id = b.id order by a.id
Exists,Datediff,Newid,
---兩張關聯表，删除主表中已經在副表中沒有的資訊
delete from table1 where not exists ( select * from table2 where table1.field1=table2.field1 )
---日程安排提前五分鐘提醒
SQL: select * from 日程安排 where datediff('minute',f開始時間,getdate())>5
---随機取出10條資料
select top 10 * from tablename order by newid()
-- 類似有 month day year
select * from table1 where convert(varchar,date,120) like '2006-04-01%'
--datediff
select * from table1 where datediff(day,time,'2006-4-1')=0 删除重複值
--1、查找表中多餘的重複記錄，重複記錄是根據單個字段（peopleId）來判斷
select * from people
where peopleId in (select peopleId from people group by peopleId having count(peopleId) > 1)
--2、删除表中多餘的重複記錄，重複記錄是根據單個字段（peopleId）來判斷，隻留有rowid最小的記錄
delete from people
where peopleId in (select peopleId from people group by peopleId having count(peopleId) > 1)
and rowid not in (select min(rowid) from people group by peopleId having count(peopleId )>1)
--3、查找表中多餘的重複記錄（多個字段）
select * from vitae a
where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1)
--4、删除表中多餘的重複記錄（多個字段），隻留有rowid最小的記錄
delete from vitae a
where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1)
and rowid not in (select min(rowid) from vitae group by peopleId,seq having count(*)>1)
--5、查找表中多餘的重複記錄（多個字段），不包含rowid最小的記錄
select * from vitae a
where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1)
and rowid not in (select min(rowid) from vitae group by peopleId,seq having count(*)>1)
--經典嘗試 删除重複值
declare @table table (id int,name nvarchar(10))
insert into @table select 1,'aa'
union all select 1,'aa'
union all select 2,'bb'
union all select 3,'bb'
union all select 4,'cc'
union all select 1,'aa'
union all select 4,'cc'
delete a
from (
select id,name,rn = row_number() over (partition by id,name order by id) from @table
) a where rn > 1
select * from @table
id name
----------- ----------
1 aa
2 bb
3 bb
4 cc
(4 row(s) affected)
常用日期轉換參數
select CONVERT(varchar, getdate(), 120 )
--結果
2004-09-12 11:06:08
select replace(replace(replace(CONVERT(varchar, getdate(), 120 ),'-',''),' ',''),':','')
--結果
20040912110608
select CONVERT(varchar(12) , getdate(), 111 )
--結果
2004/09/12
select CONVERT(varchar(12) , getdate(), 112 )
--結果
20040912
select CONVERT(varchar(12) , getdate(), 102 )
--結果
2004.09.12 行轉列
create table tb(姓名varchar(10) , 課程varchar(10) , 分數int)
insert into tb values('張三' , '國文' , 74)
insert into tb values('張三' , '數學' , 83)
insert into tb values('張三' , '實體' , 93)
insert into tb values('李四' , '國文' , 74)
insert into tb values('李四' , '數學' , 84)
insert into tb values('李四' , '實體' , 94)
go
select 姓名 as 姓名,
max(case 課程 when '國文' then 分數 else 0 end) 國文,
max(case 課程 when '數學' then 分數 else 0 end) 數學,
max(case 課程 when '實體' then 分數 else 0 end) 實體,
cast(avg(分數*1.0) as decimal(18,2)) 平均分,
sum(分數) 總分
from tb
group by 姓名
--SQL SERVER 2000 動态SQL。
declare @sql varchar(8000)
set @sql = 'select 姓名 '
select @sql = @sql + ' , max(case 課程 when ''' + 課程 + ''' then 分數 else 0 end) [' + 課程 + ']'
from (select distinct 課程 from tb) as a
set @sql = @sql + ' , cast(avg(分數*1.0) as decimal(18,2)) 平均分, sum(分數) 總分 from tb group by 姓名'
exec(@sql)
--姓名 數學 實體 國文 平均分 總分
--李四 84 94 74 84.00 252
--張三 83 93 74 83.33 250 通過子節點ID得到所有父節點
ALTER function [dbo].[f_cid](@id int)
returns @t table(id int,[name] varchar(30),parentid int,lev int)
as
begin
declare @lev int
set @lev=1
insert into @t SELECT cid,name,parent_cid,@lev from TB_ItemCats where cid=@id
while(@@rowcount>0)
begin
set @lev=@lev+1
insert into @t select a.cid,a.name,a.parent_cid,@lev from TB_ItemCats a,@t b
where a.cid=b.parentid and b.lev=@lev-1 AND a.cid NOT IN (select b.id from @t)
end
return
END
123
3232
![阿裡雲天池龍珠計劃SQL訓練營day1[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)