用不同的where子句选择SQL

Question

我需要用不同的where子句进行一次选择(使用不同的产品代码)。我需要的结果如下。

我有一张桌子，如：

ApID | Date       | Code | Qty | Price | Sum
222  | 2014-10-11 | 555  | 1   | 2,22  | 2,22
222  | 2014-10-11 | 555  | 1   | 2,22  | 2,22
222  | 2014-10-11 | 333  | 1   | 3,33  | 3,33
222  | 2014-10-12 | 555  | 1   | 2,22  | 2,22

My查询：

SELECT 
CAST(Date AS DATE) as 'Data',
SUM(Qty*Price) AS 'Sum',
SUM(Qty) AS 'Qty'
  FROM Table
  WHERE ApID = 222
  AND Data BETWEEN '2014-10-11' AND '2014-10-13' 
  AND Code LIKE '555'
GROUP BY CAST(KvitoGalva.Data AS DATE)

我得到了这样的结果：

Data       | Sum  | Qty
2014-10-11 | 4.44 | 2
2014-10-12 | 2.22 | 1

我需要在一个表中显示结果：

Data       | Sum 555  | Qty 555 | Sum 333 | Qty 333 |
2014-10-11 | 4.44     | 2       | 3.33    | 1       |
2014-10-12 | 2.22     | 1       | 0       | 0       |

试过：

SELECT((Select1),(Select2))
----------------------------
SELECT 1
union
select 2

Answer 1

您可以使用条件聚合来完成此操作：

SELECT CAST(Date AS DATE) as Data,
       SUM(case when code = '555' then Qty*Price else 0 end) AS Sum555,
       SUM(case when code = '555' then Qty else 0 end) AS Qty555,
       SUM(case when code = '333' then Qty*Price else 0 end) AS Sum333,
       SUM(case when code = '333' then Qty else 0 end) AS Qty333
FROM Table t
WHERE ApID = 222 AND
      Data BETWEEN '2014-10-11' AND '2014-10-13' AND
      Code IN ('555', '333')
GROUP BY CAST(KvitoGalva.Data AS DATE);

注意:仅对字符串和日期常量使用单引号。列别名不需要引号，如果需要，数据库将有一个更好的字符来转义名称。