在 MySQL 中从单个表中查找与给定结果类似的结果 - Find similar results to a given result from single table in MySQL

Find similar results to a given result from single table in MySQL

本文关键字：结果 MySQL 单个表查找 | 更新日期: 2023-09-27

我有一个包含这些列的表格：

id - 姓名 - 性别 - 年龄 - 国家

我想显示所有与MySQL和PHP中的某人具有相同gender，age和country的人的名字。

最好的MySQL查询是什么？

SELECT
yt.*
FROM yourTable AS yt
JOIN 
(SELECT * FROM yourTable yt2 WHERE id = $id) yt3
ON yt.Gender = yt3.Gender AND yt.Age = yt3.Age AND yt.Country = yt3.Country

观看现场表演

试试这个：

SELECT p1.Name
FROM peoplenames p1
INNER JOIN
(
  SELECT Gender, Age, Country
  FROM peoplenames p1
  GROUP BY Gender, Age, Country
  HAVING COUNT(*) > 1
) p2  ON p1.Gender  = p2.Gender
     AND p1.Age     = p2.Age
     AND p1.Country = p2.Country;

这将忽略那些没有重复Gender, Age, Country的名称。

这将为您提供具有相同Gender, Age, Country的名称。但是，如果此组合Gender, Age, Country只出现一次，则这些名称将被忽略。

SQL 小提琴演示

例如，让我们有以下示例数据：

| ID |     NAME | GENDER | AGE | COUNTRY |
------------------------------------------
|  1 |    Ahmed |   Male |  20 |   Egypt |
|  2 | Mohammed |   Male |  20 |   Egypt |
|  3 |      ali |   Male |  20 |   Egypt |
|  4 |     Saly | Female |  25 |  Jordan |
|  5 |     Sara | Female |  25 |  Jordan |
|  6 | Mohammed |   Male |  60 |   Egypt |
|  7 |   Khalid |   Male |  20 |     KSA |

Ahmed，Mohammed（ID为2）和ali具有相同的Gender，Age和Country：Male, 20, Egypt。

saly和Sara也是如此.将仅选择五个名称：

|     NAME |
------------
|    Ahmed |
| Mohammed |
|      ali |
|     Saly |
|     Sara |

而其他人：

|  6 | Mohammed |   Male |  60 |   Egypt |
|  7 |   Khalid |   Male |  20 |     KSA |

没有其他行具有相同的Gender, Age, Country因此它们将被忽略。

SELECT Name 
FROM table_name
WHERE Gender  = (SELECT Gender FROM  table_name WHERE id = $x) AND 
      Age     = (SELECT Age FROM  table_name WHERE id = $x) AND 
      Country = (SELECT Country FROM  table_name WHERE id = $x);

$x将是特定人员的 ID。

根据@fthiella评论

SELECT Name 
FROM table_name
WHERE (Gender, Age, Country) = (SELECT Gender, Age, Country FROM table_name WHERE id = $x)

select `name` from `tablename` group by `gender`,`age `,`country`;