Common SQL favorites

MSSQL Split table Field

-- Split the string and match the result set create function [dbo]. [fnSplit] (@ sInputList VARCHAR (8000) -- List of delimited items, @ sDelimiter VARCHAR (8000) = ',' -- delimiter that separates items) RETURNS @ List TABLE (item VARCHAR (8000) BEGINDECLARE @ 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 endif len (@ sInputList)> 0 insert into @ List SELECT @ sInputList -- Put the last item inRETURNEND -------------------- use method if object_id ('tempdb .. # Tmp ') is not null drop table # Tmpcreate table # Tmp -- create a temporary table # Tmp (id varchar (100); DECLARE @ iid VARCHAR (100) declare @ name varchar (500) declare cursor1 cursor for -- Define the cursor cursor1select iid, props from Iteminfos -- use the cursor object open cursor1 -- open the cursor fetch next from cursor1 into @ iid, @ name -- move the cursor down one line and put the obtained data into the defined variable @ iid, @ name while @ fetch_status = 0 -- determine whether data is successfully obtained beginIF (select COUNT (*) FROM fnSplit (@ name, ';') WHERE item = '2017: 20090 ')> 0) insert into # Tmp (ID) VALUES (@ iid) fetch next from cursor1 into @ iid, @ name -- move the cursor down 1 row endclose cursor1 -- close the cursor deallocate cursor1SELECT * FROM dbo. iteminfos WHERE iid IN (select id from # Tmp)

Query node functions

Create table tb (id varchar (3), pid varchar (3), name varchar (10) insert into tb values ('001', null, 'guangdong province ') insert into tb values ('002 ', '001', 'guangzhou ') insert into tb values ('003', '001', 'shenzhen ') insert into tb values ('004 ', '002', 'tianhe district ') insert into tb values ('005', '003 ', 'luohu district ') insert into tb values ('006 ', '003', 'futian district ') insert into tb values ('007', '003 ', 'baoan district ') insert into tb values ('008 ', '007', 'West township ') insert into tb values ('009', '007', 'longhua Zhen ') insert into tb values ('010 ', '007', 'songgang Zhen ') go ----------- create function f_cid (@ ID varchar (3 )) returns @ t_level table (id varchar (3), level int) asbegindeclare @ level intset @ level = 1 insert into @ t_level select @ id, @ levelwhile @ ROWCOUNT> 0 beginset @ level = @ level + 1 insert into @ t_level select. id, @ levelfrom tb a, @ t_Level bwhere. pid = B. id and B. level = @ level-1endreturnENDGO ------------ use method select. * from tb a, f_cid ('001') B where. id = B. id order by. id -------------- call function query (Guangzhou City) and all its subnodes select. * from tb a, f_cid ('002 ') B where. id = B. id order by. id

Exists, Datediff, Newid,

--- Two associated tables: delete the information that is not in the sub-table in the master table. delete from table1 where not exists (select * from table2 where table1.field1 = table2.field1) --- schedule five minutes in advance to remind SQL: select * from Schedule where datediff ('minute ', f Start Time, getdate ()> 5 --- randomly retrieve 10 pieces of data select top 10 * from tablename order by newid () -- similar to month day yearselect * from table1 where convert (varchar, date, 120) like '2017-04-2006 '-- datediffselect * from table1 where datediff (day, time, '2014-4-1 ') = 0

Delete duplicate values

-- 1. Search for redundant duplicate records in the Table. duplicate records are based on a single field (peopleId) select * from people where peopleId in (select peopleId from people group by peopleId having count (peopleId)> 1) -- 2. Delete unnecessary duplicate records in the table, repeat records are determined based on a single field (peopleId). Only the records with the smallest rowid are retained: delete from people where peopleId in (select peopleId from people group by leleid having count (peopleId)> 1) and rowid not in (select min (rowid) from people group by peopleId having count (peopleId)> 1) -- 3. Search for redundant duplicate records in the table (multiple fields) select * from vitae a where (. peopleId,. seq) in (select peopleId, seq from vitae group by peopleId, seq having count (*)> 1) -- 4. Delete unnecessary duplicate records (multiple fields) in the table ), delete from vitae a where (. peopleId,. 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. Search for redundant duplicate records (multiple fields) in the table, excluding the select * from vitae a where (. peopleId,. 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) -- classic attempts to delete duplicate values declare @ table (id int, name nvarchar (10) insert into @ table select 1, 'A' union all select 1, 'A' union all select 2, 'bb' union all select 3, 'bb' union all select 4, 'cc' union all select 1, 'A' union all select 4, 'cc' delete afrom (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 aa2 bb3 bb4 cc (4 row (s) affected)

Common date conversion Parameters

Select CONVERT (varchar, getdate (), 120) -- result 2004-09-12 11: 06: 08 select replace (CONVERT (varchar, getdate (), 120 ), '-', ''),'', ''), ':','') -- result 20040912110608 select CONVERT (varchar (12), getdate (), 111) -- result 2004/09/12 select CONVERT (varchar (12), getdate (), 112) -- result 20040912 select CONVERT (varchar (12), getdate (), 102) -- result 2004.09.12

Row-to-Column

Create table tb (name varchar (10), course varchar (10), score int) insert into tb values ('zhang san', 'China', 74) insert into tb values ('zhang san', 'mat', 83) insert into tb values ('zhang san', 'Physical ', 93) insert into tb values ('Li si ', 'China', 74) insert into tb values ('lily', 'mat', 84) insert into tb values ('lily', 'Physical ', 94) goselect name as name, max (case course when 'chine' then score else 0 end) language, max (case course when 'mate' then score else 0 end) mathematics, max (c Ase course when 'physical 'then score else 0 end) Physical, cast (avg (score * 1.0) as decimal () average score, sum (score) total from tbgroup by name -- SQL server 2000 dynamic SQL. Declare @ SQL varchar (8000) set @ SQL = 'select name' select @ SQL = @ SQL + ', max (case course when''' + course + ''' then score else 0 end) ['+ course +'] 'from (select distinct course from tb) as aset @ SQL = @ SQL + ', cast (avg (score * 1.0) as decimal () average score, sum (score) total score from tb group by name 'exec (@ SQL) -- average score of name, mathematics, physics, and Chinese -- Li Si 84947484.00252 -- Zhang San 83937483.33250

Obtain all parent nodes by using the child node ID

ALTER function [dbo].[f_cid](@id int)returns @t table(id int,[name] varchar(30),parentid int,lev int)asbegin     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

123

3232

123

3232

123

3232

123

3232

123

3232

123

3232

123

3232

123

3232

123

3232

123

3232