mysql - mysql 查询中的计数不正确，因为使用数据透视表 - 将行转换为列

Question

由于数据透视表的使用，我有以下查询，该查询返回错误的计数(附加值表中的 3-4 行导致计数不正确)

SQL:-

select
     count(o.model_id) as qty,
    o.model_id,
    bd.brand_name,
    pd.product_name,
             MAX(IF(oinfokey.name = 'Colour'    , oinfokeyvalue.value , NULL)) AS colourName,
             MAX(IF(oinfokey.name = 'Grade'     , oinfokeyvalue.value , NULL)) AS gradeName,
             MAX(IF(oinfokey.name = 'Network'   , oinfokeyvalue.value , NULL)) AS networkName,
             MAX(IF(oinfokey.name = 'Condition' , oinfokeyvalue.value , NULL)) AS conditionName
From
    `order` o 
INNER JOIN
    product pd
ON
    pd.id  = o.model_id
INNER JOIN
    brand bd
ON
    bd.id  = pd.brand_id
INNER JOIN
    order_additional_information oinfo
ON
    oinfo.order_id  =  o.id
INNER JOIN
    order_additional_information_key oinfokey
ON
    oinfokey.id  = oinfo.order_additional_information_key_id
INNER JOIN
    order_additional_information_value oinfokeyvalue
ON
    oinfokeyvalue.id  = oinfo.order_additional_information_value_id     

GROUP BY 
    o.model_id  

输出是:-

qty model_id    brand_name  product_name    colourName  gradeName   networkName conditionName
3   320 LG  KP235   Brown   Brand New   Unknown 
3   393 Blackberry  Curve 8520  Black   14 Day  3 (Three)   
4   854 Apple   iPhone 4S 16GB  Green   Brand New   Orange  POT - GOOD LCD
3   1087    Apple   iPad 4 64GB WiFi    Bronze  Grade B Unknown 
3   1182    Samsung Ch@t 357 S3570  Black   Grade B Unlocked    
6   1713    Nokia   5500 Sport  Blue    Grade C Vecton  

如何正确从订单表中获取计数，计数结果应该是:-

Count - Model ID
1 - 320,
1 - 393,
2 - 854,
1 - 1087,
1 - 1182,
2 - 1713

Answer 1

在选择中使用DISTINCT:

count(DISTINCT o.id) as qty,