查询返回一个“额外的"记录.关于如何从查询结果中删除它的任何建议?

2022-01-23 00:00:00 join union subquery mysql

我们有以下非常复杂的(至少对我们而言)查询.因为据我们所知，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 Fiddle)产生您正在寻找的结果.我包装了联合，因此我可以对 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

相关文章