MySQL Group_Concat重复值 [英] MySQL Group_Concat Repeating Values
问题描述
我正在开发一个名为 PHP-Bouncer 的开源项目,我正在为它编写MySQL查询时遇到问题.基本上,我们有三个表:BouncerRoles,PageInRole和BouncerPageOverrides. BouncerRoles包含访问级别,另外两个表通过外键链接回BouncerRoles,并提供多个附加数据条目.我编写了以下查询,以尝试一次提取我需要的所有角色数据:
I am working on an open source project called PHP-Bouncer, and I'm having issues with a MySQL Query I am writing for it. Basically we have three tables: BouncerRoles, PageInRole, and BouncerPageOverrides. BouncerRoles contains access levels, and the other two tables link back to BouncerRoles via Foreign Key and provide multiple entries of additional data. I have written the following query to attempt to pull all of the role data I need all at once:
select BouncerRoles.RoleID, BouncerRoles.RoleName,
GROUP_CONCAT(PageInRole.PageName separator '|') as ProvidedPages,
GROUP_CONCAT(CONCAT(BouncerPageOverrides.OverriddenPage,'&',BouncerPageOverrides.OverridingPage) separator '|') as OverriddenPages
from BouncerRoles join PageInRole on BouncerRoles.RoleID = PageInRole.RoleID
join BouncerPageOverrides on BouncerRoles.RoleID = BouncerPageOverrides.RoleID
group by BouncerRoles.RoleID;
此查询的目标是提供RoleID,RoleName,提供页面的以竖线分隔的列表和替代的竖线分隔的列表(以overriddenpage& overridingpage的形式).除查询的最后一列外,其他所有内容都可以正常工作,查询的最后一遍如此重复(以CSV格式输出):
The Goal of this query is to provide the RoleID, RoleName, a pipe delimited list of provided pages, and a pipe delimited list of overrides (in the form of overriddenpage&overridingpage). Everything works except the last column of the query, which repeats the entries it finds over and over like this (output in CSV format):
RoleID,RoleName,ProvidedPages,OverriddenPages
2,Exchange,exchange-how.php|exchange-support.php|exchange.php|premium-promo.php|exchange-resorts.php|premiumplus-promo.php|exchange-deposit.php|exchange-requestdestination.php,whyexchange.php&exhange.php|whyexchange.php&exhange.php|whyexchange.php&exhange.php|whyexchange.php&exhange.php|whyexchange.php&exhange.php|whyexchange.php&exhange.php|whyexchange.php&exhange.php|whyexchange.php&exhange.php
3,Premium,premiumplus-promo.php|premium-cruises.php|premium-resorts.php|premium-condohome.php|premium-hotelaircar.php|premium.php|premium-restaurants.php|premium-overview.php,premium-promo.php&premium.php|premium-promo.php&premium.php|premium-promo.php&premium.php|premium-promo.php&premium.php|premium-promo.php&premium.php|premium-promo.php&premium.php|premium-promo.php&premium.php|premium-promo.php&premium.php
4,"Premium Plus",premiumplus-exclusiveescapes.php|premiumplus.php|premiumplus-overview.php|premiumplus-concierge.php|premiumplus-airportlounge.php,premiumplus-promo.php&premiumplus.php|premiumplus-promo.php&premiumplus.php|premiumplus-promo.php&premiumplus.php|premiumplus-promo.php&premiumplus.php|premiumplus-promo.php&premiumplus.php
我在查询中是否做错了什么导致这种情况?
Is there something I've done wrong in my query to cause this?
推荐答案
您可能正在将一个表与具有1..n关系的两个表连接在一起,从而产生重复的结果.
You are probably joining a table with two tables on 1..n relationships, producing duplicate results.
-
使用
GROUP_CONCAT( DISTINCT ...)
或
使用两个子查询:在每个子查询中,对两个表中的每个表均使用GROUP_CONCAT()
和group by.然后将两个子查询和主表连接起来.
Use two subqueries: in each one use GROUP_CONCAT()
with group by on each of the 2 tables. Then join the two subqueries and the main table.
这篇关于MySQL Group_Concat重复值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!