Database statement Summary

Summary of database statements: Table # lastTable, column: [provinceId], [allTypeSum], contains dozens of rows of data. I want to select all and sort them, however, the provinceId40 does not need to be sorted. That is to say, I want to sort all the rows except allTypeSum40 BY THE allTypeSum value desc, then put the id40 rows at the end of the result, and then output the solution.

Summary of database statements: Table # lastTable, column: [provinceId], [allTypeSum], contains dozens of rows of data. I want to select all and sort them, but there is no need to arrange provinceId = 40, that is to say, I want to sort all the rows except allTypeSum = 40 by allTypeSum value desc, and then put the id = 40 rows to the end of the result, then output the solution

Database statement Summary

Problem: Table # lastTable, column: [provinceId], [allTypeSum], which contains dozens of rows of data. I want to select all and sort them. However, if provinceId = 40, no sorting is required, that is to say, I want to sort all the rows except allTypeSum = 40 by allTypeSum value desc, then put the id = 40 rows at the end of the result, and then output

Solution 1:

Select * from (select top 100 * from # lastTable where provinceId! = 40 order by allTypeSum desc -- note that the top statement must be used here.) Tunion allselect * from # lastTable where provinceId = 40

In fact, a temporary table is used here, but pay attention to multiple select and top

Solution 2:

TempColunmfrom # lastTable order by tempColunm asc, allTypeSum desc

In fact, this method uses the case statement, which is equivalent to adding a new column to the table. The value of the newly added column of the row with provinceId = 40 is 1, and the other columns are all 0, and then sort by two columns, the result of adding a new column is as follows:

Bytes ----------------------------------------------------------------------------------------

Learned: The. case statement adds the power of temporary Columns

Bytes ----------------------------------------------------------------------------------------

Posted on

,