sql - SQLite查询聚合

Question

我正在寻求使用Sqlite3解决以下问题。我想汇总此数据：

+--------+-------+----------+----------+--------+
| DRAWID | BETID | TICKETID |  STATUS  | AMOUNT |
+--------+-------+----------+----------+--------+
|      1 |     1 |        1 | OK       |      3 |
|      1 |     2 |        1 | OK       |      2 |
|      1 |     3 |        2 | CANCELED |      4 |
|      1 |     4 |        2 | CANCELED |      7 |
|      1 |     5 |        3 | OK       |      5 |
|      1 |     6 |        3 | OK       |      4 |
+--------+-------+----------+----------+--------+

到以下

+--------+-----------+-----------+--------------+----------+-------------+
| BetsOK | BetsNotOk | TicketsOk | TicketsNotOk | AmountOk | AmountNotOk |
+--------+-----------+-----------+--------------+----------+-------------+
|      4 |         2 |         2 |            1 |       14 |          11 |
+--------+-----------+-----------+--------------+----------+-------------+

为了对TicketsOk进行正确的分组，我似乎要进行两个子查询并必须加入。一个子查询可以确定，而另一个子查询可以这样

(SELECT drawId, (DISTINCT ticketId) FROM Bets WHERE drawId =  AND userId = AND Status IN ("OK")) TblTicketsOk ...

在sqlite3或通用sql中是否可能存在一些更简单的东西。有人可以帮忙编写完整的sql语句来解决此问题吗？

Answer 1

您不需要使用join，请尝试在distinct函数中使用CASE WHEN和COUNT。

SELECT COUNT(distinct(CASE WHEN STATUS = 'OK' then BETID end)) BetsOK,
       COUNT(distinct(CASE WHEN STATUS = 'CANCELED' then BETID end)) BetsNotOk,
       COUNT(distinct(CASE WHEN STATUS = 'OK' then TICKETID  end)) TicketsOk,
       COUNT(distinct(CASE WHEN STATUS = 'CANCELED' then TICKETID  end)) TicketsNotOk,
       SUM(CASE WHEN STATUS = 'OK' then AMOUNT else 0 end) AmountOk,
       SUM(CASE WHEN STATUS = 'CANCELED' then AMOUNT  else 0 end) AmountNotOk
FROM T

Sqlfiddle

[结果]：

| BetsOK | BetsNotOk | TicketsOk | TicketsNotOk | AmountOk | AmountNotOk |
|--------|-----------|-----------|--------------|----------|-------------|
|      4 |         2 |         2 |            1 |       14 |          11 |