php - SELECT 中带有 CASE 的 SQL 查询不起作用

Question

我有一个更大的复杂查询的这一部分:

SELECT SUM(pir.priceChange) AS CASE 
WHEN (SUM(pir.priceChange) > 0) THEN sellSum 
WHEN (SUM(pir.priceChange) < 0) THEN priceSum 
END

每当我尝试通过 PDO 将其传递到 MySQL (MyISAM) 时，它都会抛出异常:

Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(SELECT SUM(pir.priceChange) AS CASE WHEN (SUM(pir.priceChange) > 0) THEN sellSu' at line 17

我想这与我试图错误地使用 CASE 语句有关。真的很想知道到底出了什么问题，提前致谢。

编辑:我的完整查询(没有任何情况下编辑):

SELECT  pi.id AS id,
pi.type AS type,
pi.parValue AS rating,
pi.duration AS time,
countAmt.amt AS amtSold,
IFNULL(pii.pChange,
0) AS priceTotal,
0) AS costTotal,
IFNULL(pii.selfPrice,
0) AS costPrice,
(IFNULL(pii.sellSum,
0) - IFNULL(pii.selfPrice,
0)) AS profit,
countClose.amtClosed AS amtClosed
FROM salon.paymentInstrument as pi
(SELECT SUM(pir.priceChange) AS pChange,
SUM(pir.costChange) AS cChange,
(IFNULL(pir.priceChange,
0) - IFNULL(pir.costChange,
0)) AS selfPrice,
pii.paymentInstrumentID
FROM salon.paymentInstrumentRegister as pir
JOIN salon.paymentInstrumentItem as pii ON pir.paymentInstrumentItemID = pii.id
WHERE pir.date >= '2014-04-11'
AND pir.date <= '2014-04-25'
GROUP BY pii.paymentInstrumentID 
) as pii
(SELECT pii.paymentInstrumentID,
COUNT(*) AS amtClosed
FROM salon.paymentInstrumentItem as pii
WHERE pii.annulTime >= 2014-04-11
AND pii.annulTime <= 2014-04-25
AND pii.status <> "active"
GROUP BY pii.paymentInstrumentID 
) as countClose
(SELECT pii.paymentInstrumentID,
COUNT(*) AS amt
FROM salon.paymentInstrumentItem as pii
WHERE pii.startTime >= 2014-04-11
AND pii.startTime <= 2014-04-25
GROUP BY pii.paymentInstrumentID 
) as countAmt

抱歉格式不正确，它是用 PHP 动态生成的。我有一种感觉，我在查询的其他地方犯了错误。

Answer 1

试试这个方法:

SELECT CASE WHEN SUM(pir.priceChange)>0 THEN SUM(pir.priceChange) END as SumPrice,
       CASE WHEN SUM(pir.priceChange)<0 THEN SUM(pir.priceChange) END as priceSum
FROM TableName

SQL Fiddle 中的示例.