MySQLのif elseのような判断

1419 ワード

CASE WHEN 
...条件
THEN 
...結果
ELSE
LEFT(monthDate,4)は左から4文字またはRIGHT(monthDate,4)右から4文字を切り取り,この日付をグループ化することができる.
DATE_FORMAT(NOW()、'%Y')の日付のフォーマットは、日付をグループ化する必要がある場合は、これを使用しないでください.問題が発生します.
SELECT monDate,f1, n1 FROM (SELECT DATE_FORMAT(d1,'%Y-%m') AS monDate ,SUM(f1) as f1 ,SUM(n1) as n1 FROM (SELECT t.date AS d1, (SUM(CASE  WHEN t.num = 2 THEN t.FLOW_IN 
  	  ELSE  (t.FLOW_IN)*2 END) + SUM(CASE WHEN t.num = 2 THEN t.FLOW_OUT  ELSE  (t.FLOW_OUT)*2 END)) AS f1  FROM (SELECT COUNT(pk) AS num, DATE(DATE) AS DATE ,
  	   SUM(FLOW_IN)/2 AS FLOW_IN , SUM(FLOW_OUT)/2 AS FLOW_OUT  FROM moa_control_flow WHERE 1=1 GROUP BY DATE(DATE) ORDER  BY
  	    DATE ASC ) AS t  WHERE  1 = 1  GROUP BY t.date) AS f, (SELECT  t.date AS d2, SUM(CASE WHEN t.num = 2 THEN t.num_sum 
  	     ELSE  (t.num_sum)*2  END) AS n1, SUM(CASE  WHEN t.num = 2 THEN t.num_in ELSE  (t.num_in)*2 END) AS n2,  SUM(CASE 
  	      WHEN t.num = 2 THEN t.num_out ELSE  (t.num_out)*2 END) AS n3  FROM (SELECT COUNT(pk) AS num, DATE(DATE) AS DATE , 
  	       SUM(num_sum)/2 AS num_sum , SUM(num_in)/2 AS num_in, SUM(num_out)/2  AS num_out  FROM  moa_control_session_conn
  	        WHERE 1=1 GROUP BY DATE(DATE) ORDER  BY  DATE ASC ) AS t WHERE 1 = 1  GROUP BY t.date  ) AS c WHERE f.d1=c.d2 GROUP BY LEFT(d1,7)) AS t
  	            where LEFT(t.monDate,4) = DATE_FORMAT(NOW(),'%Y')