需要在mysql行之间找到一个公共元素

时间：2021-07-06 15:29:06

Let me start by saying that I am new to mysql and have searched on this site and others for a solution that will work for me. I've gotten fairly close (I think), but can't seem to find a solution.

首先让我说我是mysql的新手,并在这个网站和其他人上搜索了一个对我有用的解决方案。我已经相当接近(我认为),但似乎无法找到解决方案。

I'm working on a Joomla site using RSForms software. I need to search a table where one row contains FirstName, another row contains LastName, and another row contains Email. Ultimately, I am looking for SubmissionId that is common to all 3. SubmissionId is a number generated by some form software that groups all of the form submission's elements by this id. I need to search for this id that is common to all 3. Each of the 3 elements may contain duplicates of the search. For instance, searching for the first name "John" will likely produce multiple results. I want to find the SubmissionId that matches "John" and last name "Doe" and email "johndoe@gmail.com" so I can use that to look up other information. It is also possible that there could be multiple matches for "John", "Doe", and johndoe@gmail.com".

我正在使用RSForms软件在Joomla网站上工作。我需要搜索一个表,其中一行包含FirstName,另一行包含LastName,另一行包含电子邮件。最终,我正在寻找所有共同的SubmissionId 3. SubmissionId是由某个表单软件生成的数字,它通过此id对所有表单提交的元素进行分组。我需要搜索所有3共有的id .3个元素中的每一个都可能包含搜索的重复项。例如,搜索名字“John”可能会产生多个结果。我想找到匹配“John”和姓氏“Doe”的SubmissionId,并发送电子邮件“johndoe@gmail.com”,以便我可以使用它来查找其他信息。 “John”,“Doe”和johndoe@gmail.com“也可能有多个匹配。”

I've tried many variations of the following (with/without ANY) and putting the results into an array and counting through each element. I've only had success in acquiring the first instance of "John".

我已经尝试了以下的许多变体(有/无ANY)并将结果放入数组并计算每个元素。我只是成功地获得了第一个“约翰”。

$getresults = $db->setQuery("SELECT SubmissionId FROM my_table_values WHERE FormId = '$formid' AND FieldValue = '$fname' AND SubmissionId = ANY
    (SELECT SubmissionId FROM my_table_values WHERE FormId = '$formid' AND FieldValue = '$lname' AND SubmissionId = ANY
    (SELECT SubmissionId FROM my_table_values WHERE FormId = '$formid' AND FieldValue = '$email' ))");

---SubmissionValueId---|---FormId---|---SubmissionId---|---FieldName---|---Field‌​‌​‌​Value---
---------18192---------|-----20-----|-------5462-------|-----Email-----|---johndoe@gmail.com---
---------18193---------|-----20-----|-------5462-------|-----FName-----|---John---
---------18194---------|-----20-----|-------5462-------|-----LName-----|---Doe---

2 个解决方案

#1

One possibility is to use a query that joins my_table_values with itself, even two times, to create something that can then be checked. For instance ... (fair warning: extemporaneous coding) ...

一种可能性是使用将my_table_values与其自身连接的查询,甚至两次,以创建可以随后检查的内容。例如......(公平警告:临时编码)......

select A.submission_id from my_table_values A inner join my_table_values B using (SubmissionID) inner join my_table_values C using (SubmissionID) where (A.formID="email" and A.fieldValue= "foo@bar.com") and (B.formID="lastname" and B.fieldValue= "bar") and (C.formID="first name" and C.fieldValue="bletch")

从my_table_values中选择A.submission_id内部联接my_table_values B使用(SubmissionID)内部联接my_table_values C使用(SubmissionID)其中(A.formID =“email”和A.fieldValue =“foo@bar.com”)和(B.formID) =“lastname”和B.fieldValue =“bar”)和(C.formID =“名字”和C.fieldValue =“bletch”)

You will need to examine that query to see (a) if the SQL processor accepts it, and (b) what sort of execution-plan it came up with. Preferably, all of the fields should be indexed. You need to see that it plans to use the indexes each time to narrow the list of possibilities, and that it plans to use the most-selective field (e.g. "email") first.

您将需要检查该查询以查看(a)SQL处理器是否接受它,以及(b)它提出了什么样的执行计划。优选地,应该索引所有字段。您需要看到它计划每次使用索引来缩小可能性列表,并且它计划首先使用最具选择性的字段(例如“email”)。

(The using (fieldname) syntax is specific to MySQL. You might need to use on to do the same thing.)

(using(fieldname)语法特定于MySQL。您可能需要使用on来执行相同的操作。)

#2

$getresults = $db->setQuery("
     SELECT 
       MAX(IF(FieldValue = '$fname',SubmissionId, NULL) as fnameId,
       MAX(IF(FieldValue = '$lname',SubmissionId, NULL) as lnameId,
       MAX(IF(FieldValue = '$email',SubmissionId, NULL) as emailId
     FROM my_table_values 
     WHERE FormId = '$formid' 
     GROUP BY FormId");

#1

One possibility is to use a query that joins my_table_values with itself, even two times, to create something that can then be checked. For instance ... (fair warning: extemporaneous coding) ...

一种可能性是使用将my_table_values与其自身连接的查询,甚至两次,以创建可以随后检查的内容。例如......(公平警告:临时编码)......

select A.submission_id from my_table_values A inner join my_table_values B using (SubmissionID) inner join my_table_values C using (SubmissionID) where (A.formID="email" and A.fieldValue= "foo@bar.com") and (B.formID="lastname" and B.fieldValue= "bar") and (C.formID="first name" and C.fieldValue="bletch")

从my_table_values中选择A.submission_id内部联接my_table_values B使用(SubmissionID)内部联接my_table_values C使用(SubmissionID)其中(A.formID =“email”和A.fieldValue =“foo@bar.com”)和(B.formID) =“lastname”和B.fieldValue =“bar”)和(C.formID =“名字”和C.fieldValue =“bletch”)

You will need to examine that query to see (a) if the SQL processor accepts it, and (b) what sort of execution-plan it came up with. Preferably, all of the fields should be indexed. You need to see that it plans to use the indexes each time to narrow the list of possibilities, and that it plans to use the most-selective field (e.g. "email") first.

您将需要检查该查询以查看(a)SQL处理器是否接受它,以及(b)它提出了什么样的执行计划。优选地,应该索引所有字段。您需要看到它计划每次使用索引来缩小可能性列表,并且它计划首先使用最具选择性的字段(例如“email”)。

(The using (fieldname) syntax is specific to MySQL. You might need to use on to do the same thing.)

(using(fieldname)语法特定于MySQL。您可能需要使用on来执行相同的操作。)

#2

$getresults = $db->setQuery("
     SELECT 
       MAX(IF(FieldValue = '$fname',SubmissionId, NULL) as fnameId,
       MAX(IF(FieldValue = '$lname',SubmissionId, NULL) as lnameId,
       MAX(IF(FieldValue = '$email',SubmissionId, NULL) as emailId
     FROM my_table_values 
     WHERE FormId = '$formid' 
     GROUP BY FormId");
标签：

相关文章