多次并集后使用groupby的mysql

zaq34kh6 于 2021-06-25 发布在 Mysql

关注(0)|答案(4)|浏览(329)

我有一个相当复杂的mysql查询，包含多个 UNION 声明。我试图从最终输出中消除重复项，但并非所有列都相同（包括id）。所以我想用 GROUP BY 在“name”列中删除具有相同名称的条目。这是一个其他许多Map图层的Map混搭。一些地点标记出现在多个层上（即餐厅可能出现在“就餐”层、“酒吧”层和“家庭烹饪”层上，每个层都有不同的id）。
每个 UNION SELECT 语句只生成一行具有该位置的'name'，但在所有 UNION 我在同一个地方有多个条目。所以我觉得我需要处理 GROUP BY 在每次 UNION 已经发生了。
我将试着用这个陈述的简化版本来说明。。。

(
    SELECT l.id AS lid, m.markername AS markername, m.id AS mid, m.layer AS mlayer
    FROM layertable 
    INNER JOIN markertable 
    ON m.layer LIKE Concat('%"',l.id,'"%') 
    WHERE l.id='66' 
    ## RESULT INCLUDES Joes Place AND Eatery
)
UNION
(
    SELECT l.id AS lid, m.markername AS markername, m.id AS mid, m.layer AS mlayer
    FROM layertable 
    INNER JOIN markertable 
    ON m.layer LIKE Concat('%"',l.id,'"%') 
    WHERE l.id='82' 
    ## RESULT INCLUDES Joes Place AND Eatery
)
UNION
(
    SELECT l.id AS lid, m.markername AS markername, m.id AS mid, m.layer AS mlayer
    FROM layertable 
    INNER JOIN markertable 
    ON m.layer LIKE Concat('%"',l.id,'"%') 
    WHERE l.id='91' 
    ## RESULT INCLUDES Joes Place
)

ORDER BY markername ASC
LIMIT 10

结果看起来像。。。

| lid | markername | mid | mlayer               |
=================================================
| 66  | Joes Place | 10  | ["66","82","91"]     |
| 82  | Joes Place | 10  | ["66","82","91"]     |
| 91  | Joes Place | 10  | ["66","82","91"]     |
| 66  | Eatery     | 11  | ["66","82"]          |
| 82  | Eatery     | 11  | ["66","82"]          |

我想要的是。。。

| lid | markername | mid | mlayer               |
=================================================
| 91  | Joes Place | 10  | ["66","82","91"]     |
| 82  | Eatery     | 11  | ["66","82"]          |
``` `DISTINCT` 似乎不起作用，因为记录不完全相同。
我试着 `GROUP BY markername` 前后 `ORDER BY` 但不管怎样我都会得到一个语法错误信息。在个人内部应用 `SELECT` s没有帮助，因为每个表都只有一个place示例。
所以，重申我的问题：如何申请 `GROUP BY` 加入总名单 `UNION` 只输出唯一命名的地方？或者有其他方法来完成这个任务吗？
提前谢谢。

mysql union group-by Distinct

来源：https://stackoverflow.com/questions/50280137/mysql-using-group-by-after-multiple-unions

4条答案

按热度按时间

wooyq4lh1#

（这应该是一个评论，但有点长）
因为记录不完全相同
然后你需要更具体地说明你所说的复制品是什么意思。在您的示例中，您提取了max（l.id）-这是您的意图吗？
你为什么在这里用工会？你可以。。。

SELECT MAX(lid), markername, mid, mlayer
FROM ( 
  SELECT l.id AS lid, m.markername, m.id AS mid, m.layer AS mlayer
  FROM layertable 
  INNER JOIN markertable 
  ON m.layer LIKE Concat('%"',l.id,'"%') 
  WHERE l.id IN ('91', '82', '66')
  LIMIT 10
) AS ilv
GROUP BY markername, mid, mlayer

使用like is连接非常糟糕，这意味着您的数据没有规范化。为什么在查询中引用整数值？
（请注意，根据数据和可用索引的分布情况，subselect可能是多余的/加速或减慢的）。
你不认为应该围绕地理空间索引来组织Map数据吗？

赞(0）回复(0）举报 2021-06-25

gajydyqb2#

您可以使用group by和max（id）

select max(lid), markername, mid, mlayer 
  from (

  (
      SELECT l.id AS lid, m.markername AS markername, m.id AS mid, m.layer AS mlayer
      FROM layertable 
      INNER JOIN markertable 
      ON m.layer LIKE Concat('%"',l.id,'"%') 
      WHERE l.id='66' 
      ## RESULT INCLUDES Joes Place AND Eatery
  )
  UNION
  (
      SELECT l.id AS lid, m.markername AS markername, m.id AS mid, m.layer AS mlayer
      FROM layertable 
      INNER JOIN markertable 
      ON m.layer LIKE Concat('%"',l.id,'"%') 
      WHERE l.id='82' 
      ## RESULT INCLUDES Joes Place AND Eatery
  )
  UNION
  (
      SELECT l.id AS lid, m.markername AS markername, m.id AS mid, m.layer AS mlayer
      FROM layertable 
      INNER JOIN markertable 
      ON m.layer LIKE Concat('%"',l.id,'"%') 
      WHERE l.id='91' 
      ## RESULT INCLUDES Joes Place
  )

  ORDER BY markername ASC
  LIMIT 10 
  ) t
  group by markername, mid, mlayer

赞(0）回复(0）举报 2021-06-25

cdmah0mi3#

你可以做一个 GROUP BY 所有的结果，比如：

SELECT * FROM 
(
    SELECT l.id AS lid, m.markername AS markername, m.id AS mid, m.layer AS mlayer
    FROM layertable AS l
    INNER JOIN markertable 
    ON m.layer LIKE Concat('%"',l.id,'"%') 
    WHERE l.id='66' 
    ## RESULT INCLUDES Joes Place AND Eatery
)
UNION
(
    SELECT l.id AS lid, m.markername AS markername, m.id AS mid, m.layer AS mlayer
    FROM layertable AS l
    INNER JOIN markertable 
    ON m.layer LIKE Concat('%"',l.id,'"%') 
    WHERE l.id='82' 
    ## RESULT INCLUDES Joes Place AND Eatery
)
UNION
(
    SELECT l.id AS lid, m.markername AS markername, m.id AS mid, m.layer AS mlayer
    FROM layertable AS l
    INNER JOIN markertable 
    ON m.layer LIKE Concat('%"',l.id,'"%') 
    WHERE l.id='91' 
    ## RESULT INCLUDES Joes Place
)

ORDER BY markername ASC
LIMIT 10
) AS makernames
GROUP BY makername

或者您可以忽略导致结果不唯一的部分。比如：

(
    SELECT m.markername AS markername, m.id AS mid, m.layer AS mlayer
    FROM layertable AS l
    INNER JOIN markertable 
    ON m.layer LIKE Concat('%"',l.id,'"%') 
    WHERE l.id='66' 
    ## RESULT INCLUDES Joes Place AND Eatery
)
UNION
(
    SELECT m.markername AS markername, m.id AS mid, m.layer AS mlayer
    FROM layertable AS l
    INNER JOIN markertable 
    ON m.layer LIKE Concat('%"',l.id,'"%') 
    WHERE l.id='82' 
    ## RESULT INCLUDES Joes Place AND Eatery
)
UNION
(
    SELECT m.markername AS markername, m.id AS mid, m.layer AS mlayer
    FROM layertable AS l
    INNER JOIN markertable 
    ON m.layer LIKE Concat('%"',l.id,'"%') 
    WHERE l.id='91' 
    ## RESULT INCLUDES Joes Place
)

ORDER BY markername ASC
LIMIT 10

你不需要 l.id 在 SELECT 条款使其在 WHERE 条款。我想如果你愿意失去 lid 在某些行上，每个行只有一行 makername 那意味着你实际上不需要 lid 在所有的结果中。

赞(0）回复(0）举报 2021-06-25

rqenqsqc4#

只需一个查询即可完成所有操作：

SELECT max(l.id) AS lid, m.markername AS markername, m.id AS mid, m.layer AS mlayer
FROM layertable 
INNER JOIN markertable 
ON m.layer LIKE Concat('%"',l.id,'"%') 
  WHERE l.id in ('66','82','91')
GROUP BY m.markername, m.id, m.layer

赞(0）回复(0）举报 2021-06-25

我来回答

多次并集后使用groupby的mysql

4条答案

相关问题

热门标签

最新问答