SQL

Создание таблицы:
CREATE TABLE user_groups (
id int NOT NULL,
PRIMARY KEY (id)
);

> Пристегиваем макс. значение к каждой записи:

SELECT

t1.book, t1.hits, t2.maximum FROM books t1,
(select max(hits) as maximum from books ) t2

---

> Выбираем три рандомных записи:

SELECT books.picture FROM books WHERE deleted = 0 ORDER BY RAND() LIMIT 3

---

> Вложенный SELECT:
SELECT authors.author, authors.author_id, cnt, authors.photo, authors.describe FROM authors LEFT JOIN (SELECT distinct bookauthor.author, Count() as cnt FROM bookauthor group by bookauthor.author ) AS authorcount ON authors.author_id = authorcount.author
**
> И еще:
SELECT events.,
users.name AS author_name,
users.img AS author_img,
communities.group_name AS group_name,
communities.group_img AS group_img, counts.registered_count
FROM events
LEFT JOIN communities ON communities.id = group_id
LEFT JOIN users ON users.id = author_id
LEFT JOIN(
SELECT
event_id,
COUNT() as registered_count
FROM
`registration`
GROUP BY
event_id
) AS counts
ON
counts.event_id = events.id;
*
> Подсчет уникальных записей в таблицах связанных “многие-ко-многим”
select author, count(
> Подсчет уникальных записей в таблицах связанных “многие-ко-многим”
select author, count(*) as cnt from (select distinct book, author from bookauthor) as s1 group by author order by author desc

В примере рассмотрено: есть таблица authors, books и bookauthor. bookauthor – таблица, связывающая authors и books по типу “многие ко многим”

Подсчет книг каждого автора. Добавление к одной книге несколько раз одного автора не влияет на корректность подсчета.
select `authors`.author, `authors`.author_id, sel3.cnt from authors left join
(select author, count(*) as cnt from (select distinct author, book from bookauthor) as sel2 group by author) as sel3
on `authors`.author_id = sel3.author

• Исключение:

WHERE `status`=‘1’
AND `id` NOT IN (70, 60, 50, 30, 1)