mysql - group by 无法正确连接两个表

Question

我想获取每个事件的每种付款方式售出的门票数量。我有以下查询:

SELECT  count(distinct(a.performance_id)) as EventQuantity,
        sum(a.admission_count)  as TicketQuantity
FROM   vw_PrecioTipoZona_Paid as a 
WHERE 1 = 1
AND a.performance_id ='DED63133-A099-4949-AA57-13BBE9462BAF'
GROUP BY a.performance_id

我得到了这个结果，没问题:

EventQuantity   TicketQuantity
   1                    203

但是当将表与其他表连接时，结果是总和，在本例中，a.admission_count 乘以另一个表中的记录数。

有问题的查询是这样的:

SELECT      a.performance_id,
            count(distinct(a.performance_id)) as EventQuantity,
            sum(a.admission_count)  as TicketQuantity,
            b.payment_method as PaymentMethod
FROM   vw_PrecioTipoZona_Paid as a inner join vw_Payment_UserByPerformance as b
       on a.performance_id = b.performance_id
WHERE 
    1 = 1
    and a.performance_id ='DED63133-A099-4949-AA57-13BBE9462BAF'
    group by a.performance_id, b.payment_method

通过这个查询我得到这个结果:

EventQuantity   TicketQuantity  PaymentMethod
   1               10353            Cash
   1               5887             Card
   1               1624         MasterCardECommerce
   1                812           VisaEcommece

而这个结果是错误的，结果应该是:

EventQuantity   TicketQuantity  PaymentMethod
       1             111             Cash
       1              63             Card
       1              17       MasterCardECommerce
       1              8             VisaEcommece

vw_Payment_UserByPerformance View 结构如下:

performance_id  user_role_id    userrole_name   userrole_group  date_transaction    user_id user_name   owner_user_id   owner_user_name amount_adm_net  amount_req_net  amount_charge_charge    amount_total    amount_net  chargeTransaction   tax payment_method

vw_PrecioTipoZona_Paid View 结构如下:

performance_id  performance_name    performance_start_date  date_transaction    user_role_id    userrole_name   userrole_group  user_id user_name   price_type  price_zone  price_zone_priority admission_count NET charge1 charge2 charge3 charge4 charge5 GROSS

我必须进行子查询吗？这里的问题出在哪里呢？

Answer 1

MySQl 允许您错误地使用分组依据。您永远不应该使用在此查询中使用的技术。

SELECT      a.performance_id,
            count(distinct(a.performance_id)) as EventQuantity,
            sum(a.admission_count)  as TicketQuantity,
            b.payment_method as PaymentMethod
FROM   vw_PrecioTipoZona_Paid as a inner join vw_Payment_UserByPerformance as b
       on a.performance_id = b.performance_id
WHERE 
    a.performance_id ='DED63133-A099-4949-AA57-13BBE9462BAF'
    group by a.performance_id, b.payment_method

当您使用 group by 时，保证正确结果的唯一方法是按所有非聚合字段进行分组。所有其他数据库都使用这部分语法，因此不存在此问题。

如果这仍然没有给出正确的结果，那么你的意图与你所写的内容的具体细节存在问题。我们需要查看业务需求、表结构、表中的示例数据和示例结果，以帮助您找到正确的查询。

查看我在撰写本文时添加的其他详细信息，我认为您需要使用派生表。

SELECT      a.performance_id,
            count(a.performance_id) as EventQuantity,
            a.admission_count  as TicketQuantity,
            b.payment_method as PaymentMethod
FROM   (select performance_id, sum(admission_count) as Admissioncount vw_PrecioTipoZona_Paid 
WHERE a.performance_id ='DED63133-A099-4949-AA57-13BBE9462BAF'
group by performance_id )as a
 inner join vw_Payment_UserByPerformance as b
       on a.performance_id = b.performance_id
    group by a.performance_id, b.payment_method