Альтернатива пересечению в MySQL

Question

Альтернатива пересечению в MySQL

Мне нужно реализовать следующий запрос в MySQL.

(select * from emovis_reporting where (id=3 and cut_name= '全プロセス' and cut_name='恐慌') ) 
intersect
( select * from emovis_reporting where (id=3) and ( cut_name='全プロセス' or cut_name='恐慌') )

Я знаю, что пересечение не в MySQL. Поэтому мне нужен другой способ. Пожалуйста, ведите меня.

65

mysql intersect

Источник

user314433 12 апр '10 в 10:44

11 ответов

Другие вопросы по тегам mysql intersect

user340355 08 июл '10 в 07:22 2010-07-08 07:22 · Answer 1 · 2010-07-08 07:22

Microsoft SQL Server INTERSECT "возвращает любые отличные значения, которые возвращаются как запросом слева, так и справа от операнда INTERSECT". Это отличается от стандартного INNER JOIN или же WHERE EXISTS запрос.

SQL Server

CREATE TABLE table_a (
    id INT PRIMARY KEY,
    value VARCHAR(255)
);

CREATE TABLE table_b (
    id INT PRIMARY KEY,
    value VARCHAR(255)
);

INSERT INTO table_a VALUES (1, 'A'), (2, 'B'), (3, 'B');
INSERT INTO table_b VALUES (1, 'B');

SELECT value FROM table_a
INTERSECT
SELECT value FROM table_b

value
-----
B

(1 rows affected)

MySQL

CREATE TABLE `table_a` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `value` varchar(255),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

CREATE TABLE `table_b` LIKE `table_a`;

INSERT INTO table_a VALUES (1, 'A'), (2, 'B'), (3, 'B');
INSERT INTO table_b VALUES (1, 'B');

SELECT value FROM table_a
INNER JOIN table_b
USING (value);

+-------+
| value |
+-------+
| B     |
| B     |
+-------+
2 rows in set (0.00 sec)

SELECT value FROM table_a
WHERE (value) IN
(SELECT value FROM table_b);

+-------+
| value |
+-------+
| B     |
| B     |
+-------+

В этом конкретном вопросе задействован столбец id, поэтому повторяющиеся значения возвращаться не будут, но для полноты, вот альтернатива MySQL, использующая INNER JOIN а также DISTINCT:

SELECT DISTINCT value FROM table_a
INNER JOIN table_b
USING (value);

+-------+
| value |
+-------+
| B     |
+-------+

И еще один пример использования WHERE ... IN а также DISTINCT:

SELECT DISTINCT value FROM table_a
WHERE (value) IN
(SELECT value FROM table_b);

+-------+
| value |
+-------+
| B     |
+-------+

user1768736 10 июн '13 в 14:59 2013-06-10 14:59 · Answer 2 · 2013-06-10 14:59

Существует более эффективный способ создания пересечения, используя UNION ALL и GROUP BY. Производительность в два раза лучше по моим тестам на больших наборах данных.

Пример:

SELECT t1.value from (
  (SELECT DISTINCT value FROM table_a)
  UNION ALL 
  (SELECT DISTINCT value FROM table_b)
) AS t1 GROUP BY value HAVING count(*) >= 2;

Это более эффективно, потому что с решением INNER JOIN MySQL будет искать результаты первого запроса, а затем для каждой строки искать результат во втором запросе. С помощью решения UNION ALL-GROUP BY он будет запрашивать результаты первого запроса, результаты второго запроса, а затем группировать результаты все вместе сразу.

user55159 12 апр '10 в 10:58 2010-04-12 10:58 · Answer 3 · 2010-04-12 10:58

Ваш запрос всегда будет возвращать пустой набор записей, так как cut_name= '全プロセス' and cut_name='恐慌' никогда не оценю true,

В общем, INTERSECT в MySQL следует подражать так:

SELECT  *
FROM    mytable m
WHERE   EXISTS
        (
        SELECT  NULL
        FROM    othertable o
        WHERE   (o.col1 = m.col1 OR (m.col1 IS NULL AND o.col1 IS NULL))
                AND (o.col2 = m.col2 OR (m.col2 IS NULL AND o.col2 IS NULL))
                AND (o.col3 = m.col3 OR (m.col3 IS NULL AND o.col3 IS NULL))
        )

Если обе ваши таблицы имеют столбцы, помеченные как NOT NULLВы можете опустить IS NULL части и переписать запрос с немного более эффективным IN:

SELECT  *
FROM    mytable m
WHERE   (col1, col2, col3) IN
        (
        SELECT  col1, col2, col3
        FROM    othertable o
        )

user1959664 24 окт '16 в 04:14 2016-10-24 04:14 · Answer 4 · 2016-10-24 04:14

Я только что проверил это в MySQL 5.7 и действительно удивлен, как никто не предложил простой ответ: ЕСТЕСТВЕННОЕ СОЕДИНЕНИЕ

Когда таблицы или (выберите результат) имеют столбцы IDENTICAL, вы можете использовать NATURAL JOIN как способ найти пересечение:

Например:

таблица1:

id, name, jobid

"1", "Джон", "1"

"2", "Джек", "3"

"3", "Адам", "2"

"4", "Билл", "6"

таблица2:

id, name, jobid

"1", "Джон", "1"

"2", "Джек", "3"

"3", "Адам", "2"

"4", "Билл", "5"

"5", "Макс", "6"

И вот запрос:

SELECT * FROM table1 NATURAL JOIN table2;

Результат запроса: id, name, jobid

"1", "Джон", "1"

"2", "Джек", "3"

"3", "Адам", "2"

user83100 08 июн '12 в 23:44 2012-06-08 23:44 · Answer 5 · 2012-06-08 23:44

Для полноты вот еще один метод для подражания INTERSECT, Обратите внимание, что IN (SELECT ...) Форма, предложенная в других ответах, как правило, более эффективна.

Обычно для таблицы под названием mytable с первичным ключом под названием id:

SELECT id
FROM mytable AS a
INNER JOIN mytable AS b ON a.id = b.id
WHERE
(a.col1 = "someval")
AND
(b.col1 = "someotherval")

(Обратите внимание, что если вы используете SELECT * с помощью этого запроса вы получите вдвое больше столбцов, чем определено в mytable, это потому что INNER JOIN генерирует декартово произведение)

INNER JOIN здесь генерирует каждую перестановку пар строк из вашей таблицы. Это означает, что каждая комбинация строк генерируется в каждом возможном порядке. WHERE затем фильтрует a сторона пары, то b боковая сторона. В результате возвращаются только те строки, которые удовлетворяют обоим условиям, так же, как и два пересечения.

user5070879 23 окт '22 в 19:32 2022-10-23 19:32 · Answer 6 · 2022-10-23 19:32

Начиная с MySQL 8.0.31 INTERSECT изначально поддерживается.

Пункт ПЕРЕСЕЧЕНИЯ :

ВЫБРАТЬ... ПЕРЕСЕЧАТЬ [ВСЕ | РАЗЛИЧНЫЕ] ВЫБРАТЬ ... [ПЕРЕСЕЧАТЬ [ВСЕ | РАЗЛИЧНЫЕ] ВЫБЕРИТЕ ...]

INTERSECT ограничивает результат нескольких операторов SELECT теми строками, которые являются общими для всех.

Образец:

       SELECT 1 AS col
 INTERSECT 
 SELECT 1 AS col;

 -- output
 1

user5966911 20 апр '23 в 04:51 2023-04-20 04:51 · Answer 7 · 2023-04-20 04:51

Эмуляция INTERSECT через INNER JOIN или IN() может работать только с несколькими наборами соединений 2-4. Если вы попытаетесь найти взаимодействие между несколькими наборами, вам необходимо использовать несколько INNER JOIN. Но множественные INNER JOIN в MySQL - абсолютно нестабильное решение, которое может привести к запутыванию исполнителя плана запроса, который в нашем случае просто зависает на несколько дней со 100% загрузкой процессора.

Вам необходимо обновить MySQL до версии 8.0.31. Он содержит оператор INTERSECT.

user4376402 16 июн '15 в 11:39 2015-06-16 11:39 · Answer 8 · 2015-06-16 11:39

Разбейте свою проблему на 2 утверждения: во-первых, вы хотите выбрать все, если

(id=3 and cut_name= '全プロセス' and cut_name='恐慌')

правда. Во-вторых, вы хотите выбрать все, если

(id=3) and ( cut_name='全プロセス' or cut_name='恐慌')

правда. Таким образом, мы присоединимся к ИЛИ, потому что мы хотим выбрать все, если кто-либо из них верен

select * from emovis_reporting
    where (id=3 and cut_name= '全プロセス' and cut_name='恐慌') OR
        ( (id=3) and ( cut_name='全プロセス' or cut_name='恐慌') )

user9412005 14 апр '23 в 11:51 2023-04-14 11:51 · Answer 9 · 2023-04-14 11:51

Я использую IN для создания пересечения

Это пример использования:

      SELECT * FROM `test_oc_product` 
WHERE product_id IN ( SELECT product_id FROM test_oc_product_option WHERE option_id = '21' AND value = 'Red'  )
AND product_id IN ( SELECT product_id FROM test_oc_product_attribute WHERE attribute_id = '10' )

А вот дамп

      CREATE TABLE `test_oc_product` (
  `product_id` int(11) NOT NULL AUTO_INCREMENT,
  `model` varchar(64) NOT NULL,
  `sku` varchar(64) NOT NULL,
   PRIMARY KEY (`product_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `test_oc_product` (`product_id`, `model`, `sku`) VALUES
(1, 'A1', 'A1'),
(2, 'A2', 'A2'),
(3, 'A3', 'A3'),
(4, 'AA', 'AA'),
(5, 'A5', 'A5'),
(6, 'A6', 'A6'),
(7, 'A7', 'A7'),
(8, 'A8', 'A8');

CREATE TABLE `test_oc_product_attribute` (
  `product_id` int(11) NOT NULL,
  `attribute_id` int(11) NOT NULL,
  `text` text NOT NULL,
  PRIMARY KEY (`product_id`, `attribute_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `test_oc_product_attribute` (`product_id`, `attribute_id`, `text`) VALUES 
('1', '10', 'Attribute Value 1'), 
('2', '11', 'Attribute Value 2');


CREATE TABLE `test_oc_product_option` (
  `product_option_id` int(11) NOT NULL AUTO_INCREMENT,
  `product_id` int(11) NOT NULL,
  `option_id` int(11) NOT NULL,
  `value` text NOT NULL,
   PRIMARY KEY (`product_option_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


INSERT INTO `test_oc_product_option` (`product_option_id`, `product_id`, `option_id`, `value`) VALUES 
(NULL, '1', '21', 'Red'), 
(NULL, '2', '21', 'Red'),
(NULL, '3', '21', 'Blue'),
(NULL, '4', '10', 'S');

user4945163 27 май '15 в 14:21 2015-05-27 14:21 · Answer 10 · 2015-05-27 14:21

SELECT
  campo1,
  campo2,
  campo3,
  campo4
FROM tabela1
WHERE CONCAT(campo1,campo2,campo3,IF(campo4 IS NULL,'',campo4))
NOT IN
(SELECT CONCAT(campo1,campo2,campo3,IF(campo4 IS NULL,'',campo4))
FROM tabela2);

-2

Источник

user4945163 27 май '15 в 14:21

user314531 12 апр '10 в 13:42 2010-04-12 13:42 · Answer 11 · 2010-04-12 13:42

AFAIR, MySQL реализует INTERSECT через INNER JOIN.

-2

Источник

user314531 12 апр '10 в 13:42