Форум MySQL

нужно найти производителей, выпускающих по меньшей мере три различных модели ПК.
есть четыре таблицы используется только две
product там model в цифрах модель написана, maker производитель
pc там model у которого внешний ключ на product.maker, ну и там несколько столбцов там их характеристики
и есть еще две таблицы printer и laptop они здесь не нужны
вот мой код

select maker, count(model)
from product
group by maker
having model in (select model from pc)

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

Column 'product.model' is invalid in the HAVING clause because it is not contained in either an aggregate function or the GROUP BY clause.

По вашему описанию тут даже однотабличный запрос.

Похоже так должно быть:

SELECT `maker`,         count(`model`) AS `co`    FROM `product`   GROUP BY `maker`  HAVING `co` > 2

в model в обоих таблицах это код по которому можно определить что это компьютер или лэптоп или принтер, вот мне надо найти только тех производителей которые выпускают по три или более моделей PC.

я не правильно написал
PC там model у которого внешний ключ на product.model ну и там несколько столбцов там их характеристики

Чет я запутался... Вы не могли бы привести CREATE TABLE обоих таблиц, кусочек дампа (строк по 10) и какой должен быть результат по этим конкретным данным? Так проще было бы....

это таблица Product

maker model
A 1232
A 1233
A 1276
A 1298
A 1401
A 1408
A 1752
B 1121
B 1750
C 1321
D 1288
D 1433
E 1260
E 1434
E 2112
E 2113

а это PC

code model speed ram hd cd price
1 1232 500 64 5.0 12x 600.0000
10 1260 500 32 10.0 12x 350.0000
11 1233 900 128 40.0 40x 980.0000
12 1233 800 128 20.0 50x 970.0000
2 1121 750 128 14.0 40x 850.0000
3 1233 500 64 5.0 12x 600.0000
4 1121 600 128 14.0 40x 850.0000
5 1121 600 128 8.0 40x 850.0000
6 1233 750 128 20. 50x 950.0000
7 1232 500 32 10.0 12x 400.0000
вот тут надо найти производителей это maker которые производят более трех компьютеров.
в функцию count должно войти только те модели которые соответствуют столбцу model в обоих таблицах. номера моделей в таблице Product уникальны для всех производителей и типов продуктов
в product.model есть модели которые соответствуют другим таблицам принтерам или лэптопам

вроде бы у меня правильно но у меня выходит ошибка

select maker, count(model)
from product
group by maker
having model in (select model from pc)

кажется так:

SELECT `pr`.`maker` , count( `pr`.`model` ) AS `co`  FROM `product` `pr`  INNER JOIN `pc` `pc`  USING ( `model` )  GROUP BY `pr`.`maker`  HAVING `co` >2

пишу так
select product.maker, count(product.model) as ountf
from product inner join pc
on product.model = pc.model
group by product.model
having ountf > 2

выходит ошибка

Invalid column name 'ountf'.

пишу так

select product.maker, count(product.model) as ountf
from product inner join pc
using (model)
group by product.model
having ountf > 2

выходит ошибка
"model" is not a recognized table hints option. If it is intended as a parameter to a table-valued function or to the CHANGETABLE function, ensure that your database compatibility mode is set to 90.

А что, в том что я написал что-то не так было? Зачем вы переделывать стали? И зачем поубирали обратные кавычки и алиасы? Хотя алиасы не должны вроде влиять, а кавычки вполне могут. Но скорее всего,

group by product.maker
а не
group by product.model

я незнал что с ковычками будет работать, думал что вы так просто пишете
а что такое алиасы? это именование чтоли, так у меня и с ними тоже выходит ошибка таже самая.

нет пишу group by product.maker выходит таже ошибка что и до этого.

я эти задачи решаю на сайте sql-ex.ru, там много задач как для начинающих так и для профи.
это моя по номеру 20 задача (select обучающий этап) вы тоже там найдете что надо для себя может быть.
вот он на английском но есть и на русском надо зарегистрироваться. и решать.
может быть так сможем его решить.

Нельзя же так делать!!! Терь я на несколько дней потерян для общества =( А ведь были же дела..... =))
----------------
Вы забыли сказать, что там не MySQL, а SQL в чистом виде. Это посложнее
----------------
20-ая задача:

SELECT maker, count(model) co     FROM product    WHERE type='pc' GROUP BY maker   HAVING count(model)>2

----------------
А вы 16ую решили? Дело в том, что я решил и уверен что правильно. А они говорят, мол результат получен правильный, но не верным путем! =(

>Нельзя же так делать!!! Терь я на несколько дней потерян для общества =( А ведь были же дела..... =))
>----------------
))

>А вы 16ую решили? Дело в том, что я решил и уверен что правильно. А они говорят, мол результат получен правильный, но не верным путем! =(


select distinct pc.model, ps.model, pc.speed, pc.ram
from pc, pc as ps
where pc.ram = ps.ram and pc.speed = ps.speed and pc.model > ps.model

у вас наверное так же только вы не поставили distinct, я тоже долго думал в чем проблема,
---------------------

ааа вот как надо решить я то думал что надо делать так чтобы получился как бы много табличный запрос.

> у вас наверное так же только вы не поставили distinct, я тоже долго думал в чем проблема
Именно. Ну почти также:

SELECT DISTINCT p1.model, p2.model, p1.speed, p2.ram    FROM pc p1    JOIN pc p2 ON p1.speed = p2.speed             AND p1.ram = p2.ram              AND p1.model > p2.model

При чем я только что ее решил-таки, и зашел отписаться, а тут вы уже ответили )
Точно также бился с 17ой.... Решил ее 6-ю способами, прежде чем додумался до DISTINCT )

тоже самое долго с несколькими задачами мучался а потом случайно поставил ее и получилось.

немного странно как то зачем он там вообще нужен? !
он же и без этого правильно ответ выдает

А как у вас с задачей 34? У меня

Ваш запрос вернул правильные данные на основной базе, но не прошел тест на проверочной базе. * Неверное число записей (больше на 2)

хоть тресни! Опять уже 10-ю способами решил, результат один и тот же! Вообще не пойму в чем дело и что они хотят! При чем стал разбирать запрос через EXPLAIN, и действительно удалось уменьшить число записей в этом запросе с 4 до 2. Но Там результат остался тем же самым! Т.е. они под проверочной базой и не EXPLAIN имеют ввиду. Ниче не понятно!

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

Я дошел до точки, в которой понял, что мне оно не нужно. Т.е. Там запросы все однотипные. И правильный результат получаю почти всегда с 1ой попытки, но из-за того что структура БД идиотская и ненормализованная, путей получения результата масса. А они хотят, чтоб решалось именно их способом. Да откуда я знаю, каким местом они думают, чтоб составить запрос как они. Я в жизни так БД не построю, чтоб у меня такие геморы были.

А так вообще решено по 40ую включительно, из них 2 пропущено (именно из-за этих тупых ошибок, где не понятно что хотят). Рейтинг 2715, очков - 60

понятно, ну а я пока только так буду практиковаться, потихоньку наберусь опыта и смогу смело говорить работодателю что я хорошо знаю SQL, ну что то типа этого.
у меня проблема с задачами 8,14,19, 23

23 у меня вот такой
select distinct maker
from product, pc, laptop
where pc.speed >= 750 and pc.model = product.model or laptop.speed >= 750 and laptop.model = product.model

результат правильный но где то надо по другому решать
и еще 24 надо бы решить

> потихоньку наберусь опыта и смогу смело говорить работодателю что я хорошо знаю SQL
Тут есть пара моментов.... Во-первых есть куча различий диалектов SQL. Т.е. то что там работает, очень часто не работает в MySQL. Очень много там приходится делать лишнего по сравнению с MySQL (в MySQL было бы проще). Но к этому можно привыкнуть.... Т.е. если научиться думать на SQL, то подстроиться под диалект можно. И во-вторых - нужно уметь проектировать структуру БД так, чтобы в дальнейшем не приходилось вот так изголяться. Но в целом, конечно, польза есть. Еще бы там были какие-то средства анализа запросов, было бы вообще супер. Т.е. чтоб не просто "не правильно на проверочной БД", а чтоб информация об ошибке была. Чтоб не методом тыка подбирать верный результат, а осознано.

> у меня проблема с задачами 8,14,19, 23
В смысле вам сказать ответы, или вы сами еще попытаетесь?

понятно спасибо!
да ответы, а то я сам запарился уже не пойму и так и так пытаюсь ответы не правильные у меня.

Кстати, я разобрался, что значит "не прошел тест на проверочной базе".

Чтобы этой ошибки не было, нужно не оптимизировать запрос, и не написать его именно как у них, а составить его так, чтобы он работал однозначно (с одинаковой логикой) на одинаковой структуре БД при любом количестве и качестве данных в этой БД.

Очень часто получается так, что мы, зная исходные таблицы и правильный результат, просто подгоняем его, дописывая лишние условия выборки. Либо не учитываем какие-то неочевидные мелочи и подводные камни. Например, я это четко заметил, обжегшись на том, что

LEN('   ')

вернет не 3, а NULL. И такого, когда на очевидных данных результат верный, а на скрытых неожиданный - ПОЛНО!

Поэтому у них есть точно такая же по структуре база, но содержащая такие данные, при которых эти ошибки вылазят. Помните про DISTINCT? результат у нас у обоих был верный, а проверка не проходила..... Значит возможно такое содержание БД, при котором без DISTINCT появляются лишние записи.

Вот они и требуют, чтоб запрос работал однозначно на любых данных!

Согласитесь, что это справедливо?

---------------------

// Задача №8 SELECT prod.maker FROM product prod LEFT JOIN( SELECT maker FROM product            WHERE type='laptop'            GROUP BY maker ) tempt ON prod.maker = tempt.maker WHERE tempt.maker IS NULL AND prod.type='pc' GROUP BY prod.maker

// Задача №14 SELECT tt.maker, tt.type FROM( SELECT maker, count(model) co       FROM product       GROUP BY maker       HAVING count(model)>1 )tm JOIN( SELECT maker, type, count(model) co       FROM product       GROUP BY maker, type       HAVING count(model)>1 )tt ON tm.co = tt.co AND tm.maker = tt.maker

// Задача №19 (че эт вы? она ж совсем элементарная!) SELECT maker, avg(screen) FROM product pr JOIN laptop l ON pr.model = l.model GROUP BY maker

// Задача №23 SELECT DISTINCT r1.maker FROM( SELECT maker       FROM product pr       JOIN( SELECT * FROM laptop WHERE speed>=750 )l       ON pr.model = l.model )r1,     ( SELECT maker       FROM product pr       JOIN( SELECT * FROM pc WHERE speed>=750 )p       ON pr.model = p.model )r2 WHERE r1.maker = r2.maker

---------------------
P.S. Если сами решать не будете, то работодателю не сможете говорить, что хорошо знаете SQL =)
А то там дальше и вот такие запросы есть:

SELECT t2.name FROM( SELECT s.name, c.displacement ds, c.numguns ng       FROM classes c       JOIN ships s ON c.class = s.class     UNION       SELECT t.ship, c.displacement, c.numguns       FROM classes c       JOIN( SELECT o.ship             FROM ships s             RIGHT JOIN outcomes o             ON o.ship = s.name             WHERE s.name IS NULL )t       ON c.class = t.ship )t2 JOIN( SELECT ds, max(ng) mng       FROM( SELECT c.displacement ds, c.numguns ng             FROM classes c             JOIN ships s ON c.class = s.class           UNION             SELECT c.displacement, c.numguns             FROM classes c             JOIN( SELECT o.ship                   FROM ships s                   RIGHT JOIN outcomes o                   ON o.ship = s.name                   WHERE s.name IS NULL )t             ON c.class = t.ship )t1       GROUP BY ds )t3 ON t2.ds = t3.ds AND t2.ng >= t3.mng

* это трехочковый =)

Спасибо за прояснение, теперь знаю в чем цель, еще буду до последнего сам стараться делать, да это вполне справедливо и правильно.
и за коды спасибо.
а что значит в 8 задаче is null

А вы теорию там вообще не читаете? Зря. Без нее можете не пытаться. Вернее даже не без теории, а без понимания теории.

Когда используешь (LEFT, RIGHT, FULL (OUTER в MySQL)) JOIN то в итоговой таблице появляются ячейки со значением null. Соответственно IS NULL их находит.

Еще FAQ по задачам читайте, когда не проходит тест на проверочной базе.

а какая еще теория и где там на их сайте что ли, я там ничего не видел

В каждой задаче под заданием ссылки на темы, которые рекомендуются для ее решения.
см.скриншот.

какое у вас продвижение уже, вы наверное уже рейтинговый этап проходите.?!
какой у вас стаж программирования SQl.

Рейтинг 946, 88 очков, решено по 56 включительно, 2 пропущено.

Рейтинговый этап вообще жесть! Там на 1 балл задачки как в обучающем на 3 балла! Я 2 штуки решил, сломал всю башку. Решил доучиться!

Стаж..... Да кто его знает. лет 6 назад первую таблицу создал. Но 3 года вообще не программировал ни в чем. Да и программируя не каждый день составляешь запросы.

и еще вопрос в чем смысл именования когда можно обойтись и без переименования

Смысл в сокращении записи. Если можно обойтись, то можно и не переименовывать. Но мне проще написать C вместо classes.

аааааа теперь понятно почему так делаете,

24
select model
from pc
where price = (select max(price) from pc)
union
select model
from printer
where price = (select max(price) from printer)
union
select model
from laptop
where price = (select max(price) from laptop)
как решить 24 задачу
я не пойму как узнать можно, может подсказку кинете мне а не код, ну а если и так не пойму ну тогда код

Нужно не объединять выборку, а выбирать из объединения

select product.model
from (select max(price) p
from pc
union
select max(price) p
from laptop
union
select max(price) p
from printer) r, product
where product.model = (select model from product, r where max(r.p) in (select price from printer, pc, laptop))

это максимум что я смог

SELECT model FROM( SELECT model, price       FROM pc     UNION       SELECT model, price       FROM laptop     UNION       SELECT model, price       FROM printer) t1 WHERE price =( SELECT max(price)                FROM( SELECT model, price                      FROM pc                    UNION                      SELECT model, price                      FROM laptop                    UNION                      SELECT model, price                      FROM printer )t2 )

select distinct maker
from product join
( select model from pc where speed = (select max(speed) from pc where ram = (select min(ram) from pc))) s
on s.model = product.model
group by maker
это задача номер 25 у меня решение правильное только он не прошел тест.
да и как я могу узнать здесь производителей принтеров.
если я пишу так

select distinct maker
from product join
( select model from pc where speed = (select max(speed) from pc where ram = (select min(ram) from pc))) s
on s.model = product.model
group by maker
having product.type = 'printer'
он мне пишет ошибку какую то
ну ни как не пойму где ошибка

Тут я не знаю как вам подсказать, не раскрыв все решение.

Там JOIN в JOIN'e с подзапросом в подзапросе подзапроса! Короче SELECT на SELECT'e едет и SELECT'ом погоняет.

SELECT DISTINCT maker
FROM Product
WHERE type = 'printer' AND
maker IN(SELECT maker
FROM Product
WHERE model IN(SELECT model
FROM PC
WHERE speed = (SELECT MAX(speed)
FROM (SELECT speed FROM PC
WHERE ram=(SELECT MIN(ram) FROM PC)) p)))

у меня как в FAQ но он все равно говорит что ответ верный только решение не правильное

SELECT DISTINCT t1.maker FROM( SELECT DISTINCT maker       FROM product       WHERE type='printer' )t1 JOIN( SELECT DISTINCT pr.maker       FROM product pr       JOIN( SELECT *             FROM pc             WHERE speed=( SELECT max(speed)                           FROM pc                           WHERE ram=( SELECT min(ram) FROM pc) )             AND ram=( SELECT min(ram) FROM pc) )bpmr       ON pr.model=bpmr.model )t2 ON t1.maker = t2.maker

-----------------------------
P.S. Больше не подскажу. В таком "прохождении" нет смысла.

да так будет лучше, я вроде бы надеюсь что найду ответ и наверное не очень стараюсь))

да это так и есть.

Кстати, после решения задач смотрите форум по этой задаче (под условием ссылка появляется) и анализируйте свое и чужие решения. Много нового узнаете.

А я закончил обучающий этап на 1ом месте! Рейтинг - 1ый, 199 очков =)))

ухты классно поздравляю

и еще как можно сделать так чтобы когда в моей теме был ответ, ко мне на почту приходило уведомление

никак

ладно придется тогда проверять