选择重复的记录并从mysql中的逗号分隔计数记录 [英] select duplicated record and count record from comma separated in mysql

问题描述

我需要查询以选择重复记录并计算重复记录总数，

I need a query to select duplicate records and count the total duplicate records,

Posted Records


PostID | Location

  1    | Delhi,Mumbai,Patna
  2    | Mumbai,Noida
  3    | Delhi
  4    | Mumbai,Noida

我想要这个结果

  Location  | Total
  Delhi     | 2
  Mumbai    | 3
  Patna     | 1
  Noida     | 2

推荐答案

第一件事是您应该规范化结构以摆脱逗号分隔的值，并使用另一个表将您的位置与您的职位表相关联，请参见

First thing is you should normalize your structure get rid of comma separated values and use another table to relate your locations with your posts table see Database normalization,for you current structure what you can do is get all locations from your table and insert them into new table then use aggregate function on your new table

CREATE TABLE locaions (cities CHAR(255)) ;

SET @S1 = CONCAT(
  "INSERT INTO locaions (cities) VALUES ('",
  REPLACE(
    (SELECT 
      GROUP_CONCAT(`Location`) AS DATA 
    FROM
      `posts`),
    ",",
    "'),('"
  ),
  "');"
) ;

PREPARE stmt1 FROM @s1 ;

EXECUTE stmt1 ;

这将在位置表中插入所有重复数据的位置，然后使用以下查询获取所需的计数

This will insert all the locations with repeated data in location table and then use below query to get your desired count

SELECT cities,count(*) 
FROM locaions 
group by cities

这篇关于选择重复的记录并从mysql中的逗号分隔计数记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！