计算同一会员表中 2 个日期之间的推荐人新会员

Question

我现在正在进行的项目需要一个参考系统（目前他们有 50K 成员）。我决定在成员表中添加 ref 和 ref_id 字段。

members表的结构；

id (int auto),
admin (enum (1,0)),
ref (enum (1,0)),
ref_id (int),
country_id (int),
city_id(int),
town_id(int),
totalRef (int),
fullName (varchar),
registrationDate (datetime)

我想列出两个日期之间有新成员的推荐人数据。我想提供更多详细信息，因此我还尝试在查询中添加国家/地区、城市和城镇。我尝试了以下查询，但考虑到加载需要很长时间，我认为这不是一个好方法；

SELECT m.id, m.fullName, m.country_id, m.city_id, m.town_id, m.totalRef,
(select name from country where country.id = m.country_id) as countryName,
(select name from city where city.id = m.city_id) as cityName,
(select name from town where town.id = m.town_id) as townName,
(select count(id) from members where members.ref_id = m.id AND ref_id > 0 AND registrationDate BETWEEN '2011.11.04 00:00:00' AND '2011.11.04 23:59:59') as newRef

FROM members as m 

WHERE

m.country_id = '224' AND 
m.city_id = '4567' AND
m.town_id = '78964' AND 
m.admin = '0' AND 
m.ref = '1' 

ORDER BY newRef DESC 
LIMIT 0, 25

如果您能帮助我解决这个问题，我将很高兴。先感谢您。

Answer 1

像这样的东西 -

SELECT
  m.id,
  m.fullName,
  m.country_id,
  m.city_id,
  m.town_id,
  m.totalRef,
  cnt.name countryName,
  ct.name cityName,
  t.name townName,
  m2.newRef
FROM members as m 
  LEFT JOIN country cnt
    ON cnt.id = m.country_id
  LEFT JOIN city ct
    ON ct.id = m.city_id
  LEFT JOIN town t
    ON t.id = m.town_id
  LEFT JOIN (
    SELECT ref_id, COUNT(id) newRef FROM members
      WHERE ref_id > 0 AND registrationDate BETWEEN '2011.11.04 00:00:00' AND '2011.11.04 23:59:59'
      GROUP BY ref_id
    ) m2
    ON m2.ref_id = m.id
WHERE
  m.country_id = '224' AND 
  m.city_id = '4567' AND
  m.town_id = '78964' AND 
  m.admin = '0' AND 
  m.ref = '1' 
ORDER BY
  newRef DESC 
LIMIT
  0, 25;