Application scenario ： According to the different states of the seal , Count the number of seals in different states .
Just started Baidu , I did find different answers , But I can only blame myself sql Insufficient grammatical interpretation , Still didn't write it out , It led to the following wrong writing .
select b.corporateOrgName, b.corporateOrgGuid companyId, count(case when
bc.ftype not in(1,2) then 1 else 0 end ) total, count(case when bc.ftype
in(3,4,5) then 1 else 0 end ) usetotal, count(case when bc.ftype = 6 then 1
else 0 end ) saveTotal, count(case when bc.ftype = 7 then 1 else 0 end )
returnTotal from B_seal_cycle bc join B_seal b on bc.sealId = b.id where
b.corporateOrgName like '%%' group by b.corporateOrgName,b.corporateOrgGuid
Logically , But I can't get the ideal interface , In this way, every data of statistics is the same . Correct writing after change
select b.corporateOrgName, b.corporateOrgGuid companyId, count(case when
bc.ftype not in(1,2) then 1 end ) total, count(case when bc.ftype in(3,4,5)
then 1 end ) usetotal, count(case when bc.ftype = 6 then 1 end ) saveTotal,
count(case when bc.ftype = 7 then 1 end ) returnTotal from B_seal_cycle bc join
B_seal b on bc.sealId = b.id where b.corporateOrgName like '%%' group by
b.corporateOrgName,b.corporateOrgGuid
Do you see the difference ? hold else 0 Remove it and you get the right result .
<> Problems encountered
1, yes case when grammar , Wrong interpretation .
Yes else after , Always take the result 1 or 0.
2, count Functions will be ignored 1 or 0 Make statistics .
3, Dang Jia else 0 after , Can pass sum Function for statistics .
It can also be written like this
select b.corporateOrgName, b.corporateOrgGuid companyId, sum(case when
bc.ftype not in(1,2) then 1 else 0 end ) total, sum(case when bc.ftype
in(3,4,5) then 1 else 0 end ) usetotal, sum(case when bc.ftype = 6 then 1 else
0 end ) saveTotal, sum(case when bc.ftype = 7 then 1 else 0 end ) returnTotal
from B_seal_cycle bc join B_seal b on bc.sealId = b.id where b.corporateOrgName
like '%%' group by b.corporateOrgName,b.corporateOrgGuid
something the matter , Or better , Thank you for your message .
Technology