MySQL SQL 比较表中的两行以找出有多少不同的值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10493804/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL compare two rows in a table to find how many values are different
提问by Sydney Boy
I have the following table for storing user data:
我有下表用于存储用户数据：
e.g.
例如
TABLE: users
COLUMNS:
...
maritalStatus (INT) - FK
gender (CHAR)
occupation (INT) - FK
...
Now I want to compare two users in this table to see how many columns match for any two given users (say user X & user Y)
现在我想比较这个表中的两个用户，看看有多少列匹配任何两个给定的用户（比如用户 X 和用户 Y）
I am doing it via mySQL Stored Procedures by getting each value separately and then comparing them
我是通过 mySQL 存储过程通过分别获取每个值然后比较它们来实现的
e.g.
例如
SELECT maritalStatus from users where userID = X INTO myVar1;
SELECT maritalStatus from users where userID = Y INTO myVar2;
IF myVar1 = myVar2 THEN
...
END IF;
Is there a shorter way using an SQL query where I can compare two rows in a table and see which columns are different? I dont need to know how much different they actually are, just need to know if they contain the same value. Also I will only be comparing selected columns, not every column in the user table.
是否有使用 SQL 查询的更短方法，我可以在其中比较表中的两行并查看哪些列不同？我不需要知道它们实际上有多少不同，只需要知道它们是否包含相同的值。此外，我只会比较选定的列，而不是用户表中的每一列。
回答by Peter Lang
This will select the number of columns that are notthe same for user xand user y:
这将选择是列数不为用户相同的x和用户y：
SELECT ( u1.martialStatus <> u2.martialStatus )
+ ( u1.gender <> u2.gender )
+ ( u1.occupation <> u2.occupation )
FROM
users u1,
users u2
WHERE u1.id = x
AND u2.id = y
回答by Michael Buen
You can also use this:
你也可以使用这个：
select
-- add other columns as needed
(a.lastname,a.gender)
= (b.lastname,a.gender) as similar,
a.lastname as a_lastname,
a.firstname as a_firstname,
a.age as a_age,
'x' as x,
b.lastname as b_lastname,
b.firstname as b_firstname,
b.age as b_age
from person a
cross join person b
where a.id = 1 and b.id = 2
Output:
输出：
SIMILAR A_LASTNAME A_FIRSTNAME A_AGE X B_LASTNAME B_FIRSTNAME B_AGE
1 Lennon John 40 x Lennon Julian 15
Live test: http://www.sqlfiddle.com/#!2/840a1/2
回答by Invincible
I think, this may help someone. Objective: To find rows with same name and update new records date with the old record. This could be a condition where you will have to duplicate news item for different countryand keep the same date as original.
我认为，这可能对某人有所帮助。目标：查找具有相同名称的行并使用旧记录更新新记录的日期。这可能是一种情况，您必须为不同的国家/地区复制新闻项目并保持与原始日期相同的日期。
CREATE TABLE `t` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`locale` varchar(10) DEFAULT 'en',
`title` varchar(255) DEFAULT NULL,
`slug` varchar(255) DEFAULT NULL,
`body` text,
`image` varchar(255) DEFAULT NULL,
`thumb` varchar(255) DEFAULT NULL,
`slug_title` varchar(255) DEFAULT NULL,
`excerpt` text,
`meta_title` varchar(200) DEFAULT NULL,
`meta_description` varchar(160) DEFAULT NULL,
`other_meta_tags` text,
`read_count` int(10) DEFAULT '0',
`status` varchar(20) DEFAULT NULL,
`revised` text,
`created` datetime DEFAULT NULL,
`modified` datetime DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=20 DEFAULT CHARSET=utf8;
INSERT INTO `t` (`id`, `locale`, `title`, `slug`, `body`, `image`, `thumb`, `slug_title`, `excerpt`, `meta_title`, `meta_description`, `other_meta_tags`, `read_count`, `status`, `revised`, `created`, `modified`)
VALUES
(2, 'en', 'A title once again', '/news/title-one-again', 'And the article body follows.', '/uploads/2014/11/telecommunications100x100.jpg', NULL, NULL, NULL, '', '', NULL, 0, 'Draft', NULL, '2014-09-22 12:26:17', '2014-10-23 10:13:21'),
(3, 'en', 'A title once again', '/news/title-strikes-back', 'This is really exciting! Not.', '/uploads/2014/11/telecommunications100x100.jpg', NULL, NULL, NULL, '', '', NULL, 0, 'Unpublished', NULL, '2014-09-23 12:26:17', '2014-10-31 11:12:55'),
(4, 'en_GB', 'test', '/news/test', 'test', '/uploads/2014/11/telecommunications100x100.jpg', NULL, NULL, NULL, '', '', NULL, 0, 'Published', NULL, '2014-10-23 10:14:30', '2014-10-23 10:14:30');
update t join
t t2
on t.title = t2.title
set t2.created = t.created
where t.title = t2.title ;update t join t t2 on t.title = t2.title set t2.created = t.created where t.title = t2.title ;
更新 t join t t2 on t.title = t2.title set t2.created = t.created where t.title = t2.title ;
回答by Chris K
In the event another Magento developer finds their way here, a specific use for this Q/A is to compare two address entries in a table. "Magento 1" will put the same address in twice with the only differences being the key entity_idcolumn and address_typecolumn ( billing or shipping ).
如果另一个 Magento 开发人员在这里找到了他们的方法，则此 Q/A 的特定用途是比较表中的两个地址条目。“Magento 1”会将相同的地址放入两次，唯一的区别是键entity_id列和address_type列（ billing 或 shipping ）。
Already knowing the order's entity_id, use this to get the billing and shipping address IDs associated with the order: SELECT entity_id FROM sales_flat_order_address WHERE parent_id = 3137;
已经知道订单的entity_id，使用它来获取与订单关联的账单和送货地址 ID：SELECT entity_id FROM sales_flat_order_address WHERE parent_id = 3137;
Then to see if they differ for that order:
然后查看它们是否因该顺序不同而异：
SELECT a1.parent_id AS 'order_id'
, ( a1.street <> a2.street )
+ ( a1.city <> a2.city )
+ ( a1.postcode <> a2.postcode )
+ ( a1.region_id <> a2.region_id )
AS 'diffs'
FROM
sales_flat_order_address a1,
sales_flat_order_address a2
WHERE a1.entity_id = 6273
AND a2.entity_id = 6274
;
Gives the output:
给出输出：
+----------+-------+
| order_id | diffs |
+----------+-------+
| 3137 | 0 |
+----------+-------+
It would be fantastic if there was a way to do this en masse.
如果有一种方法可以集体做到这一点，那就太棒了。
回答by Andomar
You can count the number of users with the same columns using group by:
您可以使用以下方法计算具有相同列的用户数group by：
select u1.maritalStatus
, u1.gender
, u1.occupation
, count(*)
from users u1
group by
u1.maritalStatus
, u1.gender
, u1.occupation
回答by Robin Castlin
Just a continued example of Peter Langs suggestion in PHP:
只是 Peter Langs 在 PHP 中的建议的一个继续示例：
$arr_cols = array('martialStatus', 'gender', 'occupation');
$arr_where = array();
$arr_select = array();
foreach($arr_cols as $h) {
$arr_having[] = "compare_{$h}";
$arr_select[] = "(u1.{$h} != u2.{$h}) AS compare_{$h}";
}
$str_having = implode(' + ', $arr_where);
$str_select = implode(', ', $arr_where);
$query = mysql_query("
SELECT {$str_select}
FROM users AS u1, users AS u2
WHERE u1.userid = {$int_userid_1} AND u2.userid = {$int_userid_2}
HAVING {$str_having} > 0
");
/* Having case can be removed if you need the row regardless. */
/* Afterwards you check these values: */
$row = mysql_fetch_assoc($query);
foreach($arr_cols as $h)
if ($row["compare_{$h}"])
echo "Found difference in column {$h}!";
