Exquisite SQL statements from winner's blog)

Note: copy a table (only copy structure, source table name: a new table name: B)
Select * into B from a where 1 <> 1

Description: copy a table (copy data, source table name: A target table name: B)
Insert into B (a, B, c) Select D, E, F from B;

Note: DisplayArticle, Submitted and last reply time
Select a. Title, A. username, B. adddate from Table A, (select max (adddate) adddate from table where table. Title = A. Title) B

Description: External join query (table name 1: Table A name 2: B)
Select a. a, a. B, A. C, B. C, B. D, B. F from a left out join B on A. A = B. C

Note: Five minutes in advance of the schedule
Select * from Schedule where datediff ('minute ', F Start Time, getdate ()> 5

Note: Two associated tables are used to delete information that is already unavailable to the primary table in the secondary table.
Delete from info where not exists (select * From infobz where info. INFID = infobz. INFID)

Note :--

SQL:

Select a. Num, A. Name, B. upd_date, B. prev_upd_date

From Table1,

(Select X. Num, X. upd_date, Y. upd_date prev_upd_date

From (select num, upd_date, inbound_qty, stock_onhand

From Table2

Where to_char (upd_date, 'yyyy/mm') = to_char (sysdate, 'yyyy/mm') X,

(Select num, upd_date, stock_onhand

From Table2

Where to_char (upd_date, 'yyyy/mm') =

To_char (to_date (to_char (sysdate, 'yyyy/mm') | '/01', 'yyyy/MM/dd')-1, 'yyyy/mm ')) y,

Where X. num = Y. Num (+)

And X. inbound_qty + nvl (Y. stock_onhand, 0) <> X. stock_onhand) B

Where a. num = B. Num

Note :--
Select * From studentinfo where not exists (select * from student where studentinfo. id = student. ID) and Department name = '"& strdepartmentname &"' and major name = '"& strinclusionname &" 'order by gender, student source, total score of the College Entrance Examination

Go to the telephone fee statistics of each unit from the database for one year (two sources of the fixed telephone fee congratulation fertilizer List)

Select a. userper, A. Tel, A. standalone, to_char (A. telfeedate, 'yyyy') as telyear,

Sum (decode (to_char (A. telfeedate, 'mm'), '01', A. factration) as Jan,

Sum (decode (to_char (A. telfeedate, 'mm'), '02', A. factration) as Fri,

Sum (decode (to_char (A. telfeedate, 'mm'), '03', A. factration) as MAR,

Sum (decode (to_char (A. telfeedate, 'mm'), '04 ', A. factration) as Apr,

Sum (decode (to_char (A. telfeedate, 'mm'), '05 ', A. factration) as may,

Sum (decode (to_char (A. telfeedate, 'mm'), '06', A. factration) as Jue,

Sum (decode (to_char (A. telfeedate, 'mm'), '07 ', A. factration) as Jul,

Sum (decode (to_char (A. telfeedate, 'mm'), '08 ', A. factration) as Agu,

Sum (decode (to_char (A. telfeedate, 'mm'), '09', A. factration) as SEP,

Sum (decode (to_char (A. telfeedate, 'mm'), '10', A. factration) as Oct,

Sum (decode (to_char (A. telfeedate, 'mm'), '11', A. factration) as Nov,

Sum (decode (to_char (A. telfeedate, 'mm'), '12', A. factration) as Dec

From (select a. userper, A. Tel, A. standalone, B. telfeedate, B. factration

From telfeestand A, telstmb

Where a. Tel = B. telfax)

Group by A. userper, A. Tel, A. standalone, to_char (A. telfeedate, 'yyyy ')

Notes: Four-table join query Problems
Select * from a left inner join B on. A = B. B right inner join C on. A = C. C inner join D on. A = D. d Where .....

Note: Obtain the smallest unused ID number in the table.

Select (case when exists (select * From handle B where B. handleid = 1) Then min (handleid) + 1 else 1 end) as handleid from handle where not handleid in (select. handleid-1 from handle)

A problem with an SQL statement: column and column Conversion

Select * From v_temp
The preceding view result is as follows:
User_name role_name
-------------------------
System Administrator
Feng Administrator
Feng users
Test Users
The result is as follows:
User_name role_name
---------------------------
System Administrator
Feng administrator, general user
Test Users
==============================
Create Table a_test (name varchar (20), role2 varchar (20 ))
Insert into a_test values ('lil', 'admin ')
Insert into a_test values ('zhang', 'admin ')
Insert into a_test values ('zhang', 'average user ')
Insert into a_test values ('change', 'General user ')

Create Function join_str (@ content varchar (100 ))
Returns varchar (2000)
As
Begin
Declare @ STR varchar (2000)
Set @ STR =''
Select @ STR = @ STR + ',' + rtrim (role2) from a_test where [name] = @ content
Select @ STR = right (@ STR, Len (@ Str)-1)
Return @ Str
End
Go

-- Call:
Select [name], DBO. join_str ([name]) role2 from a_test group by [name]

-- Select distinct name, DBO. uf_test (name) from a_test

Quickly compare two tables with the same structure
For two tables with the same structure, one table has about 30 thousand records and one table has about 20 thousand records. How can I quickly find different records of the two tables?
======================================
Here is a test method for getting data from the orders table in northwind.
Select * into N1 from orders
Select * into N2 from orders

Select * from N1
Select * from N2

-- Add a primary key and modify several entries of several fields in N1.
Alter table N1 add constraint pk_n1_id primary key (orderid)
Alter table N2 add constraint pk_n2_id primary key (orderid)

Select orderid from (select * from N1 Union select * from N2) a group by orderid having count (*)> 1

Yes, and the IDs of different records are displayed.
The following applies to situations where both parties record the same,

Select * from N1 where orderid in (select orderid from (select * from N1 Union select * from N2) a group by orderid having count (*)> 1)
Records that do not exist between the two parties are better handled.
-- Delete several records in N1 and N2
Delete from N1 where orderid in ('20140901', '20160901 ')
Delete from N2 where orderid in ('20140901', '20160901 ')

--*************************************** **********************
-- Both parties have this record but are not exactly the same
Select * from N1 where orderid in (select orderid from (select * from N1 Union select * from N2) a group by orderid having count (*)> 1)
Union
-- If N2 exists but does not exist in N1, it is stored in, limit 30.
Select * from N1 where orderid not in (select orderid from N2)
Union
-- N1.
Select * from N2 where orderid not in (select orderid from N1)

Four methods to obtain n to M records in the table:

1.
Select top M * into temporary table (or table variable) from tablename order by columnname -- insert top M pen
Set rowcount n
Select * from Table variable order by columnname DESC

2.
Select Top N * from (select top M * From tablename order by columnname) A order by columnname DESC

3. If there are no other identity columns in tablename, then:
Select Identity (INT) id0, * into # temp from tablename

N to M statements:
Select * from # temp where id0> = N and id0 <= m

If you report an error when executing the Select Identity (INT) id0, * into # temp from tablename statement, this is because the select into/bulkcopy attribute in the middle of your DB is not enabled and must be executed first:
Exec sp_dboption your DB name, 'select into/bulkcopy', true

4. If the table contains the identity attribute, it is simple:
Select * From tablename where identitycol between N and m

How do I delete repeated records in a table?
Create Table a_dist (ID int, name varchar (20 ))

Insert into a_dist values (1, 'abc ')
Insert into a_dist values (1, 'abc ')
Insert into a_dist values (1, 'abc ')
Insert into a_dist values (1, 'abc ')

Exec up_distinct 'A _ dist', 'id'

Select * From a_dist

Create procedure up_distinct (@ t_name varchar (30), @ f_key varchar (30 ))
-- F_key indicates the group field, that is, the primary key field.
As
Begin
Declare @ Max integer, @ ID varchar (30), @ SQL varchar (7999), @ type Integer
Select @ SQL = 'maid cur_rows cursor for select' + @ f_key + ', count (*) from '+ @ t_name + 'group by' + @ f_key + 'having count (*)> 1'
Exec (@ SQL)
Open cur_rows
Fetch cur_rows into @ ID, @ Max
While @ fetch_status = 0
Begin
Select @ max = @ max-1
Set rowcount @ Max
Select @ type = xtype from syscolumns where id = object_id (@ t_name) and name = @ f_key
If @ type = 56
Select @ SQL = 'delete from' + @ t_name + 'where' + @ f_key + '=' + @ ID
If @ type = 167
Select @ SQL = 'delete from' + @ t_name + 'where' + @ f_key + '=' + ''' + @ ID + ''''
Exec (@ SQL)
Fetch cur_rows into @ ID, @ Max
End
Close cur_rows
Deallocate cur_rows
Set rowcount 0
End

Select * From policypes
Select * From syscolumns where id = object_id ('A _ dist ')

Maximum sorting of query data (only one statement can be used for writing)
Create Table hard (qu char (11), Co char (11), je numeric (3, 0 ))

Insert into hard values ('A', '1', 3)
Insert into hard values ('A', '2', 4)
Insert into hard values ('A', '4', 2)
Insert into hard values ('A', '6', 9)
Insert into hard values ('B', '1', 4)
Insert into hard values ('B', '2', 5)
Insert into hard values ('B', '3', 6)
Insert into hard values ('C', '3', 4)
Insert into hard values ('C', '6', 7)
Insert into hard values ('C', '2', 3)

The query results are as follows:

Qu Co je
---------------------------
A 6 9
A 2 4
B 3 6
B 2 5
C 6 7
C 3 4

Group by Qu. In each group, the first two digits of JE are the largest !!
Only one SQL statement can be used !!!
Select * from hard a where je in (select Top 2 je from hard B where a. Qu = B. Qu order by je)

Are you sure you want to delete the SQL statement for Repeated Records?
How to delete a record with the same field, leaving only one record.
For example, the table test contains the ID and name fields.
If there are records with the same name, only one record is left, and the remaining records are deleted.
The content of the name is not fixed, and the number of identical records is not fixed.
Is there such an SQL statement?
====================================
A: a complete solution:

Record duplicate records in the temp1 table:
Select [flag field ID], count (*) into temp1 from [Table name]
Group by [flag field ID]
Having count (*)> 1

2. Record non-Repeated Records in Table temp1:
Insert temp1 select [flag field ID], count (*) from [Table name] group by [flag field ID] Having count (*) = 1

3. Create a table that contains all non-Repeated Records:
Select * into temp2 from [Table name] where flag field ID in (select flag field ID from temp1)

4. Delete duplicate tables:
Delete [Table name]

5. Restore table:
Insert [Table name] Select * From temp2

6. delete a temporary table:
Drop table temp1
Drop table temp2
======================================
B:
Create Table a_dist (ID int, name varchar (20 ))

Insert into a_dist values (1, 'abc ')
Insert into a_dist values (1, 'abc ')
Insert into a_dist values (1, 'abc ')
Insert into a_dist values (1, 'abc ')

Exec up_distinct 'A _ dist', 'id'

Select * From a_dist

Create procedure up_distinct (@ t_name varchar (30), @ f_key varchar (30 ))
-- F_key indicates the group field, that is, the primary key field.
As
Begin
Declare @ Max integer, @ ID varchar (30), @ SQL varchar (7999), @ type Integer
Select @ SQL = 'maid cur_rows cursor for select' + @ f_key + ', count (*) from '+ @ t_name + 'group by' + @ f_key + 'having count (*)> 1'
Exec (@ SQL)
Open cur_rows
Fetch cur_rows into @ ID, @ Max
While @ fetch_status = 0
Begin
Select @ max = @ max-1
Set rowcount @ Max
Select @ type = xtype from syscolumns where id = object_id (@ t_name) and name = @ f_key
If @ type = 56
Select @ SQL = 'delete from' + @ t_name + 'where' + @ f_key + '=' + @ ID
If @ type = 167
Select @ SQL = 'delete from' + @ t_name + 'where' + @ f_key + '=' + ''' + @ ID + ''''
Exec (@ SQL)
Fetch cur_rows into @ ID, @ Max
End
Close cur_rows
Deallocate cur_rows
Set rowcount 0
End

Select * From policypes
Select * From syscolumns where id = object_id ('A _ dist ')

column and column conversion-normal

assume that there is a student Partition Table (CJ)
name subject result
Zhang San Chinese 80
Zhang San mathematics 90
Zhang San physical 85
Li Si Chinese 85
Li Si mathematics 92
li Si physical 82

name:
Chinese Mathematics and Physics
Zhang San 80 90 85
Li Si 85 92 82

declare @ SQL varchar (4000)
set @ SQL = 'select name'
select @ SQL = @ SQL + ', sum (case subject when ''' + Subject + ''' then result end) ['+ Subject +'] '
from (select distinct subject from CJ) as a
select @ SQL = @ SQL + 'from test group by name'
exec (@ SQL)

column and column Conversion -- merge

Table,
id pid
1
1 2
1 3
2 1
2 2
3 1
table B:
id pid
1, 2, 3
2 1, 2
3 1

Create a merged function
Create Function fmerg (@ id int)
Returns varchar (8000)
as
begin
declare @ STR varchar (8000)
set @ STR = ''
select @ STR = @ STR + ',' + Cast (PID as varchar) from table A where id = @ ID
set @ STR = right (@ STR, Len (@ Str)-1)
return (@ Str)
end
go

-- call a UDF to obtain the result.
select distinct ID, DBO. fmerg (ID) from Table A

How to obtain all column names of a data table

The method is as follows: first obtain the systemid of the data table from the systemobject system table, and then retrieve all the column names of the data table from the syscolumn table.
The SQL statement is as follows:
Declare @ objid int, @ objname char (40)
Set @ objname = 'tablename'
Select @ objid = ID from sysobjects where id = object_id (@ objname)
Select 'column _ name' = Name from syscolumns where id = @ objid order by colid

Or

Select * From information_schema.columns where table_name = 'users'

Use SQL statements to change user passwords

SysAdmin role is required to modify other users.
Exec sp_password null, 'newpassword', 'user'

If the account is Sa, execute exec sp_password null, 'newpassword', SA

How can I determine which fields of a table cannot be blank?

Select column_name from information_schema.columns where is_nullable = 'no' and table_name = tablename

How can I find a table with the same fields in the database?
A. query the names of known Columns
Select B. Name as tablename, A. Name as columnname
From syscolumns a inner join sysobjects B
On a. ID = B. ID
And B. type = 'U'
And a. Name = 'your field name'

Query all names of unknown columns in different tables
Select O. Name as tablename, s1.name as columnname
From syscolumns S1, sysobjects o
Where s1.id = O. ID
And O. type = 'U'
And exists (
Select 1 from syscolumns S2
Where s1.name = s2.name
And s1.id <> s2.id
)

Query row XXX data

Assume that ID is the primary key:
Select * from (select top xxx * From yourtable) AA where not exists (select 1 from (select top XXX-1 * From yourtable) BB where AA. ID = BB. ID)

You can also use a cursor.
Fetch absolute [number] from [cursor_name]
The number of rows is absolute.

SQL Server date calculation
A. the first day of a month
Select dateadd (mm, datediff (mm, 0, getdate (), 0)
B. Monday of the week
Select dateadd (wk, datediff (wk, 0, getdate (), 0)
C. The first day of a year
Select dateadd (YY, datediff (YY, 0, getdate (), 0)
D. The first day of the quarter
Select dateadd (QQ, datediff (QQ, 0, getdate (), 0)
E. Last day of last month
Select dateadd (MS,-3, dateadd (mm, datediff (mm, 0, getdate (), 0 ))
F. Last day of last year
Select dateadd (MS,-3, dateadd (YY, datediff (YY, 0, getdate (), 0 ))
G. Last day of the month
Select dateadd (MS,-3, dateadd (mm, datediff (M, 0, getdate () + 1, 0 ))
H. The first Monday of this month
Select dateadd (wk, datediff (wk, 0,
Dateadd (DD, 6-datepart (day, getdate (), getdate ())
), 0)
I. The last day of the year
Select dateadd (MS,-3, dateadd (YY, datediff (YY, 0, getdate () + 1, 0 )).

Obtain the table structure [Replace 'sysobjects' with 'tablename']

Select case isnull (I. Name ,'')
When ''then''
Else '*'
End as ispk,
Object_name (A. ID) as t_name,
A. Name as c_name,
Isnull (substring (mb. Text, 1,254), '') as pbc_init,
T. Name as f_datatype,
Case isnull (typeproperty (T. Name, 'Scale '),'')
When ''then cast (A. prec as varchar)
Else cast (A. prec as varchar) + ',' + Cast (A. scale as varchar)
End as f_scale,
A. isnullable as f_isnullable
From syscolumns as
Join policypes as t
On (A. xtype = T. xusertype and A. ID = object_id ('sysobjects '))
Left join (sysindexes as I
Join syscolumns as A1
On (I. ID = a1.id and a1.id = object_id ('sysobjects') and (I. Status & 0x800) = 0x800 and a1.colid <= I. keycnt ))
On (A. ID = I. ID and A. Name = index_col ('sysobjects', I. indid, a1.colid ))
Left join syscomments as m
On (M. ID = A. cdefault and objectproperty (A. cdefault, 'isconstraint') = 1)
Order by A. colid ASC

SQL statement that extracts detailed descriptions of fields from all tables in the database

Select
(Case when a. colorder = 1 then D. Name else ''end) n' table name ',
A. colorder n' Field Sequence Number ',
A. Name n' field name ',
(Case when columnproperty (A. ID, A. Name, 'isidentity ') = 1 then' √ 'else''
End) n' identifiers ',
(Case when (select count (*)
From sysobjects
Where (name in
(Select name
From sysindexes
Where (ID = A. ID) and (indid in
(Select indid
From sysindexkeys
Where (ID = A. ID) and (colid in
(Select colid
From syscolumns
Where (ID = A. ID) and (name = A. Name) and
(Xtype = 'pk')> 0 then' √ 'else' end) n'primary key ',
B. Name n' type ',
A. length N 'number of bytes occupied ',
Columnproperty (A. ID, A. Name, 'precision ') as N 'length ',
Isnull (columnproperty (A. ID, A. Name, 'Scale'), 0) as N 'decimal ',
(Case when a. isnullable = 1 then '√ 'else' 'end) n' allow null ',
Isnull (E. Text, '') n' default value ',
Isnull (G. [value], '') as n' field description'
From syscolumns
Left join policypes B
On a. xtype = B. xusertype
Inner join sysobjects d
On a. ID = D. id and D. xtype = 'U' and D. Name <> 'dtproperties'
Left join syscomments E
On a. cdefault = E. ID
Left join sysproperties g
On a. ID = G. ID and A. colid = G. smallid
Order by object_name (A. ID), A. colorder

Quickly retrieve the total number of test records in a table [very effective for large-capacity tables]

Quick query of the total number of test records:
Select rows from sysindexes where id = object_id ('test') and indid in (0, 1)

Update 2 set khxh = (ID + 1) \ 2 2 2 rows increment number
Update [23] Set id1 = 'no. '+ right ('2013' + id, 6) where id not like 'no %' // increments
Update [23] Set id1 = 'no. '+ right ('000000' + Replace (id1, 'no.', ''), 6) // increment the position
Delete from [1] Where (ID % 2) = 1
Odd

Replace the table name
Update [1] Set domurl = Replace (domurl, 'upload/imgswf/', 'upload/Photo/') Where domurl like '% upload/imgswf/%'

Truncation
Select left (Table Name, 5)