类设计：就线程安全性和可测试性而言，将数据文件包装到类中(Class design: Wrapping up a datafile into a class with respect to thread-safety and testability)

 使用带有select的IF语句的正确语法如下所示 
 
CREATE DEFINER=`root`@`localhost` PROCEDURE `TxCountByDatePerod`(IN `fromdate` DATE, IN `todate` DATE, IN `department` VARCHAR(20), IN `status` VARCHAR(10), IN `ipg` VARCHAR(20))
SELECT d.dept_name,
       s.service_name,
       COUNT(*) AS all_row_count,
       IF(t.tx_status = 1, 
          (SELECT COUNT(*) FROM transaction_detail WHERE tx_status = 1), 
          0 
         ) AS successful_tx_count
FROM department AS d
INNER JOIN service s ON s.dept_id=d.dept_id
INNER JOIN transaction_detail t ON s.dept_id=t.dept_id
AND s.service_id=t.service_id
WHERE t.tx_begin_date BETWEEN fromdate AND todate
  AND (t.dept_id = department
       OR department ='null')
  AND (t.tx_status = status
       OR status ='null')
  AND (t.pg_name = ipg
       OR ipg ='null')
GROUP BY t.dept_id,
         t.service_id
 
 注意： 
 
 我只是把一个默认的0值，因为你正在做一个计数，但你可以改变它 
 
IF(t.tx_status = 1
    ,(SELECT COUNT(*) FROM transaction_detail WHERE tx_status = 1)
    ,0 -- # -- this can be anything.. you could put in NULL or whatever else if you want
  ) AS successful_tx_count
 
 你也可以使用一个用户定义的变量，并像这样插入 
 
SET @a := (SELECT COUNT(*) FROM transaction_detail WHERE tx_status = 1);
 
 然后在你的if语句中插入@a变量就像这样 
 
if(t.tx_status = 1, @a, 0)
 
 有关if函数的更多详细信息，请参阅本文 

the correct syntax for using an IF statement with a select is like this
 
CREATE DEFINER=`root`@`localhost` PROCEDURE `TxCountByDatePerod`(IN `fromdate` DATE, IN `todate` DATE, IN `department` VARCHAR(20), IN `status` VARCHAR(10), IN `ipg` VARCHAR(20))
SELECT d.dept_name,
       s.service_name,
       COUNT(*) AS all_row_count,
       IF(t.tx_status = 1, 
          (SELECT COUNT(*) FROM transaction_detail WHERE tx_status = 1), 
          0 
         ) AS successful_tx_count
FROM department AS d
INNER JOIN service s ON s.dept_id=d.dept_id
INNER JOIN transaction_detail t ON s.dept_id=t.dept_id
AND s.service_id=t.service_id
WHERE t.tx_begin_date BETWEEN fromdate AND todate
  AND (t.dept_id = department
       OR department ='null')
  AND (t.tx_status = status
       OR status ='null')
  AND (t.pg_name = ipg
       OR ipg ='null')
GROUP BY t.dept_id,
         t.service_id
 
NOTE: 
 
I just put in a default 0 value since you are doing a count but you can change that
 
IF(t.tx_status = 1
    ,(SELECT COUNT(*) FROM transaction_detail WHERE tx_status = 1)
    ,0 -- # -- this can be anything.. you could put in NULL or whatever else if you want
  ) AS successful_tx_count
 
you can also use a user defined variable and plug that in like so
 
SET @a := (SELECT COUNT(*) FROM transaction_detail WHERE tx_status = 1);
 
and then plug the @a variable in your if statement like so
 
if(t.tx_status = 1, @a, 0)
 
See this ARTICLE for more details on the if function