如何找到同一表中两行之间的差异并列出不匹配的记录? mysql在表中查找不匹配的行 [英] mysql How to Find Difference between two rows in same table and list the Unmatched Records? mysql finding unmatched rows in a table

问题描述

在MySQL中，我想查找同一表中两行之间的差异 我需要显示不匹配的记录

In MySQL i want to find difference between the two rows in same table where i need to display the unmatched records

这是我的表，名为project_details

CREATE TABLE IF NOT EXISTS `project_details` (
  `project_name` varchar(100) NOT NULL,
  `project_detail` varchar(100) NOT NULL,
  `project_version` varchar(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


INSERT INTO `project_details` (`project_name`, `project_detail`, `project_version`) VALUES
('project_1', 'php_mysql', '1.0'),
('project_1', 'php_mysql_android', '2.0'),
('project_1', 'php_mysql_android_ajax', '3.0'),
('project_2', 'php_mysql', '1.0'),
('project_2', 'php_mysql_android', '2.0'),
('project_2', 'php_mysql_android_ajax', '3.0'),
('project_2', 'php_mysql_jquery', '1.0'),
('project_2', 'php_mysql', '4.0');

这是表格的样子

我要从表中获取不匹配记录的结果 是比较project_1和project_2

where i want the result of unmatched records from the table that is from comparing project_1 and project_2

需要从project_name中比较project_1和project_2，那么它应该获得project_detail和project_version的不匹配记录

need to Compare both project_1 and project_2 from project_name then it should get the unmatched records of project_detail and project_version

我需要类似这样的结果，仅显示不匹配的记录，请参阅下文 图片

i need result something like this displaying only unmatched records refer below pic

推荐答案

使用NOT EXISTS检索输出，其中对于不同的项目名称，没有基于详细信息和版本的匹配行:

Use NOT EXISTS to retrieve output where for different project names there are no matching rows based on detail and version:

select *
from project_details p1
where not exists (
  select 1
  from project_details p2
  where p1.project_name <> p2.project_name
    and p1.project_detail = p2.project_detail
    and p1.project_version = p2.project_version
  )

SQL小提琴 ，以了解其工作原理.

SQL Fiddle to see how it works live.

输出

project_name    project_detail      project_version
---------------------------------------------------
project_2       php_mysql_jquery    1.0
project_2       php_mysql           4.0

这篇关于如何找到同一表中两行之间的差异并列出不匹配的记录? mysql在表中查找不匹配的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！