问题：
如何在具有SUM的多连接查询中包含DISTINCT
谭富
下面的查询几乎给了我正确的计数，除了它包括具有重复p.promo_code和子值的行。如何使用DISTINCTp.promo_code查询？
我已经尝试了一些我在SO上看到的用于单连接查询的解决方案，比如---sum*COUNT（DISTINCT p.promo_code，submitid）/COUNT（*）
不走运。以下是完整的查询：
SELECT
SUM(CASE WHEN ISNULL(p.promo_code)
AND h.account_number = c2.account_number THEN 1 ELSE 0 END) as 'no_match_acct_match',
SUM(CASE WHEN ISNULL(p.promo_code)
AND (h.account_number <> c2.account_number OR ISNULL(c2.account_number)) THEN 1 ELSE 0 END) as 'no_match',
SUM(CASE WHEN p.promo_code IS NOT NULL
AND c.full_name = CONCAT(h.firstname,' ',h.lastname)
AND p.start_date > @today THEN 1 ELSE 0 END) as 'future_name',
SUM(CASE WHEN p.promo_code IS NOT NULL
AND c.full_name = CONCAT(h.firstname,' ',h.lastname)
AND p.start_date <= @today
AND p.`status` = 'REDEEMED' THEN 1 ELSE 0 END) as 'redeemed_name',
SUM(CASE WHEN p.promo_code IS NOT NULL
AND c.full_name = CONCAT(h.firstname,' ',h.lastname)
AND p.start_date <= @today
AND p.`status` <> 'REDEEMED'
AND p.end_date < @today
AND p.end_date + 259200 >= @today THEN 1 ELSE 0 END) as 'buffered_name',
SUM(CASE WHEN p.promo_code IS NOT NULL
AND c.full_name = CONCAT(h.firstname,' ',h.lastname)
AND p.start_date <= @today
AND p.`status` <> 'REDEEMED'
AND p.end_date < @today
AND p.end_date + 259200 < @today THEN 1 ELSE 0 END) as 'expired_name',
SUM(CASE WHEN p.promo_code IS NOT NULL
AND c.full_name = CONCAT(h.firstname,' ',h.lastname)
AND p.start_date <= @today
AND p.`status` <> 'REDEEMED'
AND p.end_date >= @today THEN 1 ELSE 0 END) as 'current_name',
SUM(CASE WHEN p.promo_code IS NOT NULL
AND c.full_name <> CONCAT(h.firstname,' ',h.lastname)
AND p.start_date > @today
AND (r.resolve_status = 0 OR ISNULL(r.resolve_status)) THEN 1 ELSE 0 END) as 'future_noname',
SUM(CASE WHEN p.promo_code IS NOT NULL
AND c.full_name <> CONCAT(h.firstname,' ',h.lastname)
AND p.start_date <= @today
AND p.`status` = 'REDEEMED' THEN 1 ELSE 0 END) as 'redeemed_noname',
SUM(CASE WHEN p.promo_code IS NOT NULL
AND c.full_name <> CONCAT(h.firstname,' ',h.lastname)
AND p.start_date <= @today
AND p.`status` <> 'REDEEMED'
AND p.end_date < @today
AND p.end_date + 259200 >= @today THEN 1 ELSE 0 END) as 'buffered_noname',
SUM(CASE WHEN p.promo_code IS NOT NULL
AND c.full_name <> CONCAT(h.firstname,' ',h.lastname)
AND p.start_date <= @today
AND p.`status` <> 'REDEEMED'
AND p.end_date < @today
AND p.end_date + 259200 < @today THEN 1 ELSE 0 END) as 'expired_noname',
SUM(CASE WHEN p.promo_code IS NOT NULL
AND c.full_name <> CONCAT(h.firstname,' ',h.lastname)
AND p.start_date <= @today
AND p.`status` <> 'REDEEMED'
AND p.end_date >= @today THEN 1 ELSE 0 END) as 'current_noname'
FROM helpdesk h
FORCE INDEX (help_status)
LEFT JOIN promo_codes p
ON h.promo_code = p.promo_code
LEFT JOIN customer_promocode cp
ON p.promo_code = cp.promo_code
LEFT JOIN customers c
ON cp.customer_number = c.customer_number
LEFT JOIN customers c2
ON h.account_number = c2.account_number
LEFT OUTER JOIN redeem_queue r
ON p.promo_code = r.promo_code
WHERE
h.help_status = 0
AND h.in_view_by = ''
AND (p.start_date >= UNIX_TIMESTAMP(SUBDATE(CURDATE(), INTERVAL 1 YEAR))
OR p.start_date IS NULL)
AND (ISNULL(p.promo_code) OR ISNULL(r.promo_code))
它运行得很快，解释计划看起来不错，但由于数据重复，目前有2个计数关闭。
任何和所有的帮助都将不胜感激。
共有1个答案
蒋星驰
您应该在这些列上使用group by。
SELECT
SUM(
CASE WHEN ISNULL(p.promo_code)
AND h.account_number = c2.account_number THEN 1 ELSE 0 END
) as 'no_match_acct_match',
SUM(
CASE WHEN ISNULL(p.promo_code)
AND (
h.account_number <> c2.account_number
OR ISNULL(c2.account_number)
) THEN 1 ELSE 0 END
) as 'no_match',
SUM(
CASE WHEN p.promo_code IS NOT NULL
AND c.full_name = CONCAT(h.firstname, ' ', h.lastname)
AND p.start_date > @today THEN 1 ELSE 0 END
) as 'future_name',
SUM(
CASE WHEN p.promo_code IS NOT NULL
AND c.full_name = CONCAT(h.firstname, ' ', h.lastname)
AND p.start_date <= @today
AND p.`status` = 'REDEEMED' THEN 1 ELSE 0 END
) as 'redeemed_name',
SUM(
CASE WHEN p.promo_code IS NOT NULL
AND c.full_name = CONCAT(h.firstname, ' ', h.lastname)
AND p.start_date <= @today
AND p.`status` <> 'REDEEMED'
AND p.end_date < @today
AND p.end_date + 259200 >= @today THEN 1 ELSE 0 END
) as 'buffered_name',
SUM(
CASE WHEN p.promo_code IS NOT NULL
AND c.full_name = CONCAT(h.firstname, ' ', h.lastname)
AND p.start_date <= @today
AND p.`status` <> 'REDEEMED'
AND p.end_date < @today
AND p.end_date + 259200 < @today THEN 1 ELSE 0 END
) as 'expired_name',
SUM(
CASE WHEN p.promo_code IS NOT NULL
AND c.full_name = CONCAT(h.firstname, ' ', h.lastname)
AND p.start_date <= @today
AND p.`status` <> 'REDEEMED'
AND p.end_date >= @today THEN 1 ELSE 0 END
) as 'current_name',
SUM(
CASE WHEN p.promo_code IS NOT NULL
AND c.full_name <> CONCAT(h.firstname, ' ', h.lastname)
AND p.start_date > @today
AND (
r.resolve_status = 0
OR ISNULL(r.resolve_status)
) THEN 1 ELSE 0 END
) as 'future_noname',
SUM(
CASE WHEN p.promo_code IS NOT NULL
AND c.full_name <> CONCAT(h.firstname, ' ', h.lastname)
AND p.start_date <= @today
AND p.`status` = 'REDEEMED' THEN 1 ELSE 0 END
) as 'redeemed_noname',
SUM(
CASE WHEN p.promo_code IS NOT NULL
AND c.full_name <> CONCAT(h.firstname, ' ', h.lastname)
AND p.start_date <= @today
AND p.`status` <> 'REDEEMED'
AND p.end_date < @today
AND p.end_date + 259200 >= @today THEN 1 ELSE 0 END
) as 'buffered_noname',
SUM(
CASE WHEN p.promo_code IS NOT NULL
AND c.full_name <> CONCAT(h.firstname, ' ', h.lastname)
AND p.start_date <= @today
AND p.`status` <> 'REDEEMED'
AND p.end_date < @today
AND p.end_date + 259200 < @today THEN 1 ELSE 0 END
) as 'expired_noname',
SUM(
CASE WHEN p.promo_code IS NOT NULL
AND c.full_name <> CONCAT(h.firstname, ' ', h.lastname)
AND p.start_date <= @today
AND p.`status` <> 'REDEEMED'
AND p.end_date >= @today THEN 1 ELSE 0 END
) as 'current_noname'
FROM
helpdesk h FORCE INDEX (help_status)
LEFT JOIN promo_codes p ON h.promo_code = p.promo_code
LEFT JOIN customer_promocode cp ON p.promo_code = cp.promo_code
LEFT JOIN customers c ON cp.customer_number = c.customer_number
LEFT JOIN customers c2 ON h.account_number = c2.account_number
LEFT OUTER JOIN redeem_queue r ON p.promo_code = r.promo_code
WHERE
h.help_status = 0
AND h.in_view_by = ''
AND (
p.start_date >= UNIX_TIMESTAMP(SUBDATE(CURDATE(), INTERVAL 1 YEAR))
OR p.start_date IS NULL
)
AND (
ISNULL(p.promo_code)
OR ISNULL(r.promo_code)
)
group by
p.promo_code,
submitid
类似资料：
-
问题内容： 我想在两个查询中 求和* 而不是在以下查询中使用 dis_max 。如何修改此查询来实现？ * 问题答案： 实现此目的的一种方法是通过使用子句的布尔查询 例：
-
问题内容： 请帮助我处理使用带有GROUP和SUM的LINQ进行查询的问题。 我希望： 从db.MYDATABASE中获取前25个项目 将所有结果按bs.PRODCODE分组 按每个bs的总和排序。 公司是“我的公司” 然后将数据管道到我的对象中 我很困惑，因为一旦我将其添加到混音中，我的变量就变得无用了。 问题答案： 我很困惑，因为一旦我将组添加到混合中，我的bs变量就变得无用了。 是的，因为您
-
我开始学习JPA，并基于我在SQL Server中测试的以下本机SQL实现了一个使用JPA查询的示例： 根据上面的SQL，我构造了以下JPQL查询： 正如您所看到的，我仍然缺少原始查询中的条件。我的问题是，我怎样才能把它放入我的JPQL中？
-
我是spring boot新手，需要关于具有多个内部连接的自定义JPA查询的帮助。基本上，我需要将以下SQL查询转换为JPA查询： 下面是代码结构： 公司实体 软件库 认证计数信号I mpl
-
我正在尝试转换以下查询： 到标准Hibernate查询，但肯定错过了一些东西，因为我得到了以下错误： 当我尝试时： 很抱歉，表/列命名错误，但这是敏感数据。
-
我有一个表“Quote”，映射在hibernate中，它有一个由整数id和日期组成的复合键，还有几个附加列。我想编写一个条件查询，它使用DetachedCriteria来获取每个id中日期最长的行。 在sql中，我可能会编写一个查询，比如 在hibernate中，我认为可以像这样为“group by”子查询创建DetachedCriteria（其中Quote是映射表的类，“Qid”是键的复合id类