Share some nice SQL statements

1. Description: Copy table (copy structure only, source table name: A new table name: B) (Access available)
Law one: SELECT * into B from a where 1 <> 1
Law II: SELECT top 0 * into B from a

2. Description: Copy table (copy data, source table name: A target table name: B) (Access available)
Insert into B (A, B, c) select d,e,f from B;

3. Description: Copy of table across databases (use absolute path for specific data) (Access available)
Insert into B (A, B, c) Select d,e,f from B in ' specific database ' where condition
Example:.. From B in "&server.mappath (". ") & "Data.mdb" & "where".

4, Description: Sub-query (table name 1:a table name 2:b)
Select A,b,c from a where a in (select D from B) or: Select A,b,c from a where a in (a)

5, Description: Display the article, the author and the last reply time
Select A.title,a.username,b.adddate from Table A, (select Max (adddate) adddate from table where table.t Itle=a.title) b

6, Description: External connection query (table name 1:a table name 2:b)
Select A.a, A.B, A.C, B.C, B.D, B.f from a left off JOIN b on a.a = B.C

7, Description: Online view query (table name 1:a)
SELECT * FROM (select A,b,c from a) T where t.a > 1;

8, Description: Between usage, between limits the query data range includes the boundary value, not between does not include
SELECT * FROM table1 where time between time1 and time2
Select A,b,c, from table1 where a is not between value 1 and value 2

9. Description: How to use
SELECT * FROM table1 where a [not] in (' Value 1 ', ' Value 2 ', ' Value 4 ', ' Value 6 ')

10, Description: Two related tables, delete the main table is already in the secondary table does not have information
Delete from table1 where NOT EXISTS (SELECT * from table2 where table1.field1=table2.field1)

11, Description: Four table linked to check the problem:
SELECT * from a left inner join B in a.a=b.b right inner join C on A.A=C.C inner join D on A.a=d.d where .....

12, Description: Schedule five minutes before the reminder
Sql:select * from schedule where DATEDIFF (minute, F start time, GETDATE ()) > 5

13, Description: A SQL statement to take care of database paging
Select Top b.* from (select Top 20 primary key field, sort field from table name order by sort field desc) A, table name B where B. Primary key field = A. primary key field order by a. Sort field

14, Description: The first 10 records
Select Top Ten * form table1 where range

15, Description: Select in each group B value the same data corresponding to a maximum record of all information (similar to the usage can be used in the forum monthly leaderboard, monthly hot product analysis, ranked by the subject score, and so on.)
Select A,b,c from tablename ta where a= (select Max (a) from TableName TB where tb.b=ta.b)

16. Description: Include all rows in TableA but not in TableB and TableC and eliminate all duplicate rows to derive a result table
(select a from TableA) except (select a from TableB) except (select a from TableC)

17, Description: Randomly remove 10 data
Select Top * FROM tablename ORDER by NEWID ()

18, Description: Random selection of records
Select NEWID ()

19. Description: Delete duplicate records
Delete from TableName where ID not in (the Select Max (ID) from tablename GROUP by Col1,col2,...)

20, Description: List all the table names in the database
Select name from sysobjects where type= ' U '

21, Description: List of all the
Select name from syscolumns where id=object_id (' TableName ')

22, Description: Lists the type, Vender, PCs fields, arranged in the Type field, case can easily implement multiple choices, similar to case in select  .  
select   type,sum (case   vender   when   A    then    pcs   else   0   end), sum (case   vender   when   C    then   pcs   else   0   end), SUM (case   vender    when   B    then   pcs   else   0   end)     from   tablename   group   by   type 
Show results:  
type& nbsp;  vender   pcs 
Computers    a   1 
Computers    a   1& nbsp
Discs    b   2 
Discs    a   2 
Phone    b   3&n Bsp
Mobile    c   3

23. Description: Initialize table table1
TRUNCATE TABLE table1

24. Description: Select records from 10 to 15
Select Top 5 * FROM (select top [from table] ORDER by ID ASC) Table_ alias ORDER by ID Desc

Share some nice SQL statements