Case of SQL statement

Case statement Syntax:

--Simple case function Sexwhen ' 1 ' then ' Male ' when ' 2 ' then ' women ' else ' other ' END
--case search function Case if sex = ' 1 ' Then ' man ' when sex = ' 2 ' Then ' women ' else ' other ' END

First, create a users table that contains Id,name,sex three fields with the following table contents: Copy Code SQL>drop table users purge; drop table users purge ORA-00942: The table or view does not exist in SQL> CREATE table users (IDint, Name VARCHAR2 (20), sex number); Table Createdsql> INSERT into Users (Id,name) VALUES (1, ' Zhang Yi '); 1Row Insertedsql> INSERT into Users (Id,name,sex) VALUES (2, ' Zhang Yi ', 1); 1Row Insertedsql> INSERT into Users (Id,name) VALUES (3, ' Zhang San '); 1Row Insertedsql> INSERT into Users (Id,name) VALUES (4, ' Zhang Si '); 1Row Insertedsql> INSERT into Users (Id,name,sex) VALUES (5, ' Five ', 2); 1Row Insertedsql> INSERT into Users (Id,name,sex) VALUES (6, ' Six ', 1); 1Row Insertedsql> INSERT into Users (Id,name,sex) VALUES (7, ' seven ', 2); 1Row Insertedsql> INSERT into Users (Id,name,sex) VALUES (8, ' eight ', 1); 1Row Insertedsql>commit; Commit Completesql> select *from users; ID NAME SEX---------------------------------------------------------------------1Zhang Yi2 Sheets 21 3Zhang San4Zhang Si5 piece 52 6 piece 61 7 72 8 Piece 81 8rows selected copy code1, "Sex" in the results of the above tableis expressed in code, and you want the code to be expressed in Chinese. You can use Case statements in statements: Copy code SQL>Select U.id,u.name,u.sex,2 ( CaseU.sex3 when 1 Then ' Male ' 4 when 2 Then ' female ' 5Else' Empty ' 6End7) Gender8from users u; ID NAME Sex Sex---------------------------------------------------------------------------1an empty one.2 Sheets 21male3Zhang Sanyi's4a four-empty one.5 Sheets 52female6 Sheets 61male7 Sheets 72female8 Sheets 81male8rows selected copy code2. If you do not want the list to appear in the "Sex"column with the following statement: Copy code SQL>Select U.id,u.name,2 ( CaseU.sex3 when 1 Then ' Male ' 4 when 2 Then ' female ' 5Else' Empty ' 6End7) Gender8from users u; ID NAME Gender-----------------------------------------------------------------1an empty one.2Zhang Yinan3Zhang Sanyi's4a four-empty one.5Zhang Five women6Zhang six male7Zhang seven women8Zhang Banan8rows selected copy code3, combining sum with case can be used to achieve segmented statistics. If you now want to count the number of genders in the table above, the SQL statement is as follows: Copy code SQL>Select2 sum ( CaseU.sex when 1 then 1Else0end) Male,3 SUM ( CaseU.sex when 2 then 1Else0end) Female,4 SUM ( CaseWhen U.sex <>1 and u.sex<>2 then 1Else0end) gender is empty5from users u; Male female sex is empty------------------------------3 2 0---------------------------------------------------------- ----------------------SQL>Select2 Count ( CaseWhen U.sex=1 then 1end) Male,3 Count ( CaseWhen u.sex=2 then 1end) Female,4 Count ( CaseWhen U.sex <>1 and u.sex<>2 then 1end) gender is empty5from users u; Male female Don't be empty------------------------------3 2 0

2. Practical application of the project

/*** Method Name: findaucagencydealcount<br> * Description: Each object in the list is a record <br> * object[] The subscript value is the corresponding field column <br> * Perform native SQL connection query <br> *@paramStartDate *@paramEndDate *@returnArray collection is list<object[]>*/@Query (Value= "Select S.name as Aucagencyname,sum (a.qty_auction) as Auclotcount," + "sum (case when b.is_deal=1 and a.is_published=2 Then B.qty_deal else 0 end) as Auclotdealcount, ' + ' sum (case when b.is_deal=1 and b.is_settled=1 and a.is_published=2nd En b.qty_deal else 0 end) as Factcount, "+" sum (case if b.is_deal=4 and a.is_published=2 then b.qty_deal else 0 end) A S regretcount, "+" sum (case is a.is_published=2 and a.type=1 then 1 else 0 end) as Shootnumber, ' + ' sum (case when a. is_published=2 and a.type=2 then 1 else 0 end) as Sellnumber, ' + ' sum (case when a.is_published=2 and B.is_deal=1 and A.T Ype=1 then 1 else 0 end) as Auclotdealnumber, ' + ' sum (case when a.is_published=2 and B.is_deal=1 and a.type=1 and b.is_p  Riority=1 then 1 else 0 end) as Firstnumber, ' + ' sum (case is a.is_published=2 and a.type=2 and b.is_deal=1 then 1 else 0 end) as Selldealnumber "+" from Auc_lot a left joins Auc_brand B on a.id=b.auc_id left joins Sys_agency s on a.agency _id=s.id "+" where (DAte_format (a.published_time, '%y-%m-%d ') between:startdate and:enddate) "+" and a.agency_id in (: agencyids) "+" GRO Up by S.name ", nativequery=true) List<Object> Findaucagencydealcount (@Param ("StartDate") string startdate, @Param ("EndDate") string endDate, @Param ( "Agencyids") list<long>agencyids); 

Case of SQL statement