mysql - GROUP BY 的 SQL 语句

Question

我真的被一个 sql select 语句困住了。

这是我从下面的 sql 语句中得到的输出/结果:

我需要什么:我需要通过 msisdn 将 assignedVouchersNumber 和 usedVouchersNumber 列放在一行中。因此，例如，如果您可以看到“msisdn”723709656，那么现在有两行......第一行的 assignedVouchersNumber = 1，第二行的 assignedVouchersNumber = 1。

但我需要将它放在一行中，assignedVouchersNumber = 2。你现在知道问题出在哪里了吗？

SELECT eu.msisdn,
   eu.id as userId,

   sum(case ev.voucherstate when '1' then 1 else 0 end) as assignedVouchersNumber,
   sum(case ev.voucherstate when '2' then 1 else 0 end) as usedVouchersNumber,

   ev.extra_offer_id,
   ev.create_time,
   ev.use_time,
   ev.id as voucherId,
   ev.voucherstate
FROM   extra_users eu
   JOIN (SELECT sn.msisdn AS telcislo,
                stn.numberid
         FROM   stats_number sn
                JOIN stats_target_number AS stn
                  ON ( sn.numberid = stn.numberid )
         WHERE  stn.targetid = 1) xy
     ON eu.msisdn = xy.telcislo
   JOIN extra_vouchers AS ev
     ON ( eu.id = ev.extra_user_id )
WHERE  ev.create_time BETWEEN '2012-07-23 00:00:00' AND '2013-08-23 23:59:59'
   AND ev.use_time <= '2013-08-23 23:59:59'
   AND ev.use_time >= '2012-07-23 00:00:00'
   AND ev.voucherstate IN ( 1, 2 )
   AND Ifnull(ev.extra_offer_id IN( 2335, 3195, 30538 ), 1)
   GROUP BY eu.msisdn, ev.extra_offer_id, ev.voucherState
ORDER  BY eu.msisdn ASC  

Answer 1

对于相同的 msisdn 和 VouchersNumber，您有两个不同的 extra_offer_id。这就是你得到两行的原因。