Mysql查询(相同)不同的结果集返回不同的结果顺序

Question

我对 Mysql 世界很天真......:)我试图使用连接从表中查询，

我遇到结果集问题...表结构如下

下面...

VIDEO_XXXXX

| Field         | Type          | Null | Key | Default           | Extra                       |
| VIDEO_ID      | int(255)         
| TAG_NAME      | varchar(1000)
| SHOP_ID       | varchar(100)   FK
| LOVES          | int(255)      
| VIDEO_URL     | varchar(1000) 
| UPLOADED_DATE | 


PLACE_TABLE...

 SHOP_ID       | varchar(100) PK

查询看起来像..

   SELECT V.SHOP_ID
     FROM VIDEO_xxxx V  
     JOIN STUB_xxxx_TABLE S  ON S.SHOP_ID=V.SHOP_ID
    WHERE S.COUPON_STATUS='REDEEMED' 
 GROUP BY V.SHOP_ID 
 ORDER BY COUNT(S.COUPON_GENERATED_ID) DESC;

结果集如下...

+-----------+
| SHOP_ID   |
+-----------+
| 001       |
| 004       |
| 005       |
| 003       |
| 002       |

第二个查询是......

   SELECT V.SHOP_ID
     FROM SECOND_TABLE V  
     JOIN STUB_xxxx_TABLE S  ON S.SHOP_ID=V.SHOP_ID  
    WHERE S.COUPON_STATUS='REDEEMED'
 GROUP BY V.SHOP_ID 
 ORDER BY COUNT(S.COUPON_GENERATED_ID) DESC;

结果集是...

+--------+
| SHOP_ID|
+--------+
|001     |
|004     |
|003     |
|002     |
 005     |

我想知道为什么 shop_id 的顺序会打乱....:( :(任何帮助将不胜感激...:),

Answer 1

您可以将表达式 COUNT(S.COUPON_GENERATED_ID) 添加到 where 子句:

   SELECT V.SHOP_ID,COUNT(S.COUPON_GENERATED_ID)
     FROM SECOND_TABLE V  
     JOIN STUB_xxxx_TABLE S  ON S.SHOP_ID=V.SHOP_ID  
    WHERE S.COUPON_STATUS='REDEEMED'
 GROUP BY V.SHOP_ID 
 ORDER BY COUNT(S.COUPON_GENERATED_ID) DESC;

如果您不希望结果中出现此额外值，您可以使用子查询:

 SELECT SHOP_ID
   FROM (
          SELECT V.SHOP_ID,COUNT(S.COUPON_GENERATED_ID) AS CNT
            FROM SECOND_TABLE V  
            JOIN STUB_xxxx_TABLE S  ON S.SHOP_ID=V.SHOP_ID  
           WHERE S.COUPON_STATUS='REDEEMED'
        GROUP BY V.SHOP_ID 
        )
ORDER BY CNT DESC