在MYSQL表中查找数据在多个表中的重复（需要多个条件） [英] Finding duplicates in MYSQL table where data is in multiple tables (multiple conditions needed)

问题描述

subrecords schema

id |记录|元素|标题|名称|类型|价值

上表显示通过网络表单提交的数据。

• 每行都是一个字段（即电话号码，电子邮件，主题等），其中标题是字段的标签，值是提交的内容字段


• 记录标识每个唯一的提交表单，它是下面说明的记录表（records.id）的外键（同一表单提交多次生成多个唯一的记录）


• 知道提交的表单需要查看另一个表格

记录架构

id |提交|形式|标题|名称

• id对每个提交都是唯一的（每个表单都不是唯一的，多次提交相同的表单生成多个唯一的ID）


• 表单是另一个表（forms.id）的外键，它定义了表单的结构（所有字段等），并不真正简化检索我需要的数据。


• records.name是一个标识每个唯一表单的文本字符串，需要选择我们正在寻找的表单






我需要找到所有提交相同表单的用户（通过电子邮件）多次，这里有一些进一步的说明：






• 所有电子邮件地址（subrecords.value where subrecords.title ='email'）


• ，其中提交了多个相同的表单（由records.name =string-form-name）。


• subrecords.record = record.id只需要加入表（每个表单提交都是唯一的，并且生成一个新的record.id，因此无法识别用户和表单） 。


• records.form是一个外键= forms.id（它似乎没有用，因为它使查询复杂化，需要查看另一个表，使用记录似乎更简单。名字来标识表单）






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

  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 
  

它返回一个空值，但是我无法缩小如何改进这一点工作。
谢谢

解决方案

我认为问题是你按 code>。给定的记录只有一种形式。





但是，通过从中的切换到加入。此外，我不清楚您是否具有特定的表单名称。如果是，则将和r.name ='form_name'添加到子句中：

  SELECT r.form，s.value as email，COUNT（distinct s.record）as form_count 
 FROM subrecords s join 
记录r 
 on s.record = r.id 
 WHERE s.title ='email'
 GROUP BY s.value，r.form 
 HAVING form_count> 1; 
  




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

subrecords schema

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

The above table stores data submitted via a web form.

• 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)
• 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)
• 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.
• records.name is a text string that identifies each unique form, this is needed to select the form we are looking for

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')
• that have more than one of the same form submitted (identified by 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).
• 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)

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

解决方案

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:

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;

这篇关于在MYSQL表中查找数据在多个表中的重复（需要多个条件）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！