Classic SQL statements

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 :--
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

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-common

assume 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
. the first day of a month
select dateadd (mm, datediff (mm, 0, getdate (), 0)
B. monday of this 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. last day of the year
select dateadd (MS,-3, dateadd (YY, datediff (YY, 0, getdate () + 1, 0 )).