mysql cross join not in?

Using a query like below you can fetch rows in which the color of the id is blue, purple, green, white, and black.

SELECT t1.id, col
FROM extra as e INNER JOIN your_table as t1 USING ( id )
CROSS JOIN your_table as t2 USING ( id )
CROSS JOIN your_table as t3 USING ( id )
CROSS JOIN your_table as t4 USING ( id )
CROSS JOIN your_table as t5 USING ( id )
WHERE t1.color = 'blue' and t2.color = 'purple' and t3.color= 'green' and t4.color= 'white' and t5.color= 'black'

Если вы попытаетесь использовать!= Или NOT IN, это не сработает. Как бы я написал запрос так, чтобы цвета содержали синий, фиолетовый, зеленый, белый, но НЕ черный?

Источник

3 ответа

Решение

Вы могли бы сделать что-то вроде:

select e.id
from   extra as e
where  exists (select null from your_table as t where t.id = e.id and t.color = 'blue')
  and  exists (select null from your_table as t where t.id = e.id and t.color = 'purple')
  and  exists (select null from your_table as t where t.id = e.id and t.color = 'green')
  and  exists (select null from your_table as t where t.id = e.id and t.color = 'white')
  and not exists (select null from your_table as t where t.id = e.id and t.color = 'black')

Или что-то вроде этого, вероятно, будет более эффективным:

select e.id
from   extra as e
where  4 = 
       (select count(*) 
        from   your_table as t 
        where  t.id = e.id 
          and  t.color in ('blue', 'purple', 'green', 'white'))
  and  0 = 
       (select count(*) 
        from   your_table as t 
        where  t.id = e.id 
          and  t.color in ('black'))
Источник

Я не знаю, почему вы используете CROSS JOIN, Это обычно для создания декартовых продуктов. Все, что вам нужно, это просто INNER JOIN или просто JOIN,

Я обычно использую OUTER JOIN когда я хочу проверить на отсутствие каких-либо данных. Когда совпадение не найдено, t5 будет NULL.

SELECT t1.id, col
FROM extra as e 
INNER JOIN your_table as t1 ON ( e.id=t1.id AND t1.color = 'blue' )
INNER JOIN your_table as t2 ON ( e.id=t2.id AND t2.color = 'purple' )
INNER JOIN your_table as t3 ON ( e.id=t3.id AND t3.color = 'green' )
INNER JOIN your_table as t4 ON ( e.id=t4.id AND t4.color = 'white' )
LEFT OUTER JOIN your_table as t5 ON ( e.id=t5.id AND t5.color = 'black' )
WHERE t5.id IS NULL;

Вы правы, что описанная выше методика с использованием объединений быстрее, чем с использованием коррелированных подзапросов, и также (по крайней мере, в MySQL) быстрее, чем GROUP BY решение, которое используют некоторые люди:

SELECT e.id, col
FROM extra as e
INNER JOIN your_table AS t USING ( id)
WHERE t.color IN ('blue', 'purple', 'green', 'white')
GROUP BY e.id
HAVING COUNT(*) = 4;

(Этот запрос не решает проблему "не черных", я просто иллюстрирую технику.)

Источник

Я думаю, что вы слишком усложняете запрос, то, что вы пытаетесь сделать, выглядит как стандартное соединение

select * from cars join colors where cars.colorid = colors.colorid where colors.color != 'black'
Источник
Другие вопросы по тегам