1. When the value of a field needs to be displayed through other word values (record conversion), the following statement can be used:
Case type when '1' then' normal channel 'when' 2' then' high-end channel 'end as type
Where "type" is the field name, "1", "2" is the field value
2. Return the table name in the database:
Select name from table where type = 'U' and status> = 2
3. sort by the first letter of the alphabet:
Select * from Table order by column name collate chinese_prc_cs_as_ks_ws
First, here, collate is a clause that defines sorting rules and can be applied to database or column definitions, or to string expressions to apply sorting rule projection.
The syntax is collate collation_name. The collate_name parameter is the name of the sorting rule applied to expressions, column definitions, or database definitions.
• Collation_name can only be the specified windows_collation_name or SQL _collation_name.
1. windows_collation_name is the name of a Windows sorting rule. See windows sorting rule name.
2. SQL _collation_name is the name of the SQL sorting rule. See SQL sorting rule name.
Note:SQL Server sorting rules are usually not used many times and may be unfamiliar to many beginners. However, when you encounter an SQL Server database that supports cross-database, multi-table join queries, if the default Character Set of the two databases is different, the system will return the following error: "The equal to Operation sorting rule conflict cannot be resolved."
4. sort by strokes of the Last Name:
Select * from Table order by customername collate chinese_prc_stroke_ci_as
Note.
5. query the range of the specified value:
1. stockname like '% [A-Za-Z] %'
2. stockname like '[^ F-M]'
Where
[]Value Range
^Exclude specified range
6. random sorting of query results:
Select * from Table orders order by newid ()
7. Return all records of the two tables:
Select tablea. A tableb. B from tablea inner join tableb as B ontablea. A = B. B
Here we will not comment on the usage of As in SQL.
8. Execute the statement after the wait time:
CopyCode The Code is as follows: waitfor delay '00: 00: 05'
Select * From studentinfowaitfor time '23: 08: 00
9. Insert a record to Table A, and the inserted record does not exist in Table A (determined by a field ):
Copy code The Code is as follows: insert into tablea (tracekey, mutesms, createtime, traceuser, tracetime, traceslot, traceduration)
Select 'trace _ timer ', 0, getdate (), mobileid, getdate (), '30', '0' from tableb where corpid = 10001
And not exists (select traceuser from tablea) and mobileid like '13' and Len (mobileid) = 11