Форум пользователей MySQL

zvv1992 · 03.12.2011 23:01:20

Задача:
3. Вывести средний возраст тех спортсменов, кто когда-либо проводил тренировки (т.е. был в качестве тренера).

dump базы. (если нужно) http://share.auditory.ru/2014/Viktor.Zaytsev/bd/vol.sql

Имеются таблицы участников и тренировок, у тренировок поле с id'шником тренера.
Когда я отправил этот запрос:
SELECT AVG(`members`.`age`) FROM `members`, `training` WHERE `members`.`id` = `training`.`coach`
Возвращается среднее, зависимое от количества участий какого либо тренера, т.е.
У нас есть тренер 1, ему 40 лет, есть 2ой, ему 30 лет.
Пусть 1ый провел 2 занятия, а второй одно.
Их среднее должно быть (40+30)/2, а запрос выводит (40+40+30)/3 (понимаю почему).

Помогите люди добрые , какой будет правильный запрос?

Отредактированно zvv1992 (03.12.2011 23:02:35)

Прикрепленные файлы:

model.png, Размер: 44,898 байт, Скачано: 392

Neval · 04.12.2011 00:07:12

Добавьте группировку по `training`.`coach`, хотя мне кажется, что результат тоже будет не совсем правильный, надо проверять.

100% рабочий вариант: выборка делается в 2 шага, можно использовать подзапрос. Сначала надо определить id тех спортсменов, кто когда-либо был тренером, а уже потом определять AVG по найденным записям.

zvv1992 · 05.12.2011 04:22:06

Кому интересно - вот команда выборки
SELECT AVG( `members`.`age` )
FROM `members` , `training`
WHERE `members`.`age` IN (SELECT `members`.`age` FROM `members`, `training` WHERE `training`.`coach` = `members`.`id`)

Вспомогательный материал : http://mysql.ru/docs/gruber/mg10.html

Нифига она неверна ='(
Вот оно решение моей мечты (сам, кстати, переделал):

SELECT AVG( `members`.`age` )
FROM `members` JOIN `training` ON `members`.`id` = `training`.`coach`

Отредактированно zvv1992 (15.12.2011 01:57:13)

zvv1992 · 06.12.2011 05:25:25

Спасибо большое за помощь!!!
И так...
Новая проблемка
Пишу на php запрос для БД.
Идея такая:

Имеется 3 таблицы:
1. Таблица новостей.
2. комментарии.
3.связующая. (2 поля - idновости, idкомментария).

Сделать выборку комментариев для новости. Желательно выборка с использованием php, а не только sql.

Пытался сделать так:

* выборка по связующей таблице по idновости - на выходе получается, скажем, 3 поля.
idNum
1
3
4
* создать массив из этих чисел - этот шаг я и не осуществил
* выборка из таблицы комментариев по idкомментария

Врятли нужно, но:
$com_id_query = "SELECT `idCom` FROM `commentnews` WHERE `idNum` = '$id'";
$com_id_result = mysql_query($com_id_query);
$com_id_row = mysql_fetch_row($com_id_result);//достает строку,а не столбик
$com_rows = mysql_num_rows($com_id_result);
for($j = 0;$j < $com_rows;$j++)
{
$com_query = "SELECT `date-time`, `nick`, `email`, `comment` FROM `comments` WHERE `id`='$com_id_row[$j]'";
$com_row = mysql_fetch_row(mysql_query($com_query));

echo <<<_END
<tr>
<td colspan="2">$com_row[0]</td>
</tr>
<tr>
<td>$com_row[1]</td>
<td>$com_row[2]</td>
</tr>
<tr>
<td colspan="2">$com_row[3]</td>
</tr>
</table>
_END;
}

deadka · 06.12.2011 13:01:01

zvv1992 написал:
Сделать выборку комментариев для новости. Желательно выборка с использованием php, а не только sql.

Что Вы имели эти в виду?

Сразу возникает вопрос - зачем Вы заводили таблицу связей новостей и комментариев? Ведь каждый комментарий относится только к одной новости - не проще ли было просто из таблицы комментариев сделать ссылку на таблицу новостей?

zvv1992 написал:
$com_id_row = mysql_fetch_row($com_id_result);//достает строку,а не столбик

Данные в принципе можно извлекать только строчно.

С Вашей структурой, думаю, лучше как-то так написать:

$com_query = "SELECT `date-time`, `nick`, `email`, `comment` FROM `comments` WHERE `id` in (SELECT `idCom` FROM `commentnews` WHERE `idNum` = " . $id . ")";

$query_result = mysql_query($com_id_query);

$com_rows = mysql_num_rows($query_result);

for($j = 0;$j < $com_rows;$j++)

{

   $com_row = mysql_fetch_row($query_result);

   // Здесь могла быть Ваша реклама, то есть выводите html, используя поля из $com_row

}

zvv1992 · 06.12.2011 16:45:13

Ох...совсем заработался )
И вправду зачем мне связующая таблица.
Вобщем таблицы оставил две, и вот результат:

    $com_query = "SELECT `date-time`, `nick`, `email`, `comment` FROM `comments` WHERE `idN` = '$id'";

    $com_result = mysql_query($com_query);

    $com_rows = mysql_num_rows($com_result);

    for($j = 0;$j < $com_rows;$j++)

    {

        $com_row = mysql_fetch_row($com_result);

echo <<<_END

        <tr>

            <td colspan="2" height="20px">$com_row[0]</td>

        </tr>

        <tr>

            <td height="30px">$com_row[1]</td>

            <td  height="30px"><a href="mailto:$com_row[2]"></a></td>

        </tr>

        <tr>

            <td colspan="2"  height="100px">$com_row[3]</td>

        </tr>

_END;

    }

Спасибо большое!

Отредактированно zvv1992 (06.12.2011 17:04:09)

deadka · 06.12.2011 16:48:04

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

zvv1992 · 07.12.2011 12:43:31

Вот и новая проблема....сдаю БД.
Задача:
создать функцию, выводящую среднее значение по всем полям, не испоьлзуюя функцию AVG.
Моя версия выдает ошибку:

SELECT sr();

ERROR 1111 (HY000): Invalid use of group function

DELIMITER //

CREATE FUNCTION `sr` ()

   RETURNS BIGINT UNSIGNED 

   BEGIN 

      DECLARE i BIGINT UNSIGNED; 

      DECLARE r BIGINT UNSIGNED; 

      DECLARE m BIGINT UNSIGNED; 

      SET m=MAX(`id`); 

      SET i=SUM(`q`); 

      SET r=i/m;

      RETURN r; 

      END 

      // DELIMITER ;

Отредактированно zvv1992 (07.12.2011 12:44:11)

zvv1992 · 07.12.2011 13:11:42

Изменил функцию:

DELIMITER //

CREATE FUNCTION `sr` (m BIGINT UNSIGNED, i BIGINT UNSIGNED)

RETURNS FLOAT

BEGIN

DECLARE r FLOAT; 

SET r = i/m;  

RETURN r; END

// DELIMITER ;

Выводит теперь ошибку:
ERROR 1305 (42000): FUNCTION q.sr does not exist

Отредактированно zvv1992 (07.12.2011 13:13:07)

zvv1992 · 15.12.2011 01:58:33

Верная версия и остальные запросы:
процедура добавляет 100 записей в таблицу/
функция находит стреднее, не используя avg
триггер выводит фукцию при любом добавлении данных в таблицу.

mysql -u root -p

USE `db`

CREATE `table` (`id` SERIAL AUTO_INCREMENT, `col` INT);

DELIMITER //

CREATE PROCEDURE `ar`()

BEGIN

DECLARE x INT;

   SET x = 0;

   WHILE (x < 100) DO

       INSERT INTO `table` (`col`) VALUES(RAND());

       SET x = x+1;

   END WHILE;

END;

// DELIMITER;

SELECT `ar`();

DELIMITER //

CREATE FUNCTION `sred`()

    RETURNS FLOAT

    BEGIN

    DECLARE x INT DEFAULT 1;

    DECLARE summa INT DEFAULT 0;

    SET @sr = 0;

    WHILE (SELECT `col` FROM `ar` WHERE `id`=x) DO

        SET summa = summa + (SELECT `col` FROM `ar` WHERE `id`=x);

        SET x = x+1;

    END WHILE;

    SET @sr = summa / (x-1);

    RETURN @sr;

// DELIMITER;

SELECT `sred`();

// DELIMITER;

DELIMITER //

CREATE TRIGGER `vyv_sr` AFTER INSERT ON `table` FOR EACH ROW

    BEGIN

        SELECT `sred`();

    END;

// DELIMITER;

P.S. не спрашивайте почему такие странные названия, но то была веская причина!

Отредактированно zvv1992 (15.12.2011 01:59:47)

Форум пользователей MySQL

#1 03.12.2011 23:01:20

Не могу написать правильный запрос на MySQL

#2 04.12.2011 00:07:12

Re: Не могу написать правильный запрос на MySQL

#3 05.12.2011 04:22:06

Re: Не могу написать правильный запрос на MySQL

#4 06.12.2011 05:25:25

Re: Не могу написать правильный запрос на MySQL

#5 06.12.2011 13:01:01

Re: Не могу написать правильный запрос на MySQL

zvv1992 написал:

zvv1992 написал:

#6 06.12.2011 16:45:13

Re: Не могу написать правильный запрос на MySQL

#7 06.12.2011 16:48:04

Re: Не могу написать правильный запрос на MySQL

#8 07.12.2011 12:43:31

Re: Не могу написать правильный запрос на MySQL

#9 07.12.2011 13:11:42

Re: Не могу написать правильный запрос на MySQL

#10 15.12.2011 01:58:33

Re: Не могу написать правильный запрос на MySQL

Board footer