我有这个查询:
SELECT gamer_id,COUNT(*) AS sum, SUM(amount) as amount
FROM sales_list
WHERE rdate BETWEEN '2012-04-01' AND '2012-04-30' AND gamer_id NOT IN
(SELECT gamer_id FROM sales_list WHERE rdate < '2012-04-01' GROUP BY gamer_id)
GROUP BY gamer_id
这个查询给我打印了"2"结果,我只看"1"。
我有 1 个玩家在 4 月份存款 2 次,我不想仅仅为了计算存入的玩家总数而计算总存款。
有什么建议吗?
我真的不明白,但是您只想要游戏玩家的存款数量还是他另外存入的金额?!
这应该提供存款数量:
SELECT gamer_id, COUNT(gamer_id) AS sum, SUM(amount) as amount
FROM sales_list
WHERE rdate BETWEEN '2012-04-01' AND '2012-04-30'
GROUP BY gamer_id
HAVING COUNT(gamer_id)>0
编辑:
SELECT DISTINCT gamer_id
FROM sales_list
WHERE rdate BETWEEN '2012-04-01' AND '2012-04-30'
AND gamer_id NOT IN (SELECT gamer_id
FROM sales_list
WHERE rdate < '2012-04-01')
GROUP BY gamer_id
试试这个:
SELECT sl.gamer_id,COUNT(*) AS sum, SUM(sl.amount) as amount
FROM sales_list sl
WHERE sl.rdate BETWEEN '2012-04-01' AND '2012-04-30' AND sl.gamer_id NOT IN
(SELECT sl1.gamer_id FROM sales_list sl1 WHERE sl1.rdate < '2012-04-01' GROUP BY sl1.gamer_id)
GROUP BY sl.gamer_id