我管理一个注册系统,人们可以在这里注册课程,我有以下查询来计算一些统计数据:
SELECT p.id_country AS id, c.name, COUNT(p.id_country) AS total
FROM participants p
LEFT JOIN countries c ON p.id_country = c.id
WHERE p.id_status NOT IN (3,4,13,14)
GROUP BY p.id_country
ORDER BY total DESC
这个查询运行良好,它准确地显示了每个国家的参与者数量。现在,我们的系统可以注册多个课程,每次注册都会在参与者表中插入一个新行。我知道,这不是一个理想的情况,但不幸的是,现在改变这种情况为时已晚。如果参与者注册了第二门(或第三门、第四门等(课程,那么他会使用相同的电子邮件地址。因此,在参与者表中,相同的电子邮件地址可以多次出现。
我想做的是更改这个查询,以便考虑到每个电子邮件地址只能使用一次。这个领域只是体育邮件,我想我应该和DISTINCT做点什么来实现这一点。但无论我尝试什么,它要么给我非常奇怪的结果,要么给我一个错误。
有可能这样做吗?
尽量不要在查询中混合使用distinct和group-by。你做也会得到同样的结果
select distinct p.id_country from participants
比做
select p.id_country from participants group by p.id_country
你需要的是过滤掉重复:
SELECT p.id_country AS id, c.name, COUNT(p.id_country) AS total
FROM participants p
LEFT JOIN countries c ON p.id_country = c.id
WHERE p.id_status NOT IN (3,4,13,14)
and not exists
(select email from participants p2 where p1.email=p2.email and p1.id>p2.id)
GROUP BY p.id_country
ORDER BY total DESC
这将只计算一次电子邮件,不计算具有重复电子邮件的帐户的更新IDS。
在表上添加UNIQUE
约束怎么样?
ALTER TABLE participants ADD CONSTRAINT part_uq UNIQUE (email)
SELECT
p.id_country AS id,
c.name,
COUNT(p.id_country) AS total
FROM
(select p.mail, max(id_country) as id_country from participants where p.id_status not in (3,4,13,14) group by p.mail) p
LEFT JOIN countries c ON p.id_country = c.id
GROUP BY
p.id_country
ORDER BY
total DESC
我使用max(id_country)
来处理一个电子邮件地址具有更多国家/地区的情况。如果设计无法实现,则可以将id_country
子句移到group by
子句。