dongliping003116 2017-12-06 23:04
浏览 542
已采纳

使用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 01: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)
    
    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论
编辑
预览

报告相同问题?

悬赏问题

  • ¥15 QGC打开没有地图显示,离线地图也不显示,如何解决?
  • ¥20 Android Studio 的 webview 与访问网络存在的限制
  • ¥15 某帖子的数据集不清楚来源,求帮助
  • ¥50 tc358743xbg寄存器配置
  • ¥15 idea构建mod报错无效的源发行版项目链接,如何解决?
  • ¥15 springboot中的路径问题
  • ¥80 App Store Connect 中设置了订阅项目,Xcode 中预览可以正确显示价格,真机测试却无法显示
  • ¥15 MATLAB的PIV算法问题
  • ¥15 RflySim例程学习:matlab编译报错
  • ¥20 谁来给我解答一下疑惑
手机看
程序员都在用的中文IT技术交流社区

程序员都在用的中文IT技术交流社区

专业的中文 IT 技术社区,与千万技术人共成长

专业的中文 IT 技术社区,与千万技术人共成长

关注【CSDN】视频号,行业资讯、技术分享精彩不断,直播好礼送不停!

关注【CSDN】视频号,行业资讯、技术分享精彩不断,直播好礼送不停!

客服 返回
顶部