- 2019-10-24 15:29
*views 4*- SQL

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

- Python187 blogs
- Java177 blogs
- Vue93 blogs
- Flow Chart80 blogs
- algorithm54 blogs
- C++52 blogs
- MySQL49 blogs
- javascript49 blogs
- more...

Daily Recommendation

views 1

views 0

©2020-2021 ioDraw All rights reserved

lstm prediction model _ use LSTM Forecast stock price Prepare for the Blue Bridge Cup — enumeration ——[USACO Nov08] deal 14:00 interview ,14:08 Just came out , The question is too much ...Python Write and read Excel Tabular data jquery Detailed explanation of method properties Linux Study notes （1）SpringMVC Source code analysis 【Hadoop】——JavaAPI operation 【Redis】—— Cache penetration , Buffer breakdown , Cache avalanche TCP and UDP What is it? ? What's the difference ?