查询返回一个“额外"消息.记录.关于如何将其从查询结果中删除的任何建议? [英] Query returning one "extra" record. Any advice on how to remove it from the query results?
问题描述
我们有以下非常复杂的查询(至少对我们而言). 由于据我们所知,在MySQL上没有诸如INTERSECT之类的东西,因此我们想知道如何解决此问题:
We have the following, quite complex (at least for us) query. Since, as far as we know, there's no such thing as INTERSECT on MySQL, we are wondering how can we fix this:
(
SELECT GROUP_CONCAT(APA_T.district ORDER BY APA_T.district), t.name
FROM tbl_activity AS t
INNER JOIN tbl_activity_package AS ap ON t.id = ap.id_activity
INNER JOIN (
SELECT DISTINCT apa.district AS district, (
SELECT s1.id_activity_package
FROM tbl_activity_package_address s1
WHERE apa.district = s1.district
ORDER BY s1.id DESC
LIMIT 1
) AS idActivityPackage
FROM
tbl_activity_package_address apa
ORDER BY apa.district
) AS APA_T
ON ap.id = APA_T.idActivityPackage
GROUP BY t.name
ORDER BY APA_T.district
)
UNION DISTINCT
(
SELECT GROUP_CONCAT(DISTINCT apa2.district ORDER BY apa2.district), t2.name
FROM tbl_activity AS t2
INNER JOIN tbl_activity_package AS ap2 ON t2.id = ap2.id_activity
INNER JOIN tbl_activity_package_address AS apa2 ON ap2.id = apa2.id_activity_package
GROUP BY t2.name
ORDER BY apa2.district
)
#LIMIT 6, 6
以下是结果:
GROUP_CONCAT(APA_T.DISTRICT ORDER BY APA_T.DISTRICT) NAME
Beja,Faro,Setubal activity-1
Evora activity-2
Sintra activity-4
Braga,Sines activity-5
Santarem activity-6
Guarda,Matosinhos,Sagres activity-7
Lisboa,Montemor,Porto,Rio de Janeiro activity-8
Beja,Evora,Faro,Setubal activity-1
Faro activity-3
以下是我们希望的结果:
GROUP_CONCAT(APA_T.DISTRICT ORDER BY APA_T.DISTRICT) NAME
Beja,Faro,Setubal activity-1
Evora activity-2
Sintra activity-4
Braga,Sines activity-5
Santarem activity-6
Guarda,Matosinhos,Sagres activity-7
Lisboa,Montemor,Porto,Rio de Janeiro activity-8
Faro activity-3
问题
此行不应出现.任何活动都不应出现两次.
This line should NOT appear. No activity should appear twice.
Beja,Evora,Faro,Setubal activity-1
我们了解到UNION DISTINCT
不会将其删除,因为实际上:
Beja, Faro, Setubal
与Beja,Evora,Faro,Setubal
不同
但是,我们希望不将Evora
出现在第一个结果中.因此,就可以了,在UNION上进行的第一个查询就可以正常工作了.
We understand that the UNION DISTINCT
doesn't remove it, because indeed:
Beja, Faro, Setubal
IS DIFFERENT THAN Beja,Evora,Faro,Setubal
HOWEVER, we wish NOT to have Evora
to appear on the first result. So, it is OK as it is, the first query on the UNION does it's job as it should.
仍然,应删除出现的第二个活动1.
Still, that second activity-1 that appears, should be removed.
有关如何解决此问题的任何建议?
Any advice on how to solve this?
大图片 如您所见,这是一个巨大的选择,随着时间的流逝,它可能会变得越来越糟. 我们希望对活动进行无限滚动,并且该无限滚动的第一个结果应该来自发生在不同地区的活动.为什么?您可能会问,为什么我们不能按日期排序"呢?
THE BIG PICTURE As you can see, this is quite a huge select that will, perhaps, get worst and slow by time. We wish to have a INFINITE SCROLL of Activities, and the first results of that Infinite Scroll, should be from Activities happening on different districts. Why? Why can't we do it "order by date" or something, you may ask.
因为如果数据库后端用户确实插入了来自单个区域的最后20条记录,我们将在无限滚动优先列表结果上,只有该区域的活动显示我们没有比那个地区更多的东西了.
Because if a database back-end user do insert the last 20 records, all from one single district, we will have on the infinite scroll first list results, only activities from that district APPEARING that we don't have more than that district.
因此,关键是要在某个(复杂)订单上列出所有结果. :) 任何其他也许更好的方法都很好.
So, the point is to LIST ALL the results on a certain (complex) ORDER. :) Any other, perhaps better way, would be great.
http://sqlfiddle.com/#!2/37dd94/51
推荐答案
执行以下操作( SQL小提琴 )产生您想要的结果.我包装了并集,以便然后可以在name
字段上进行排序.如果您不希望这样做,则可以将其删除或在DistCon
字段上排序.
Does the below (SQL Fiddle) produce the results you are looking for. I wrapped the union so I could then sort on the name
field. If you don't want it that way then you can remove it or sort on the DistCon
field instead.
SELECT * FROM
(
SELECT GROUP_CONCAT(APA_T.district) AS DistCon, t.name
FROM tbl_activity AS t
JOIN tbl_activity_package AS ap ON t.id = ap.id_activity
JOIN
(
SELECT DISTINCT apa.district AS district,
(
SELECT s1.id_activity_package
FROM tbl_activity_package_address s1
WHERE apa.district = s1.district
ORDER BY s1.id DESC
LIMIT 1
) AS idActivityPackage
FROM
tbl_activity_package_address apa
ORDER BY apa.district
) AS APA_T
ON ap.id = APA_T.idActivityPackage
GROUP BY t.name
UNION
SELECT GROUP_CONCAT(apa.district), t.name
FROM tbl_activity AS t
JOIN tbl_activity_package AS ap ON t.id = ap.id_activity
JOIN tbl_activity_package_address AS apa ON ap.id = apa.id_activity_package
WHERE t.name NOT IN
(
SELECT DISTINCT t.name
FROM tbl_activity AS t
JOIN tbl_activity_package AS ap ON t.id = ap.id_activity
JOIN
(
SELECT DISTINCT apa.district AS district,
(
SELECT s1.id_activity_package
FROM tbl_activity_package_address s1
WHERE apa.district = s1.district
ORDER BY s1.id DESC
LIMIT 1
) AS idActivityPackage
FROM
tbl_activity_package_address apa
) AS APA_T
ON ap.id = APA_T.idActivityPackage
)
GROUP BY t.name
) AS Mm
ORDER BY Mm.name
这篇关于查询返回一个“额外"消息.记录.关于如何将其从查询结果中删除的任何建议?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!