Mysql case expression
I. background
The resc table not only saves the first-and second-level functional modules of the system, but also saves the functions of the pages of the secondary functional modules, and defines the father-son relationship through pid.
I want to sort out the corresponding relationship between the first-and second-level functional modules through the resc table, which is similar to this result.
Second, the realization method
Select case when PID=1 then 'user Management' when PID=4 then 'Agent Management' when PID=8 then 'Media Management' when PID=21 then 'report Management' when PID=26 then 'content Management' when PID=30 then 'performance Management' when PID=34 then 'Financial Management' END as menu, `name`, PID from rescwhere pid in (select id from rescwhere pid=0) order by pid
Execution result:
Paste the execution results into excel and merge the cells to get the expected results. The functional modules can be tracked in excel.
III. Theory
Case expressions in sql, you can implement the logic of if-then-else.
There are two types of case expressions:
A simple case expression that uses the expression to determine the return value
Search for case expressions and use criteria to determine the return value.
Use simple case expressions
Syntax:
Case search_expression
When expression1 then result1
When expression2 then result2
...
When expressionN then resultN
Else default_result
End
Keyword description:
Search_expression, the expression to be evaluated, a field in the data table
ExpressionX, which is an expression that compares to search_expression
Result1, which is the value returned when the expression is met
Default_result, is the default return value when the expression cannot be matched.
two。 Search for case expressions
Syntax:
Case
When condition1 then result1
When condition2 then result2
...
When conditionN then resultN
Else default_result
End
Keyword description:
Condition, which is the expression to be evaluated
Result, which is the return value. If condition1 is true, result1 is returned, and so on.
Default_result, is the default return value when the expression cannot be matched.
