Mysql - 仅对所选的不同行求和

Question

我有这个查询:

select date, id_transaction, id_reader, id_book, finalized from ebook_la_rentalbooks_meter_reading order by id_transaction;+---------------------+----------------+-----------+---------+-----------+| date                | id_transaction | id_reader | id_book | finalized |+---------------------+----------------+-----------+---------+-----------+| 2014-07-16 11:29:47 |            395 |       132 |     281 | N         || 2014-07-19 22:27:36 |            398 |        81 |     278 | N         || 2014-07-19 22:28:03 |            398 |        81 |     278 | N         || 2014-07-19 22:27:34 |            398 |        81 |     278 | N         || 2014-07-19 22:27:32 |            398 |        81 |     278 | N         || 2014-07-19 22:27:28 |            398 |        81 |     278 | N         || 2014-07-19 22:27:10 |            398 |        81 |     278 | N         || 2014-07-19 22:25:50 |            398 |        81 |     278 | N         || 2014-07-19 22:27:03 |            398 |        81 |     278 | N         || 2014-07-19 22:27:56 |            398 |        81 |     278 | N         || 2014-07-19 22:28:00 |            398 |        81 |     278 | N         || 2014-07-16 13:51:25 |            406 |       132 |     201 | Y         || 2014-07-16 11:29:43 |            406 |       132 |     201 | Y         || 2014-07-16 11:29:38 |            407 |       132 |     207 | N         || 2014-07-21 12:36:55 |            407 |       132 |     207 | N         || 2014-07-14 17:54:45 |            409 |       132 |     252 | Y         || 2014-07-16 11:29:25 |            409 |       132 |     252 | Y         || 2014-07-07 13:17:20 |            413 |        83 |     184 | N         || 2014-07-07 13:18:10 |            413 |        83 |     184 | N         || 2014-07-07 13:16:03 |            414 |        83 |     181 | N         || 2014-07-07 13:15:43 |            415 |        83 |     300 | N         || 2014-07-07 13:15:54 |            415 |        83 |     300 | N         || 2014-07-07 13:37:53 |            417 |        85 |     196 | N         |...

我尝试这样做:

SELECT     DISTINCT(A.id_transaction), A.id_book,     SUM( A.finalized="Y") AS books_read,     SUM( A.finalized="N") AS books_unread,     COUNT( DISTINCT A.id_transaction ) as books_read_unread     FROM ebook_la_rentalbooks_meter_reading AS A    WHERE A.id_customer=33     GROUP BY A.id_book     ORDER BY books_read DESC, books_unread DESC

我明白了:

+----------------+---------+------------+--------------+-------------------+| id_transaction | id_book | books_read | books_unread | books_read_unread |+----------------+---------+------------+--------------+-------------------+|            453 |      58 |          3 |            0 |                 1 ||            445 |      89 |          2 |            2 |                 2 ||            406 |     201 |          2 |            1 |                 2 ||            452 |     243 |          2 |            1 |                 2 ||            409 |     252 |          2 |            0 |                 1 ||            444 |     114 |          1 |            1 |                 2 ||            398 |     278 |          0 |           10 |                 1 ||            425 |     327 |          0 |            6 |                 3 ||            434 |     326 |          0 |            5 |                 2 ||            423 |     331 |          0 |            4 |                 2 ||            432 |      85 |          0 |            4 |                 3 ||            424 |      50 |          0 |            3 |                 1 ||            429 |     329 |          0 |            3 |                 2 ||            438 |     122 |          0 |            3 |                 2 ||            427 |     332 |          0 |            3 |                 1 ||            417 |     196 |          0 |            2 |                 1 ||            418 |     330 |          0 |            2 |                 1 ||            440 |     205 |          0 |            2 |                 1 ||            422 |     136 |          0 |            2 |                 1 ||            415 |     300 |          0 |            2 |                 1 ||            407 |     207 |          0 |            2 |                 1 ||            430 |     333 |          0 |            2 |                 1 ||            413 |     184 |          0 |            2 |                 1 ||            435 |      54 |          0 |            1 |                 1 ||            436 |     130 |          0 |            1 |                 1 ||            395 |     281 |          0 |            1 |                 1 ||            439 |      60 |          0 |            1 |                 1 ||            414 |     181 |          0 |            1 |                 1 ||            450 |     325 |          0 |            1 |                 1 ||            419 |     220 |          0 |            1 |                 1 ||            420 |     192 |          0 |            1 |                 1 |+----------------+---------+------------+--------------+-------------------+

错了!正在计算重复的“SUM id_transaction”，每个“id_transaction”应该只计算一条记录。如何解决这个问题？

非常感谢!!!

Answer 1

也许这就是你想要的:

SELECT MAX(A.id_transaction), A.id_book, 
       COUNT(DISTINCT CASE WHEN A.finalized = 'Y' THEN A.id_transaction END) AS books_read, 
       COUNT(DISTINCT CASE WHEN A.finalized = 'N' THEN A.id_transaction END) AS books_unread, 
       COUNT( DISTINCT A.id_transaction ) as books_read_unread 
FROM ebook_la_rentalbooks_meter_reading AS A
WHERE A.id_customer = 33 
GROUP BY A.id_book 
ORDER BY books_read DESC, books_unread DESC;