ReSQL.ru
     
powered by simpleCommunicator - 2.0.61     © 2026 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / MySQL [игнор отключен] [закрыт для гостей] / sql запрос: 2 связанных таблицы, только 10 записей
22 сообщений из 22, страница 1 из 1
sql запрос: 2 связанных таблицы, только 10 записей
    #38532155
linki
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
linki
Гость
Данный запрос считает количество постов определенного типа (posts.tip = 0) для каждой рубрики:
SELECT dirs.name, dirs.id, SUM(!posts.tip) AS nn FROM dirs LEFT JOIN posts ON posts.rid=dirs.id WHERE dirs.sid=0 GROUP BY dirs.id

ВОПРОС: Как посчитать то-же самое (количество posts.tip=0 для каждой рубрики) - но для последних 10 записей posts?


dirs - рубрики
posts - посты в рубриках

posts.tip = 0 - картинка
posts.tip = 1 - текст

Последние 10 записей - это то, что выводится на первой странице каждого раздела dirs. Я хочу посчитать сколько будет постов-картинок на первой странице каждого раздела.
...
Рейтинг: 0 / 0
20.01.2014, 17:18:03
| Ответить | Цитировать | Написать  
sql запрос: 2 связанных таблицы, только 10 записей
    #38532217
Cygapb-007
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Cygapb-007
Участник
навскидку
Код: sql
1.
where ... and (select count(*) from posts e where e.rid=posts.rid and e.id>posts.id)<=10
...
Рейтинг: 0 / 0
20.01.2014, 17:57:41
| Ответить | Цитировать | Написать  
sql запрос: 2 связанных таблицы, только 10 записей
    #38532345
linki
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
linki
Гость
Так?
Код: sql
1.
SELECT dirs.name, dirs.id, SUM(!posts.tip) AS nn FROM dirs LEFT JOIN posts ON posts.rid=dirs.id WHERE dirs.sid=0 and (select count(*) from posts e where e.rid=posts.rid and e.id>posts.id)<=10 GROUP BY dirs.id



Минут 5 как пытается обработать.

Да, в таблице posts - 60.000 записей.
...
Рейтинг: 0 / 0
20.01.2014, 19:52:33
| Ответить | Цитировать | Написать  
sql запрос: 2 связанных таблицы, только 10 записей
    #38532401
Cygapb-007
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Cygapb-007
Участник
тогда FAQ: Нумерация строк и другие вопросы про использование переменных
...
Рейтинг: 0 / 0
20.01.2014, 20:50:52
| Ответить | Цитировать | Написать  
sql запрос: 2 связанных таблицы, только 10 записей
    #38532563
linki
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
linki
Гость
Cygapb-007,
на маленькой таблице (1000 записей) хорошо отработал. А большая - долго висит до таймаута.
...
Рейтинг: 0 / 0
21.01.2014, 00:28:57
| Ответить | Цитировать | Написать  
sql запрос: 2 связанных таблицы, только 10 записей
    #38532566
linki
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
linki
Гость
большая - это 60.000 записей
...
Рейтинг: 0 / 0
21.01.2014, 00:32:11
| Ответить | Цитировать | Написать  
sql запрос: 2 связанных таблицы, только 10 записей
    #38532929
AlexeyVD
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
AlexeyVD
Участник
Вот вариант с переменными. Я его не тестил, но думаю смысл понятен.

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
SELECT 
    sel.name
  , sel.dir_id
  , SUM(!sel.trip)
  , SUM(!sel.trip AND sel.top10)
  FROM (
              SELECT 
                  dirs.name
                , dirs.id AS dir_id
                , posts.trip
                , posts.id AS post_id
                , IF(@cur_id != dirs.id, @i := 0, @i := @i + 1)
                , IF(@i < 10, 1, 0) AS top10
                , IF(@cur_id != dirs.id, @cur_id := dirs.id, @cur_id)
                FROM dirs
                JOIN posts ON posts.rid = dirs.id
                JOIN (SELECT @i := 0, @cur_id := 0) v
                ORDER BY dirs.id, posts.id DESC
          ) sel
  GROUP BY sel.dir_id
...
Рейтинг: 0 / 0
21.01.2014, 12:31:21
| Ответить | Цитировать | Написать  
sql запрос: 2 связанных таблицы, только 10 записей
    #38533640
linki
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
linki
Гость
AlexeyVD,

SUM(!sel.tip) - итого посчитало верно =500 для первой рубрики.
SUM(!sel.tip AND sel.top10) =432 - а должно же быть не более 10.
...
Рейтинг: 0 / 0
21.01.2014, 19:58:10
| Ответить | Цитировать | Написать  
sql запрос: 2 связанных таблицы, только 10 записей
    #38533665
Cygapb-007
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Cygapb-007
Участник
http://sqlfiddle.com/#!2/1d7b9c/1
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
select id, name, count(*) qty, ifnull(sum(tip=0),0) tip0
from (
  select d.name, d.id, p.tip, p.id pid, @npp:=if(@id=d.id,@npp+1,(@id:=d.id)/d.id-1) npp
  from dirs d
  left join (
    select id,rid,tip from posts t order by t.rid, t.id desc
    )p on p.rid=d.id
  where d.sid=0
  )n
where n.npp<10
group by id;
...
Рейтинг: 0 / 0
21.01.2014, 20:22:32
| Ответить | Цитировать | Написать  
sql запрос: 2 связанных таблицы, только 10 записей
    #38533689
linki
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
linki
Гость
Cygapb-007,

Получилось для первой рубрики:
qty=9826 - общее кол-во в рубрике
tip0=500 - общее кол-во с tip==0

Посчитать именно в десятке последних так и не получилось :(
...
Рейтинг: 0 / 0
21.01.2014, 20:56:23
| Ответить | Цитировать | Написать  
sql запрос: 2 связанных таблицы, только 10 записей
    #38533986
Cygapb-007
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Cygapb-007
Участник
Да, на малом объеме тот вариант работает, но смущало бесполезное вложенное select order by...
Вот так вроде должно сработать, причем довольно быстро...
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
select d.id, d.name, count(*) qty, ifnull(sum(p.tip=0),0) tops
from (
  select id pid,rid,tip,
    @npp:=if(@id=t.rid,@npp+1,(@id:=t.rid)/t.rid-1) npp
  from posts t 
  order by t.rid, t.id desc
  )p
right join dirs d on d.id=p.rid
where ifnull(p.npp,0)<10
group by d.id;
...
Рейтинг: 0 / 0
22.01.2014, 10:34:36
| Ответить | Цитировать | Написать  
sql запрос: 2 связанных таблицы, только 10 записей
    #38533992
Cygapb-007
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Cygapb-007
Участник
забыл в join дописать and d.sid=0
...
Рейтинг: 0 / 0
22.01.2014, 10:37:10
| Ответить | Цитировать | Написать  
sql запрос: 2 связанных таблицы, только 10 записей
    #38534219
AlexeyVD
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
AlexeyVD
Участник
linkiAlexeyVD,

SUM(!sel.tip) - итого посчитало верно =500 для первой рубрики.
SUM(!sel.tip AND sel.top10) =432 - а должно же быть не более 10.
Проверил запрос, правда на своих таблицах, сделал похожую выборку. Всё прекрасно отработало. Проверьте как следует не забыли ли вы где-нибудь чего-нибудь. Я же точнее подсказать вам не могу, т.к. вашей базы у меня нет.
...
Рейтинг: 0 / 0
22.01.2014, 13:19:08
| Ответить | Цитировать | Написать  
sql запрос: 2 связанных таблицы, только 10 записей
    #38534913
linki
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
linki
Гость
Cygapb-007,
не работает, отдает tops=500
а должно не более 10.
:(
...
Рейтинг: 0 / 0
22.01.2014, 21:07:16
| Ответить | Цитировать | Написать  
sql запрос: 2 связанных таблицы, только 10 записей
    #38551336
linki
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
linki
Гость
Разбирал по частям:
@npp:=if(@id=t.rid,@npp+1,(@id:=t.rid)/t.rid-1) npp
у меня возвращает для всех = 0
...
Рейтинг: 0 / 0
06.02.2014, 04:17:35
| Ответить | Цитировать | Написать  
sql запрос: 2 связанных таблицы, только 10 записей
    #38551338
linki
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
linki
Гость
А запрос:
select id pid,rid,tip, @id as tmp,
@npp:=if(@id=t.rid,@npp+1,(@id:=t.rid)/t.rid-1) npp
from posts t order by t.rid, t.id desc

pid rid tip tmp npp
1590 1 1 NULL 0
1589 1 1 NULL 0

Т.е. @id для всех строк NULL.
...
Рейтинг: 0 / 0
06.02.2014, 04:25:56
| Ответить | Цитировать | Написать  
sql запрос: 2 связанных таблицы, только 10 записей
    #38551339
linki
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
linki
Гость
MySQL
5.5.25 - MySQL Community Server (GPL)
...
Рейтинг: 0 / 0
06.02.2014, 04:29:12
| Ответить | Цитировать | Написать  
sql запрос: 2 связанных таблицы, только 10 записей
    #38551341
Фотография javajdbc
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
javajdbc
Участник
linki,

вы не забыли проинициализировать переменные?
...
Рейтинг: 0 / 0
06.02.2014, 05:31:03
| Ответить | Цитировать | Написать  
sql запрос: 2 связанных таблицы, только 10 записей
    #38551342
Фотография javajdbc
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
javajdbc
Участник
когда делаете топ-10 выборку, надо следить
за памятью на больших обьемах.
Если процее вылетает на диск -- плохо однозначно.

Один из способов избежать:
сделайте на переменным сначала одну выборку ТОЛЬКО ИД постов
(или что там у вас),
затем эти ИД сжините опять на нужные таблицы и подсчитывайте агрегаты.

А вообше, попробуйте сделать предварительный подсчет агрегатов.
Ну скажем раз в день или раз в час. Неужели
вам такой результат нужен прямо в реал-тайм ?
...
Рейтинг: 0 / 0
06.02.2014, 05:36:31
| Ответить | Цитировать | Написать  
sql запрос: 2 связанных таблицы, только 10 записей
    #38551344
linki
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
linki
Гость
javajdbc,

http://sqlfiddle.com/#!2/c9d26/1 - тут все работает.
повторяю ВСЕ один в один (создание таблиц, запросы) у себя - не работает

select d.name, d.id, p.tip, p.id pid, @npp:=if(@id=d.id,@npp+1,(@id:=d.id)/d.id-1) npp
from dirs d
left join (
select id,rid,tip from posts t order by t.rid, t.id desc
)p on p.rid=d.id
where d.sid=0

@id - все время NULL

Может какая настройка нужна?
...
Рейтинг: 0 / 0
06.02.2014, 05:50:08
| Ответить | Цитировать | Написать  
sql запрос: 2 связанных таблицы, только 10 записей
    #38551345
linki
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
linki
Гость
И вообще в phpMyAdmin:
SET @var1='RUS';
SELECT @var1;

Отдает:
@var1
NULL
...
Рейтинг: 0 / 0
06.02.2014, 06:02:31
| Ответить | Цитировать | Написать  
sql запрос: 2 связанных таблицы, только 10 записей
    #38551453
Фотография javajdbc
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
javajdbc
Участник
linkijavajdbc,

http://sqlfiddle.com/#!2/c9d26/1 - тут все работает.
повторяю ВСЕ один в один (создание таблиц, запросы) у себя - не работает

select d.name, d.id, p.tip, p.id pid, @npp:=if(@id=d.id,@npp+1,(@id:=d.id)/d.id-1) npp
from dirs d
left join (
select id,rid,tip from posts t order by t.rid, t.id desc
)p on p.rid=d.id
where d.sid=0

@id - все время NULL

Может какая настройка нужна?

http://sqlfiddle.com/#!2/c9d26/1 ух ты! создатели этой програмы
перестарались! Настояший MySQL требует иницилизации
параметров. (По крайней мере я не видел что работало
без инициализации).

Попробуйте так (логику не проверял, я просто добавил инициализацию):

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
select 
  d.name, 
  d.id, 
  p.tip, 
  p.id pid, 
  @npp:=if(@id=d.id,@npp+1,(@id:=d.id)/d.id-1) npp
from
  (
    select @id:=0,@npp:=0
  ) zz
  join 
  dirs d
  left join 
  (
    select id,rid,tip from posts t order by t.rid, t.id desc
  )p on p.rid=d.id
where 
  d.sid=0
...
Рейтинг: 0 / 0
06.02.2014, 09:47:22
| Ответить | Цитировать | Написать  
22 сообщений из 22, страница 1 из 1
Форумы / MySQL [игнор отключен] [закрыт для гостей] / sql запрос: 2 связанных таблицы, только 10 записей
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение   Полит. конфиден.  
NoSQL.ru       Кролег: Проекты, Новости, Чат       РЕД ФОРУМ       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=47&msg=38532217&tid=1835267]:
 0ms
get settings:
 6ms
get forum list:
 14ms
check forum access:
 3ms
check topic access:
 3ms
track hit:
 47ms
get topic data:
 8ms
get forum data:
 2ms
get page messages:
 57ms
get tp. blocked users:
 1ms
others: 209ms
total:  350ms
созд. / загр.: 350ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]