нужно составить запрос

@solomon123 · Регистрация: 25.01.2013

Author24 — интернет-сервис помощи студентам

Ребята, всем привет. Нужна ваша помощь. Имеется следующая таблица School:

Teacher	Class
t1	A
t1	B
t2	B
t3	B
t2	A
t3	C
t2	C

Нужно создать запрос который находит те классы в которых преподают ВСЕ преподаватели (т.е вывод должен иметь только В)

@Joeymax · 10.05.2013, 08:49

не самый оптимальный

, но что-то еще придумывать было лениво. На этих тестовых данных выдает "B". Дополнительно не проверял.

MySQL

create table x (
  m char(1),
  p char(1)
) engine = myisam default character set = cp1251;
insert into x values
  ('1', 'a'),
  ('1', 'b'),
  ('2', 'b'),
  ('3', 'b'),
  ('2', 'a'),
  ('2', 'c'),
  ('3', 'c');
select p from (
  select m,p from x group by p,m) a
    group by p having count(a.p) = (select count(*) from (select distinct m from x) b);

Vovan-VE · 10.05.2013, 10:13

MySQL

SELECT `Class`
FROM
    (
        SELECT DISTINCT `Class`
        FROM `School`
    ) AS `c`
WHERE 1 = ALL (
    SELECT
        EXISTS(
            SELECT *
            FROM `School` AS `s`
            WHERE `s`.`Teacher` = `t`.`Teacher`
                AND `s`.`Class` = `c`.`Class`
        )
    FROM
        (
            SELECT DISTINCT `Teacher`
            FROM `School`
        ) AS `t`
);

@Joeymax · 10.05.2013, 12:14

Поздравляю, я и не думал, что так легко напишу простой и совершенно не оптимальный код, но ты меня переплюнул, спасибо!
Вывод для explain на одних и тех же данных

Код

mysql> explain select p from (                                                                                            
    ->  select m,p from x group by p,m) as a                                                                              
    ->          group by p having count(a.p) = (select count(*) from (select distinct m from x) b);                       
+----+-------------+------------+------+---------------+------+---------+------+------+---------------------------------+ 
| id | select_type | table      | type | possible_keys | key  | key_len | ref  | rows | Extra                           | 
+----+-------------+------------+------+---------------+------+---------+------+------+---------------------------------+ 
|  1 | PRIMARY     | <derived2> | ALL  | NULL          | NULL | NULL    | NULL |   10 | Using temporary; Using filesort | 
|  3 | SUBQUERY    | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL | Select tables optimized away    | 
|  4 | DERIVED     | x          | ALL  | NULL          | NULL | NULL    | NULL |   11 | Using temporary                 | 
|  2 | DERIVED     | x          | ALL  | NULL          | NULL | NULL    | NULL |   11 | Using temporary; Using filesort | 
+----+-------------+------------+------+---------------+------+---------+------+------+---------------------------------+ 
4 rows in set (0.00 sec)                                                                                                  
                                                                                                                          
mysql> explain SELECT `Class` FROM (                                                                                      
    ->  SELECT DISTINCT `Class` FROM `School`) AS `c`                                                                     
    ->          WHERE 1 = ALL (                                                                                           
    ->     SELECT EXISTS (                                                                                                
    ->          SELECT * FROM `School` AS `s`                                                                             
    ->                  WHERE `s`.`Teacher` = `t`.`Teacher` AND `s`.`Class` = `c`.`Class`)                                
    ->     FROM (                                                                                                         
    ->          SELECT DISTINCT `Teacher` FROM `School`) AS `t`);                                                         
+----+--------------------+------------+------+---------------+------+---------+------+------+-----------------+          
| id | select_type        | table      | type | possible_keys | key  | key_len | ref  | rows | Extra           |          
+----+--------------------+------------+------+---------------+------+---------+------+------+-----------------+          
|  1 | PRIMARY            | <derived2> | ALL  | NULL          | NULL | NULL    | NULL |    3 | Using where     |          
|  3 | DEPENDENT SUBQUERY | <derived5> | ALL  | NULL          | NULL | NULL    | NULL |    4 | Using where     |          
|  5 | DERIVED            | School     | ALL  | NULL          | NULL | NULL    | NULL |   11 | Using temporary |          
|  4 | DEPENDENT SUBQUERY | s          | ALL  | NULL          | NULL | NULL    | NULL |   11 | Using where     |          
|  2 | DERIVED            | School     | ALL  | NULL          | NULL | NULL    | NULL |   11 | Using temporary |          
+----+--------------------+------------+------+---------------+------+---------+------+------+-----------------+          
5 rows in set (0.00 sec)

Vovan-VE · 11.05.2013, 08:14

Не по теме:

Joeymax, Мне всё равно не даёт покоя мысль, что должен быть способ лучше. :umbrage:

Vovan-VE
	11.05.2013, 08:14 нужно составить запрос #5
	Не по теме: Joeymax, Мне всё равно не даёт покоя мысль, что должен быть способ лучше. :umbrage: 0