Let the numbers show the SQL of the words
C:/Users/bobo/AppData/Local/YNote/data/qq1BD99A3C386BF33BC41A3E78A77E7734/2698c116efe5423980e1e1395409b41c/i%5B35%7Exc2qdob.png
Using trinocular operations is fine
C:/Users/bobo/AppData/Local/YNote/data/qq1BD99A3C386BF33BC41A3E78A77E7734/5f6a2d9db0bd43acbef159186eabfd51/6ihqynhx_z89.png
SQL can also be written
select *,
case sgroup
when 1 then N'Group 1'
when 2 then N'Group 2'
when 3 then N'Group 3'
else N'unknown' end groupname
from @stuinfo
===========
This topic is SQL
select a.StandardId,a.StandardType, case StandardStatus
when 1 then N'void'
when 2 then N'Currently Valid'
when 3 then N'to be implemented'";
else N'unknown' end groupname,
a.StandardTitle,a.ImplementationDate,a.ReleaseDate from OATestStandard a
SQL learning address https://www.cnblogs.com/4littleProgrammer/p/4820006.html
It's almost time to get off work, so take some time to summarize the case usage of SQL.
The role of CASE in SQL:An expression used to compute a list of conditions, and returns one of the possible results.The case type of SQL is If-Esle If-Else or Switch in programming languages, but it is not used to control the execution process of SQL programs, but as a functioncolumnlogical use.
Syntax:
case [input_expression]
when when_expression then result_expression
[... n]
[else else_result_expression]
end
Note: [] is optional.
Prepare test data:
1
2
3
4
5
6
7
8
9
10
11
12
| declare@stuinfotable
(idint,
sname nvarchar(20),
gendervarchar(1),
sgroupint)
insert into@stuinfo
select 1,'Zhang San','m',1union all
select 2,'Li Si','f',1union all
select 3,'Wang Wu','f',2union all
select 4,'Zhao Liu','m',3union all
select 5,'Yellow Seven','m',3
|
1. Add an expression after the case
Returns based on the expression result.
1
2
3
4
5
6
7
| select*,
casesgroup
when 1 thenN'Group 1'
when 2 thenN'Group 2'
when 3 thenN'Group 3'
elseN'Unknown' endgroupname
from@stuinfo
|
2. No expression after case
Without an expression, it is returned according to the condition of when.
| select*,
case
whensgroup = 1andgender ='m' thenN'The first group of boys'
whensgroup = 1andgender ='f' thenN'First group of girls'
whensgroup = 2andgender ='m' thenN'The second group of boys'
whensgroup = 2andgender ='f' thenN'The second group of girls'
whensgroup = 3andgender ='m' thenN'The third group of boys'
whensgroup = 3andgender ='f' thenN'The third group of girls'
elseN'Unknown' endcomment
from@stuinfo
|
3. For order by
If the stored procedure needs to support multiple sorts, you can pass a parameter variable and then judge based on that variable.
1
2
3
4
5
6
7
| declare@orderbyint
set@orderby = 1
select * from@stuinfo
order by
case when@orderby = 1thenidend desc,
case when@orderby = 2thenidend
|
Use multiple cases here, because desc needs to be placed after end, otherwise there will be syntax errors.
4. Example:
select a.ApplyId,a.Code,a.CostItemId,a.CostItemName,a.CreatedDate,a.CreatedUserId,a.EquipmentCategroyId,a.Flag,a.FlowTime,a.Name,a.ParentId,a.PassingUserId,a. PassingUserIds,a.PassingUserNames,a.ProjectId,a.Remark,a.Spec,a.Unit, b.*, c.Code as ParentCode, c.Name as ParentName,casea.ImportFlag when 0then 'Not imported'when1 then 'Imported' endasImportFlagfrom EquipmentCategoryApply as a
inner join ProjectViewForJoin as b on a.ProjectId = b.ProjectIdForJoin
inner join EquipmentEquipmentCategory as c on a.ParentId = c.EquipmentCategoryId
|
Posted on 4/28/2019 3:57:54 PM
|
|
|
