将两个类似的查询结合在一起
问题描述:
我有两个独立工作的MySQL查询,我希望将它们组合在一起,以便返回三个值。将两个类似的查询结合在一起
查询1个检查多少帐户已被删除:
SELECT
COUNT(1) AS deleted_count,
SUBDATE(e.timestamp, INTERVAL WEEKDAY(e.timestamp) DAY) AS display_date
FROM
exit_reasons e
WHERE
e.timestamp>='$sixmonths'
GROUP BY
WEEKOFYEAR(e.timestamp)
ORDER BY
display_date ASC
LIMIT 26
这返回的日期,谁在这一周中删除
查询2个检查多少这些都随后再次报名人数:
SELECT
COUNT(1) AS date_count,
SUBDATE(e.timestamp, INTERVAL WEEKDAY(e.timestamp) DAY) AS display_date
FROM
exit_reasons e
LEFT JOIN
companies c on e.email=c.email
WHERE
e.timestamp>='$sixmonths' AND c.email IS NOT NULL
GROUP BY
WEEKOFYEAR(e.timestamp)
ORDER BY
display_date ASC
LIMIT 26
这会返回一个日期和星期删除的号码谁现在有一个新的帐户
我想它返回一个日期,然后数删除,并在一个查询再结合数,所以我尝试:
SELECT
COUNT(1) AS date_count,
SUBDATE(e.timestamp, INTERVAL WEEKDAY(e.timestamp) DAY) AS display_date,
date_count as rejoined_count from
(SELECT
COUNT(1) AS date_count,
SUBDATE(e.timestamp, INTERVAL WEEKDAY(e.timestamp) DAY) AS display_date
FROM
exit_reasons e2
LEFT JOIN
companies c on e.email=c.email
LEFT JOIN
companies_users cu on e.email=cu.email
WHERE
e2.timestamp>='$sixmonths' AND c.email IS NOT NULL
GROUP BY
WEEKOFYEAR(e.timestamp)
ORDER BY
display_date ASC
LIMIT 26)
FROM
exit_reasons e
WHERE
e.timestamp>='$sixmonths'
GROUP BY
WEEKOFYEAR(e.timestamp)
ORDER BY
display_date ASC
LIMIT 26
,但我得到一个语法错误 - 我怎么可以将这些查询组合在一起成为一个查询?
答
通过使用聚合函数以及一些条件逻辑(如CASE表达式),您应该能够将两个查询合并为一个查询:
SELECT
COUNT(1) AS deleted_count,
SUM(CASE WHEN c.email IS NOT NULL THEN 1 ELSE 0 END) as date_count,
SUBDATE(e.timestamp, INTERVAL WEEKDAY(e.timestamp) DAY) AS display_date
FROM exit_reasons e
LEFT JOIN companies c
on e.email=c.email
WHERE e.timestamp>='$sixmonths'
GROUP BY WEEKOFYEAR(e.timestamp)
ORDER BY display_date ASC
LIMIT 26;
See Demo。如果将c.email IS NOT NULL
移动到SUM(CASE..
中,则可以检查第二个查询,该查询允许您获取总数不为空的行。
答
我认为下面会做你想要什么:
SELECT COUNT(*) AS deleted_count,
COUNT(c.email) as date_count,
SUBDATE(e.timestamp, INTERVAL WEEKDAY(e.timestamp) DAY) AS display_date
FROM exit_reasons e LEFT JOIN
companies c
on e.email = c.email
WHERE e.timestamp >= '$sixmonths'
GROUP BY WEEKOFYEAR(e.timestamp)
ORDER BY display_date ASC
LIMIT 26;
倘若有人可以注册多个具有相同电子邮件一次,你应该改变count()
使用distinct
:
COUNT(DISTINCT e.email) as deleted_count,
COUNT(DISTINCT c.email) as date_count
工作过,感谢 – bhttoan 2014-09-03 18:17:10