在MYSQL表中查找重复项，其中数据位于多个表中（需要多个条件）

My knowledge of MYSQL is rather basic, I would appreciate help with the following:

我对MYSQL的了解非常基础,我将非常感谢以下方面的帮助:

subrecords schema

id | record | element | title | name | type | value

The above table stores data submitted via a web form.

上表存储了通过Web表单提交的数据。

• Each row is one field (i.e. "phone number", "email", "subject" etc..) where "title" is the label of the field and "value" is the content of the submitted field

每行是一个字段(即“电话号码”,“电子邮件”,“主题”等)。其中“标题”是字段的标签,“值”是提交字段的内容

• the "record" identifies each unique submitted form, it is a foreign key of the "records" table (records.id) clarified below (the same form submitted several times generates multiple unique records)

“记录”标识每个唯一提交的表单,它是“记录”表(records.id)的外键,下面说明(同一表单提交多次生成多个唯一记录)

• to know which form was submitted we need to look at another table

要知道提交了哪个表单,我们需要查看另一个表

records schema

id | submitted | form | title | name

• id is unique to each submission (not unique to each form, the same form submitted multiple times generates multiple unique ids)

id对于每个提交都是唯一的(对于每个表单不是唯一的,多次提交的相同表单会生成多个唯一ID)

• form is a foreign key of another table (forms.id) that defines the structure of the form (all its fields etc..) and does not really simplify retrieving the data I need.

form是另一个表(forms.id)的外键,它定义了表单的结构(所有字段等等),并没有真正简化检索我需要的数据。

• records.name is a text string that identifies each unique form, this is needed to select the form we are looking for

records.name是一个标识每个唯一表单的文本字符串,这是选择我们要查找的表单所必需的

I need to find all users (by email) that submitted the same form more than once, here are some further clarifications:

我需要找到多次提交相同表单的所有用户(通过电子邮件发送),这里有一些进一步的说明:

• all email addresses (subrecords.value where subrecords.title = 'email')

所有电子邮件地址(subrecords.value where subrecords.title ='email')

• that have more than one of the same form submitted (identified by records.name = "string-form-name").

提交了多个相同表单(由records.name =“string-form-name”标识)。

• subrecords.record = record.id is needed simply to join the tables (each form submission is unique and generates a new record.id thus not helpful to identify neither the user nor the form).

只需加入表格就可以使用subrecords.record = record.id(每个表单提交都是唯一的,并生成一个新的记录。因此无法识别用户和表单)。

• records.form is a foreign key = forms.id (it does not seem useful as it it complicates the query by needing to look into yet another table, it seems simpler to use records.name to identify the form)

records.form是一个外键= forms.id(它似乎没用,因为它需要查看另一个表使查询复杂化,使用records.name来识别表单似乎更简单)

So far thanks to another user I have this:

到目前为止,感谢另一位用户,我有这个:

SELECT value     as email
      ,record
      ,COUNT(*)  as form_count
  FROM subrecords
 WHERE title = 'email'
   AND record IN (SELECT id
                    FROM records
                   WHERE name = 'form_name'
                 )
 GROUP BY value
         ,record
HAVING COUNT(*) > 1

It returns an empty value, but I am unable to narrow down how to improve this to make it work. Thank you

它返回一个空值,但我无法缩小如何改进它以使其工作。谢谢

1 个解决方案

#1

I think the problem is that you are grouping by record. A given record only has one form.

我认为问题在于你是按记录进行分组。给定记录只有一种形式。

However, your query would be improved by switching from in to a join. Also, I'm not clear if you have a specific form name in mind. If so, then add and r.name = 'form_name' to the on clause:

但是,通过从in切换到join,可以改善查询。另外,我不清楚你是否有一个特定的表格名称。如果是,则将on和r.name ='form_name'添加到on子句:

SELECT r.form, s.value as email, COUNT(distinct s.record)  as form_count
FROM subrecords s join
     records r
     on s.record = r.id
WHERE s.title = 'email'
GROUP BY s.value, r.form
HAVING form_count > 1;