使用GROUP_CONCAT作为输入选择位置 [英] SELECT WHERE IN with GROUP_CONCAT as input

问题描述

在继续操作之前，请先阅读以下内容:根据白名单过滤未过滤的表桌子

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

因此，我目前已按照参考链接所示设置了白名单表，并且遇到了所述表带来的另一个问题，即检查每列的唯一性.按照MySQL的规范，不可能将NULL列设置为UNIQUE，因此，我决定采用一种不同的解决方案，通过使用SELECT GROUP BY查询来检查行是否重复.

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)

当然，我可以只使用PHP并分解group_concat ID并重新选择它，但我想知道是否有可能在一个SQL查询调用中而不是在两个SQL查询调用中做到这一点.

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.

编辑:糟糕，上面的示例中有一个错误(xD处不小心使用了真实模式)

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

Edit2 :suddenly，我突然想到了为什么使事情变得复杂以及为什么不只是简单地使用它...

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

...但是仍然失败，因为您不能在两个NULL列上使用=.哦，到目前为止还很近>.<

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

致: Bill Karwin

To: Bill Karwin

这正是这里的问题.如果我在国家，地区，项目上设置了唯一键，然后执行以下SQL，则会发生这种情况.

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

但是，如果我包含任何通配符(也称为NULL)，则会发生这种情况.

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.

推荐答案

不喜欢此解决方案，但可行:-

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)

这篇关于使用GROUP_CONCAT作为输入选择位置的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！