Delete duplicate records and save TABLE_NAME Records in #table_name
SELECT DISTINCT * INTO #table_name table_name
Delete from table_name
SELECT * INTO table_name from #table_name
drop table #table_name
Related to this is the "SELECT INTO" option, which can be used in database properties
dialog box, either hook up this item, or execute in Query Analyzer
Execute sp_dboption ' db_name ', ' SELECT INTO ', ' true '
Open. The default value is off.
*******************************************************
Shrink transaction log (multiple execution)
Backup LOG Register with NO_LOG
Backup LOG Register with TRUNCATE_ONLY
DBCC shrinkdatabase (Register)
More useful SQL statements
/*sql Grammar Learning * *
/* Function of learning---------------------------------------*/
Get current time (Time/minute/sec): Select CONVERT (varchar), GETDATE (), 8)
Get Current Date: Select CONVERT (varchar), GETDATE (), 120)
Get Current Date: Select CONVERT (varchar (7), GETDATE (), 120)
Get Current Date: Select CONVERT (varchar), year (GETDATE ()) + '-' + convert (varchar), month (GETDATE ())
Select cast (b as Integer) as BB from table1 where B = ' 11 '
Select A,case B when ' one ' then ' thin ' when ' then ' ha ' ah ' else ' ha ' end as conversion, C from table1
Select a,b,case when c = ' Then ' ' thin ' when C = ' 222 ' Then ' hehe ' else ' ha ' end as conversion 1 from table1
Get the current time: print Current_timestamp
/*－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－*/
-----------------output The SQL query to the TXT text file-------------------------------------------
EXEC Master.. xp_cmdshell ' bcp database name. dbo. Table name out d:\1.txt-c-q-u "sa"-P "password"
---------------------------------------------------------------------------------------
The usage of---------------------------round beigin------------------------------
DECLARE @s float
Set @s = 0.1566134
Print round (@s,3)
---------------------------The usage end of the round---------------------------------
--------------------------------automatically shrink the database begin-----------------------------
EXEC [Master]. sp_dboption [Database Name], ' autoshrink ', ' TRUE '
--------------------------------automatically shrink the database end-----------------------------
-------------------------------Remove the end of the 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 the end of an invalid character end--------------------------
------------delete duplicate records in the database (and keep only one valid record) sample-----------------
CREATE TABLE A
(
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 A
--method One
Delete from a where userid to (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 to (select min (userid) from a b where a.username = B.username and a.userpwd = B.userpwd and A.userid > B.userid)
SELECT * FROM A
drop table A
------------delete duplicate records in the database (and keep only one valid record) sample-----------------
-------------------------------the application of the iteration (find the path between the starting point and the endpoint-----------------------------
CREATE TABLE T
(St varchar (), Ed varchar, 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 Insert value
SELECT * FROM t
Go
--Create a function
--function returns a table, according to the actual situation of different layers of inserts, you can make full use of the resulting table
Create function F_go (@col varchar (10))
Returns @t table (col varchar, st varchar (), Ed varchar, 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 Environment
Drop function F_go
drop table T
-------------------------------the application of the iteration (find the path between the starting point and the endpoint-----------------------------
--------go to the newest top N records by category, put the same class together, count the number of items of the same class, and so on-------------
CREATE TABLE T
(
ClassName varchar (50),
Classcode varchar (10),
ClassID int identity (1,1)
)
INSERT INTO T
Select ' Cccc1 ', ' 002 ' UNION ALL
Select ' AAAA ', ' 001 ' UNION ALL
Select ' bbbb ', ' 001 ' UNION ALL
Select ' Aaaa1 ', ' 002 ' UNION ALL
Select ' CCCC ', ' 001 ' UNION ALL
Select ' dddd ', ' 001 ' UNION ALL
Select ' Bbbb1 ', ' 002 ' UNION ALL
Select ' Dddd1 ', ' 002 '
SELECT * FROM t
Select Classcode = 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 the 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-classcode,classid Desc) a GROUP by Classcode
Select Classcode,classname from T-classcode,classid desc
drop table T
--------go to the newest top N records by category, put the same class together, count the number of items of the same class, and so on-------------
-------------Ibid, statistics by category, add the other content of the same class of items together in a field------------------
CREATE TABLE TB (ProductID varchar), PositionID varchar (10))
INSERT INTO TB
Select ' 10001 ', ' A1 '
UNION ALL SELECT ' 10001 ', ' B2 '
UNION ALL SELECT ' 10002 ', ' C3 '
UNION ALL SELECT ' 10002 ', ' D4 '
UNION ALL SELECT ' 10002 ', ' 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 () 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
-------------statistics by category, adding other content of items of the same class to a field------------------
--Take the first n records of each class (each class takes top N bars)
--if there are multiple classes in the database, now you want to take the first n records of each class, you can use the following statement
Create Table TEST
(ID Int Identity (1,1),
h_id Int)
Insert TEST Select 100
Union All Select 100
Union All Select 100
Union All Select 101
Union All Select 101
Union All Select 101
Union All Select 100
Go
--Method One:
SELECT * FROM Test A where Id in (select top 3 ID from test where h_id=a.h_id)
--Method Two:
SELECT * FROM Test A where not Exists (select 1 from Test where h_id=a.h_id and id<a.id have Count (*) >2)
--Method Three:
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
--grouped statistics to count the number of data in each segment
--general performance statistics can be used in this
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 the group by (weight/@p ">wei") GHT/@P)) A
----------------------------Use custom sort begin--------------------------------in the in statement
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
--SQL is sorted by ID in the default in statement
SELECT * from @t where ID in (2,4,3)
--Use this method to display data in the order of IDs we passed in
SELECT * from @t where ID in (2,4,3) Order by CHARINDEX (RTrim (ID), ', 2,4,3, ')
----------------------------Use custom sort end only in the in statement--------------------------------
