One of the typical examples of SQL Server

Carriage returns and line breaks in sqlserver
Tab: Char (9) linefeed: Char (10) carriage return: Char (13)
1. Use the varchar value as a condition to query the value of the where ID in list
Declare @ A varchar (100)
Set @ A = '56, 58'
-- Correct:
Select * from TB where charindex (',' + ltrim (ID) + ',' + @ A + ',')> 0
-- Incorrect:
Select * from TB where ID in + @
2. Find four records that are similar to a specific 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. Group by name and type, merge unit price data into one row, and calculate the quantity.
Create Table [TB] (tname varchar (4), [tsize] varchar (7), [tprice] int, [tqty] INT)
Insert [TB]
Select 'var', '2014*880 ', 22, 1 Union all select 'var', '2014*110', 25, 1 Union all
Select 'var', '2014*880 ', 22, 1 Union all select 'var', '2014*110', 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
--- Result -----------------------------
Tname tsize tprice tqty
Gaowa 880*110, 22 3
Gaowa 880*120 22 1
4. Calculate the accurate age based on the date of birth.
Set @ A = '2017-08-12'
Declare @ A datetime
Select age =
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. Find all the sub-directories in a directory list, including

Code

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. simple static cursor

Code

Declare product_cursor cursor static
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. The requirement is to obtain the first three

Code

Create Table [TB] (ID int, postid INT)
Insert into [TB]
Select 2788,457 53530 Union all
Select 6417,468 Union all
Select 61773,474 07456 Union all
Select 61773,474 36468 Union all
Select 61773,474 48259 Union all
Select 61773,474 74393 Union all
Select 83604,416 71947 Union all
Select 83604,458 58681 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. automatic increase of numbers

Code

-- The followingCodeGenerate an 8-digit serial number. The serial number starts with BH and the remaining six digits are serial numbers.
-- Get the function with the new number
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
-- Apply a function to a table
Create Table Tb (
BH char (8) primary key default DBO. f_nextbh (), Col INT)
-- Insert data
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. The callrecordid field in the age_assignednum table has three types of values: k, n, and null,
Group by planid to calculate the number of rows for each of the three values

Code

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