dongliping003116 2017-12-07 07:04
浏览 538
已采纳

使用GROUP_CONCAT作为输入选择WHERE IN

Please read this before continuing: Filter an unfiltered table against a whitelist table

So, I currently have a whitelist table set up as shown in the referenced link, and I'm encountering yet another issue brought up by said table, that is, to check the UNIQUENESS of each column. As MySQL's specification, it is not possible to set NULL column as UNIQUE, so, I've decided to come up with a different solution to check if rows are duplicated or not by using a SELECT GROUP BY query as follows.

SELECT GROUP_CONCAT(ID) AS IDs, country, region, item, count(*) AS amount
FROM whitelist

Now, to check if the item is duplicated, I've warpped it on top of another layer.

SELECT IDs, country, region, item, amount
FROM (SELECT GROUP_CONCAT(ID) AS IDs, country, region, item, count(*) AS amount
      FROM whitelist) tmp
WHERE amount > 1

Still works fine as intended, but the question starts here.

Is it possible for me to use this data, and RE-SELECT the whitelist table so I can get each entry as a row with something like ...

SELECT ID, country, region, item
FROM whitelist
WHERE ID IN (SELECT group_concat(ID)
               FROM (SELECT group_concat(ID) AS ID, country, region, item, COUNT(*) AS AMOUNT
                       FROM whitelist
                      GROUP BY country, region, item) tmp
              WHERE AMOUNT > 1)

Of course, I could just use PHP and explode the group_concat IDs and re-select it, but I'm wondering if it's possible to do it in one SQL query call instead of two.

Edit: Oops, the example above had an error in it (accidentally used real schema there xD)

Edit2: Doh, I suddenly thought why complicate things and why not just simply go with this ...

SELECT wl1.ID, wl1.country, wl1.region, wl1.item, wl1.reason
  FROM whitelist wl1, 
       (SELECT country, region, item
          FROM whitelist
         GROUP BY country, region, item
        HAVING count(*) > 1) wl2
 WHERE wl1.country = wl2.country AND
       wl1.region = wl2.region AND
       wl1.item = wl2.reason

... but still fails too, because you cannot use = on two NULL columns. Urgh, so close yet so far >.<

To: Bill Karwin

That is exactly the issue here. If I set a unique key on country, region, item, and I perform the following SQL, this will happen.

INSERT INTO whitelist(country, region, item) VALUES ('Taiwan', 'Asia', 'PC');
INSERT INTO whitelist(country, region, item) VALUES ('Taiwan', 'Asia', 'PC');
-- Would fail due to UNIQUE check

However, if I include any of the wildcards, aka NULL, and this would happen.

INSERT INTO whitelist(country, region, item) VALUES (NULL, 'Asia', 'Rice');
INSERT INTO whitelist(country, region, item) VALUES (NULL, 'Asia', 'Rice');
-- Would succeed due to UNIQUE does not check NULL columns.

Hence the idea of this post is to list all repeating whitelist in a list so that the operator can decide what to keep and what to delete.

  • 写回答

1条回答 默认 最新

  • duanpaxin3531 2017-12-07 09:14
    关注

    Not keen on this solution, but viable:-

    SELECT a.ID, 
            a.country, 
            a.region, 
            a.item
    FROM whitelist a
    INNER JOIN 
    (
        SELECT group_concat(ID) AS ID, USERNAME, COMPNAME, PUBLISHER, NAME, VERSION, COUNT(*) AS AMOUNT
        FROM software_checklist
        GROUP BY USERNAME, COMPNAME, PUBLISHER, NAME, VERSION 
        HAVING AMOUNT > 1
    ) tmp
    ON FIND_IN_SET(a.ID, tmp.ID)
    
    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论

报告相同问题?

悬赏问题

  • ¥30 gradle环境下javafx项目如何使用druid连接池
  • ¥15 服务器打印水晶报表问题
  • ¥15 初学者用plt报错,求解答
  • ¥18 深度学习tensorflow1,ssdv1,coco数据集训练一个模型
  • ¥100 关于注册表摄像头和麦克风的问题
  • ¥30 代码本地运行正常,但是TOMCAT部署时闪退
  • ¥15 关于#python#的问题
  • ¥15 主机可以ping通路由器但是连不上网怎么办
  • ¥15 数据库一张以时间排好序的表中,找出多次相邻的那些行
  • ¥50 关于DynamoRIO处理多线程程序时候的问题