因为用多条SELECT怕影响效率,请问怎么把它整合成一句?SELECT a.deptno AS deptno,
COUNT(distinct a.account) AS y_account,
SUM(CASE WHEN b.tradedate = '201110'
THEN b.amount
ELSE 0
END
) AS year_amount,
FROM a_table a
INNER JOIN b_table b
ON a.account = b.account
WHERE a.open_date BETWEEN '20110101' AND '20111030'
AND b.tradedate BETWEEN '20110101' AND '20111030'
-- AND b.amount <> 0
GROUP BY a.deptno 要求:我要计算当 b.amount<>0 的条数(上面已注解掉),但统计求和则不需要这个条件,请问加条件怎么处理,谢谢.
COUNT(distinct a.account) AS y_account,
SUM(CASE WHEN b.tradedate = '201110'
THEN b.amount
ELSE 0
END
) AS year_amount,
FROM a_table a
INNER JOIN b_table b
ON a.account = b.account
WHERE a.open_date BETWEEN '20110101' AND '20111030'
AND b.tradedate BETWEEN '20110101' AND '20111030'
-- AND b.amount <> 0
GROUP BY a.deptno 要求:我要计算当 b.amount<>0 的条数(上面已注解掉),但统计求和则不需要这个条件,请问加条件怎么处理,谢谢.
SELECT a.deptno AS deptno,
COUNT(DISTINCT DECODE(b.amount,0,null,a.account)) AS y_account,
SUM(CASE
WHEN b.tradedate = '201110' THEN
b.amount
ELSE
0
END) AS year_amount,
FROM a_table a
INNER JOIN b_table b ON a.account = b.account
WHERE a.open_date BETWEEN '20110101' AND '20111030'
AND b.tradedate BETWEEN '20110101' AND '20111030'
GROUP BY a.deptno
只作为count的条件?
SELECT a.deptno AS deptno,
COUNT(DISTINCT CASE
WHEN b.tradedate BETWEEN '20110101' AND '20111030' THEN
DECODE(b.amount, 0, NULL, a.account)
ELSE
NULL
END) AS y_account,
SUM(CASE
WHEN b.tradedate = '201110' THEN
b.amount
ELSE
0
END) AS year_amount,
FROM a_table a
INNER JOIN b_table b ON a.account = b.account
WHERE a.open_date BETWEEN '20110101' AND '20111030'
GROUP BY a.deptno