Common SQL statements (nested subqueries/random queries, etc.)

1. the SQL insert statement obtains the auto-generated incremental ID value.
Copy codeThe Code is as follows:
Insert into Table1 (Name, des, num) values ('ltp ', 'thisbest', 10 );
Select @ identity as 'id'

2. Implement 1 or 0 to show as a male or female
Copy codeThe Code is as follows:
Select name, Sex = (case Sex when '1' then' male 'when' 0'then' female 'end) from Tablename

3. nested subqueries
Copy codeThe Code is as follows:
Select a, B, c from Table1 where a IN (select a from Table2)

4. display the article, Submitter, and last reply time
Copy codeThe Code is as follows:
Select a. title, a. username, B. adddate from tablename a, (select max (adddate) adddate from tablename where tablename. title = a. title) B

5. Example of randomly extracting records
Copy codeThe Code is as follows:
SQL Server: Select Top 10 * From Tablename Order By NewID ()

Access: Select Top 10 * From Tablename Order By Rnd (ID)

In Rnd (ID), the ID is an automatically numbered field, which can be completed using any other value, for example, UserName

Select Top 10 * From Table Order BY Rnd (Len (UserName ))

MySql: Select * From Table Order By Rand () Limit 10

6. Find records with the same attributes in the same table
Copy codeThe Code is as follows:
Select UserID from Accounts_Users where UserName is not null group by UserID having count (*)> 1

7. query data of all products of a category
Copy codeThe Code is as follows:
SELECT CategoryName, ProductName FROM Categories left join Products ON Categories. CategoryID = Products. CategoryID;

8. query user information between 2 and 5 by range
Copy codeThe Code is as follows:
Select * from UserValue where UserID between 2 and 5;

9. 5 minutes in advance of the schedule
Copy codeThe Code is as follows:
Select * from TabSchedule where datediff (minute, getdate (), start time) <5

10. obtain the maximum number of days in the month of a date.
Copy codeThe Code is as follows:
Select day (DATEADD (dd,-DAY ('2017-02-13 '), DATEADD (mm, 1, '2017-02-13') AS 'daynumber'

11. sort by last name strokes
Copy codeThe Code is as follows:
Select * From TableName Order By CustomerName Collate Chinese_PRC_Stroke_ci_as

12. Usage of wildcards
Copy codeThe Code is as follows:
1. select * from tablename where column1 like '[A-M] %' so that you can select a record with the first letter in the column field between the A-M
2. select * from tablename where column1 like '[ABC] %'. You can select A record with the first letter A, B, or C in the column field.
3. select * from tablename where column1 like '[A-CG] %' this way you can select a record with the first letter in the column field between the A-C or G
4. select * from tablename where column1 like '[^ C] %'. You can select a record where the first letter in the column field is not C.

13. Copy the table structure (only copy the structure, source table name: a, target table name: B)
Copy codeThe Code is as follows:
Select * into B from a where 1 <> 1 or select top 0 * into [B] from [a]

14. Copy table data (copy data, source table name: a, target table name: B)
Copy codeThe Code is as follows:
Insert into B (Name, des, num) select Name, des, num from Table1;