16 марта, 2011, обновлено 20 февраля, 2016

MySQL

Пройдемся по SQL

SELECT
    SQL_CALC_FOUND_ROWS `публикации`.`id`,
    CONCAT(
        DATE_FORMAT(`публикации`.`date`, '%e '),
        ELT(MONTH(`публикации`.`date`),'января','февраля','марта','апреля','мая','июня','июля','августа','сентября','октября','ноября','декабря'),
        DATE_FORMAT(`публикации`.`date`, ', %Y')
    ) AS `date`,
    `публикации`.`heading`,
    `публикации`.`introtext`,
    COUNT(DISTINCT `комментарии`.`id`) AS `coms`,
    CONVERT(GROUP_CONCAT(DISTINCT `связькп`.`cat` SEPARATOR '::') USING cp1251) AS `catid`,
    GROUP_CONCAT(DISTINCT `категории`.`heading` SEPARATOR '::') AS `catname`
FROM
    `публикации`
LEFT OUTER JOIN
    `комментарии` ON `комментарии`.`pub` = `публикации`.`id`
INNER JOIN
    `связькп` ON `связькп`.`pub` = `публикации`.`id`
INNER JOIN
    `категории` ON `категории`.`id` = `связькп`.`cat`
WHERE
    `публикации`.`show` = 'true'
GROUP BY
    `публикации`.`id`
ORDER BY
    `публикации`.`date` DESC, `публикации`.`id` DESC
LIMIT
    ${startlist}, ${limitlist}

SQL_CALC_FOUND_ROWS — позволяет при последующем вызове команды SELECT FOUND_ROWS(); получить количество строк которые возвратила бы первая команда SELECT, если бы она была написана без выражения LIMIT.

CONCAT(a,b,...) — возвращает строку, являющуюся результатом конкатенации аргументов. Если хотя бы один из аргументов равен NULL, возвращается NULL. Стоит отметить наличие функции CONCAT_WS(s, a, b,...) обозначает CONCAT With Separator — говорит сама за себя

DATE_FORMAT(date,format) — форматирует величину date в соответствии со строкой format. В строке format могут использоваться следующие определители:

Определитель Описание
%M Название месяца (январь...декабрь)
%W Название дня недели (воскресенье...суббота)
%D День месяца с английским суффиксом (0st, 1st, 2nd, 3rd и т.д.)
%Y Год, число, 4 разряда
%y Год, число, 2 разряда
%X Год для недели, где воскресенье считается первым днем недели, число, 4 разряда, используется с '%V'
%x Год для недели, где воскресенье считается первым днем недели, число, 4 разряда, используется с '%v'
%a Сокращенное наименование дня недели (Вс...Сб)
%d День месяца, число (00..31)
%e День месяца, число (0..31)
%m Месяц, число (00..12)
%c Месяц, число (0..12)
%b Сокращенное наименование месяца (Янв...Дек)
%j День года (001..366)
%H Час (00..23)
%k Час (0..23)
%h Час (01..12)
%I Час (01..12)
%l Час (1..12)
%i Минуты, число (00..59)
%r Время, 12-часовой формат (hh:mm:ss [AP]M)
%T Время, 24-часовой формат (hh:mm:ss)
%S Секунды (00..59)
%s Секунды (00..59)
%p AM или PM
%w День недели (0=воскресенье..6=суббота)
%U Неделя (00..53), где воскресенье считается первым днем недели
%u Неделя (00..53), где понедельник считается первым днем недели
%V Неделя (01..53), где воскресенье считается первым днем недели. Используется с '%X'
%v Неделя (01..53), где понедельник считается первым днем недели. Используется с '%x'
%% Литерал ‘%’.

ELT(N,a,b,c,...) — возвращает a, если N = 1, b, если N = 2, и так далее.

Таким образом код:

CONCAT(DATE_FORMAT(`публикации`.`date`, '%e '),
ELT(MONTH(`публикации`.`date`),'января','февраля','марта','апреля','мая','июня','июля','августа','сентября','октября','ноября','декабря'),
DATE_FORMAT(`публикации`.`date`, ', %Y')) AS `date`

в столбце `date` сформирует строки из дат в формате «3 марта 2010» из 03-05-2010

Задачки

Даны две SQL-таблицы:

employee - сотрудник

  • id
  • first_name - имя
  • last_name - фамилия
  • salary - размер зарплаты
  • department_id - отдел

department - отдел

  • id
  • name - название отдела

Вывести

  • Список имён и фамилий всех сотрудников с названиями отделов, к которым каждый из них привязан
  • Список отделов с количеством сотрудников в каждом из них
  • Список отделов, в которых нет сотрудников
  • Список отделов, к которым привязано более двух сотрудников
  • Список отделов с указанием средней и максимальной зарплаты в каждом из них, отсортированный по убыванию средней зарплаты

Решения

Список имён и фамилий всех сотрудников с названиями отделов, к которым каждый из них привязан

select
    `e`.`first_name`,
    `e`.`last_name`,
    `d`.`name`
from
    `employee` `e`
left join
    `department` `d` on `d`.`id` = `e`.`department_id`;

Список отделов с количеством сотрудников в каждом из них

select
    `d`.`name`,
    count(`e`.`id`) as `count`
from
    `department` `d`
left join
    `employee` `e` on `e`.`department_id` = `d`.`id`
group by
    `d`.`id`;

Список отделов, в которых нет сотрудников

select
    `d`.`name`
from
    `department` `d`
left join
    `employee` `e` on `e`.`department_id` = `d`.`id`
where
    `e`.`department_id` is null;

Список отделов, к которым привязано более двух сотрудников

select
    `d`.`name`,
    count(`e`.`id`) as `count`
from
    `department` `d`
left join
    `employee` `e` on `e`.`department_id` = `d`.`id`
group by
    `d`.`id`
having
    `count` > 2;

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

select
    `d`.`name`,
    max(`e`.`salary`) as `max`,
    avg(`e`.`salary`) as `avg`
from
    `department` `d`
left join
    `employee` `e` on `e`.`department_id` = `d`.`id`
group by
    `d`.`id`
order by
    `avg` desc;

Добавить комментарий

2010–2019 Блог Максима Златова, контакты

PHP execution time: 0.0094 s.
SQL execution time: 0.0013 s. (select publication with comments)

Яндекс.Метрика