mysql - 在 where 条件下计算个人总计的情况

Question

select       
Count(*)
from  gl
join TRVMAINDATA msit 
on gl.TRANSACTIONID = msit.CHARGETRANSACTIONID

where gl.CREATEDDATETIME >= DATEADD(MONTH,-2,getdate()) AND 
             datediff(DAY,gl.BUSINESSPROCESSDATE,gl.CREATEDDATETIME)>=4  AND
             gl.MARKETCODE In('535','532','056','050','039','036','034','033','030','029','027','023','022','021','018','015','012','011','010','009','007','006','005','002','001' ) and DATEADD(MONTH,DATEDIFF(MONTH,0,gl.CREATEDDATETIME),0) = '2/1/2017  12:00:00 AM'

上面的代码给出了提到的市场代码的总数。但我也想要其他代码的市场代码，这些代码不在此处。例如

select              
Count(*)
from  gl
join TRVMAINDATA msit 
on gl.TRANSACTIONID = msit.CHARGETRANSACTIONID
where gl.CREATEDDATETIME >= DATEADD(MONTH,-6,getdate()) AND 
             datediff(DAY,gl.BUSINESSPROCESSDATE,gl.CREATEDDATETIME)>=0     
    and 
    DATEADD(MONTH,DATEDIFF(MONTH,0,gl.CREATEDDATETIME),0) = '2/1/2017  12:00:00 AM'
    AND
   (case
     when gl.MARKETCODE In('535','532','056','050','039','036','034','033','030','029','027','023','022','021','018','015','012','011','010','009','007','006','005','002','001') then 'Proprietary'
    when gl.MARKETCODE='037' then 'US' 
   else 'partner'
   end )

是否可以在一个 sql 查询中计算其他市场代码的总数，还是我必须单独计算？

Answer 1

我认为您希望在 select 语句中使用 case when 进行 count，如下所示:

SELECT COUNT(CASE 
               WHEN gl.marketcode IN ( '535', '532', '056', '050', 
                                      '039', '036', '034', '033', 
                                      '030', '029', '027', '023', 
                                      '022', '021', '018', '015', 
                                      '012', '011', '010', '009', 
                                      '007', '006', '005', '002', '001' ) THEN 1  
               ELSE NULL 
             END) AS `Proprietary`, 
       COUNT(CASE 
               WHEN gl.marketcode = '037' THEN 1 
               ELSE NULL 
             END) AS `US`, 
       COUNT(CASE 
               WHEN gl.marketcode NOT IN ( '535', '532', '056', '050', 
                                          '039', '036', '034', '033', 
                                          '030', '029', '027', '023', 
                                          '022', '021', '018', '015', 
                                          '012', '011', '010', '009', 
                                          '007', '006', '005', '002', 
                                          '001', '037' ) THEN 1 
               ELSE NULL 
             END) AS `partner` 
FROM gl 
JOIN trvmaindata msit 
ON gl.transactionid = msit.chargetransactionid 
WHERE gl.createddatetime >= DATEADD(month, -2, GETDATE()) 
AND DATEDIFF(day, gl.businessprocessdate, gl.createddatetime) >= 4 
AND DATEADD(month, DATEDIFF(month, 0, gl.createddatetime), 0) =  '2/1/2017  12:00:00 AM'