Mysql按功能分组

Question

这是我的 table :

Id customer email        timestamp             store_id
1    1      abc@xx.com   2014-04-11 17:06:21   5416
2    0      xyz@xy.com   2014-04-11 15:05:23   5416
3    1      abc@xx.com   2014-04-11 17:55:34   5416
4    2      bca@bc.com   2014-04-11 17:55:34   5416

我的查询是:

SELECT *, count(*) as total FROM `tybo_orders` 
WHERE timestamp IN (SELECT max(timestamp) FROM `tybo_orders`
GROUP BY email) AND store_id='5416' GROUP BY email

customer 列中的值为 0 表示访客，大于 0 表示客户 ID。如果 customer 为 0，我想按 email 分组，否则按 customer 分组。怎么可能？

Answer 1

尝试这样

SELECT *, count(*) as total 
FROM `tybo_orders` 
WHERE timestamp IN (SELECT max(timestamp) FROM `tybo_orders` GROUP BY email) 
AND 
store_id='5416' 
GROUP BY 
case when customer=0 then customer else email end