MySQL查询(下同)将返回不同的结果集
问题描述:
我天真到MySQL世界.... :)我试图使用连接表来查询不同的结果顺序,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 |
我不知道为什么order_shop_id洗牌.... :(:(任何帮助将不胜感激... :),
答
您可以添加表达式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
即使我选择shop_id它返回集合不同.. 。::( – goodyzain 2014-10-20 12:39:11
我看到你正在从不同的表格中选择...... VIDEO_xxxx和SECOND_TABLE是同一张表吗? – 2014-10-20 12:41:24
不,他们是不同的:(是因为结构... ?? – goodyzain 2014-10-20 12:43:53