基于单字段的Mysql交集 [英] Mysql intersection based on single field

问题描述

我正在使用 php 和 mysql.我有 2 张桌子:

I am working with php and mysql. I have 2 tables :

CREATE TABLE IF NOT EXISTS `mytable` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`emails` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`phones` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`history` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`insert_date` date DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=259 ;

INSERT INTO `mytable` (`id`, `emails`, `phones`, `history`, `insert_date`)
VALUES
(118, 'PLEASE SET',  '[e-mail]', 'None','2015-01-13'),
(237, 'PLEASE SET', '[e-mail]', 'gomez', '2015-01-11');

和

 CREATE TABLE IF NOT EXISTS `mytable2` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`history` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=259 ;

INSERT INTO `mytable2` (`id`, `history`)
VALUES
(1, 'bob',),
(2 , 'gomez');

选择后:

SELECT *
FROM `mytable1`
WHERE `insert_date` >= DATE_SUB(CURDATE(), INTERVAL 3 DAY)

我想在 mytable2 中找到历史字段与上述查询选择的历史字段相匹配的记录子集.这基本上是基于历史字段的交集.我不确定如何在 mysql 中对此进行编码，尤其是因为intersect"不存在.

I want to find the subset of records in mytable2 that have a history field that match the history fields selected with the above query. This is basically an intersection based on the history field. I'm not sure how to code this in mysql, especially since 'intersect' does not exist.

推荐答案

你可以使用一个简单的 加入这个工作:

You can use a simple JOIN for this job:

SELECT 
  m1.* 
FROM
  mytable m1 
  INNER JOIN mytable2 m2 
    ON m1.history = m2.history 

返回:

id  emails      phones          history insert_date
237 PLEASE SET  [e-mail]  gomez   2015-01-11

您问题中的查询如下所示:

The query in your question would look like this:

SELECT 
  m1.* 
FROM
  mytable m1 
  INNER JOIN mytable2 m2 
    ON m1.history = m2.history 
WHERE `insert_date` >= DATE_SUB(CURDATE(), INTERVAL 3 DAY)

并且它正确地不返回任何内容，因为唯一符合条件的记录的日期早于 3 天.

And it correctly returns nothing because the only qualifying record has a date older than 3 days.

这是一个非常有用的信息图，将集合操作与 SQL 函数相关联

Here is an incredibly useful infographic relating set operations to SQL functions

http://www.codeproject.com/KB/database/Visual_SQL_Joins/Visual_SQL_JOINS_orig.jpg

这篇关于基于单字段的Mysql交集的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！