Useful SQL statements (deleting duplicate records and shrinking logs)

Delete duplicate records and save the non-Repeated Records in table_name to # table_name

Select distinct * into # table_name from table_name
Delete from table_name
Select * into table_name from # table_name
Drop table # table_name

This is related to the "select into" option, which can be in the database attribute
In the dialog box, check this item, or execute it in query analyzer.
Execute sp_dboption 'db _ name', 'select into', 'true'
Enable. The default value is disabled.

**************************************** ***************
Shrink transaction logs (multiple executions)

Backup log register with no_log
Backup log register with truncate_only
DBCC shrinkdatabase (Register)

More useful SQL statements
/* SQL syntax learning */

/* Function learning ---------------------------------------*/

Obtain the current time (hour/minute/second): Select convert (varchar (10), getdate (), 8)
Obtain the current year, month, and day: Select convert (varchar (10), getdate (), 120)
Obtain the current year and month: Select convert (varchar (7), getdate (), 120)
Obtain the current year and month: Select convert (varchar (10), Year (getdate () + '-' + convert (varchar (10), month (getdate ()))

Select cast (B as integer) as BB from Table1 where B = '11'

Select a, Case B when '11' then' detailed 'when' 22 'then', 'else', 'hangzhou' end as conversion, C from Table1

Select a, B, case when C = '000000' then' detail 'when c = '000000' then' Haha 'else' Haha 'end as conversion 1 from Table1

Get current time: Print current_timestamp

/*---------------------------------------------*/

----------------- Output the SQL query to the TXT text file -------------------------------------------
Exec master.. xp_mongoshell 'bcp database name. DBO. Table name out d: \ 1.txt-C-Q-U "sa"-P "password "'

Bytes ---------------------------------------------------------------------------------------

--------------------------- Usage of round beigin ------------------------------
Declare @ s float
Set @ s = 0.1566134
Print round (@ s, 3)
--------------------------- Round usage end ---------------------------------

------------------------------ Automatically contract the database begin -----------------------------

Exec [Master] .. sp_dboption [database name], 'autoshrink ', 'true'

-------------------------------- Automatically contract the Database End -----------------------------

------------------------------- Remove invalid character begin --------------------------
Declare @ s varchar (20)
Set @ s = ', 1-> 1 ,'
While (left (@ s, 1) = ',')
Set @ s = stuff (@ s, 1, 1 ,'')
While (right (@ s, 1) = ',')
Set @ s = stuff (reverse (@ s), 1, 1 ,'')
Select @ s
------------------------------- Remove invalid characters at the beginning and end --------------------------

------------ Example of deleting a duplicate record in a database (and retaining only one valid record -----------------
Create Table
(
Userid int identity (1, 1 ),
Username varchar (20 ),
Userpwd varchar (20 ),
Useremail varchar (50)
)
Insert into a (username, userpwd) Select 'qin', 'qin' Union all select 'qin', 'qin1' Union all select 'qin', 'qin1'
Select * from

-- Method One
Delete from a where userid not in (select Min (userid) as userid from a group by username, userpwd)

-- Method two
Delete from a where exists (select * from a B where a. Username = B. username and A. userpwd = B. userpwd and A. userid <B. userid)

-- Method three
Delete from a where userid not in (select Min (userid) from a B where. username = B. username and. userpwd = B. userpwd and. userid> B. userid)

Select * from
Drop Table

------------ Example of deleting a duplicate record in a database (and retaining only one valid record -----------------

------------------------------- duplicate application (find the path between the start and end -----------------------------
Create Table T
(ST varchar (20), ED varchar (20 ), km int)
go
insert T values ('A', 'B', 1000)
insert T values ('A', 'C ', 1100)
insert T values ('A', 'D', 900)
insert T values ('A', 'E', 400)
insert T values ('B', 'D ', 300)
insert T values ('D', 'F', 600)
insert T values ('E', 'A', 400)
insert T values ('F', 'G', 1000)
insert T values ('C', 'B', 600)
go
-- display the insert value
select * from T
go

-- Create a function
-- The function returns a table, which is inserted at different layers according to the actual situation. The generated table can be fully utilized.
Create Function f_go (@ Col varchar (10 ))
Returns @ t table (COL varchar (30), St varchar (20), ED varchar (20), KM int, level INT)
As
Begin
Declare @ I int
Set @ I = 1
Insert @ t select st + '-' + ed, *, @ I from t where St = @ col
While exists (select * from t a, @ t B where
B. Ed = A. st and B. Level = @ I and B. Ed <> @ col)
Begin
Set @ I = @ I + 1
Insert @ t
Select B. Col + '-' + A. Ed, A. St, A. Ed, B. km + A. km, @ I from t a, @ T B
Where B. Level = @ I-1 and B. Ed = A. st and B. Ed <> @ col
End
Return
End
Go

-- Call
-- Select * From DBO. f_go ('A ')
Select Col, km from DBO. f_go ('A ')

-- Delete the environment
Drop function f_go
Drop table t

----------------------------- Application of the same origin (find the path between the start and end -----------------------------

-------- Sort the latest first N records by category, put the same class together, and count the number of items in the same class -------------
Create Table t
(
Classname varchar (50 ),
Classcode varchar (10 ),
Classid int identity (1, 1)
)
Insert into t
Select 'cccccc1', '002' Union all
Select 'aaa', '001' Union all
Select 'bbbbb', '001' Union all
Select 'aaaaa1', '002 'Union all
Select 'cccc', '001' Union all
Select 'ddddd', '001' Union all
Select 'bbbb1', '002' Union all
Select 'dddd1', '002'
Select * from t
Select classcode = (case when exists (select 1 from t T1 where classcode = t1.classcode
And classid <t1.classid)
Then ''else classcode end), classname from t order by classcode, classid DESC

Select count (*), classcode from (select Top 100 percent classcode = (case when exists (select 1 from t T1 where classcode = t1.classcode
And classid <t1.classid)
Then ''else classcode end), classname from t order by classcode, classid DESC) a group by classcode

Select classcode, classname from t order by classcode, classid DESC
Drop table t

-------- Sort the latest first N records by category, put the same class together, and count the number of items in the same class -------------

------------- Same as above, calculate by category, add the other content of the same category and concurrently add it to a field ------------------
Create Table Tb (productid varchar (10), positionid varchar (10 ))
Insert into TB
Select '200', 'a1'
Union all select '200', 'b2'
Union all select '200', 'c3'
Union all select '20180101', 'd4'
Union all select '000000', 'e5'
Go

Create Function DBO. fc_str (@ productid varchar (10 ))
Returns varchar (100)
As
Begin
Declare @ SQL varchar (1000)
Set @ SQL =''
Select @ SQL = @ SQL + ',' + Cast (positionid as varchar (20) from TB where productid = @ productid
Return stuff (@ SQL, 1, 1 ,'')
End
Go

Select productid, DBO. fc_str (productid) as positionid from TB group by productid

Drop table TB

Drop function DBO. fc_str

------------- Calculate by category, add the other content of the same class, and concurrently add it to a field ------------------

-- Obtain the first N records of each class (Top N records for each class)
-- If there are multiple classes in the database, you need to retrieve the first N records of each class. The following statements can be used:
Create Table Test
(ID int identity (1, 1 ),
H_id INT)
"Insert test select 100"
Join all select 100
Join all select 100
Join all select 101
Join all select 101
Join all select 101
Join all select 100
Go
-- Method 1:
Select * from test a where ID in (select top 3 ID from test where h_id = A. h_id)
-- Method 2:
Select * from test a where not exists (select 1 from test where h_id = A. h_id and ID <A. ID having count (*)> 2)
-- Method 3:
Select * from test a where (select count (*) from test where h_id = A. h_id and ID <A. ID) <3
Go
Drop Table Test
Go

-- Group statistics: counts the number of data in each segment.
-- This is applicable to general score statistics.
Declare @ t table (ID int, weight INT)
Insert into @ t select 1, 20
Insert into @ t select 2, 15
Insert into @ t select 3, 5
Insert into @ t select 4, 60
Insert into @ t select 5, 12
Insert into @ t select 6, 33
Insert into @ t select 7, 45
Insert into @ t Select 8, 59
Insert into @ t select 9, 89
Insert into @ t select 10,110

Declare @ P int
Set @ P = 10
Select
Rtrim (p * @ p) + '-' + rtrim (p + 1) * @ P "> P * @ P) + '-' + rtrim (p + 1) * @ p) as P,
Num
From
(Select (weight/@ P "> weight/@ p) as P, count (*) as num from @ t where weight between 10 and 100 group by (weight/@ P "> weight/@ p)

-------------------------- In the in statement, you can only use the custom sorting begin --------------------------------
Declare @ t table (ID int, weight INT)
Insert into @ t select 1, 20
Insert into @ t select 2, 15
Insert into @ t select 3, 5
Insert into @ t select 4, 60
Insert into @ t select 5, 12
Insert into @ t select 6, 33
Insert into @ t select 7, 45
Insert into @ t Select 8, 59
Insert into @ t select 9, 89
Insert into @ t select 10,110
-- The SQL statements in the in statement are sorted by ID by default.
Select * From @ t where ID in (2, 4, 3)
-- This method can be used to display data in the order of input IDs.
Select * From @ t where ID in (2, 4, 3) Order by charindex (rtrim (ID), ', 2, 4, 3 ,')

-------------------------- In the in statement, only use the custom sorting end --------------------------------