ВИБІР з попередньої запису та використання двох основних ORDER BY (ів)

користувачі:

user_id    user_name     
---------------------

 1          User A
 2          User B

відстеження:

user_id     track         
---------------------

 1           no
 2           no

програми:

user_id    date_of_application   date_ended    grade    status    
---------------------------------------------------------------

 1            2011-01-01         2011-02-28     1.0     Ended
 1            2011-02-02         2011-03-28     1.0     Ended
 1            2011-03-03         2011-04-28     (1.5)   Ended

 2            2011-01-01         2011-02-20     2.0     Ended
 2            2011-02-02         2011-03-11     2.5     Ended
 2            2011-03-03         2011-04-28     (1.0)   Ended

 1            2011-05-10              -          -      Pending
 2            2011-05-15              -          -      Pending
  • note that the table can contain multiple records of the same user as long as all its previous applications have ended (status = ended)
  • user_id is not unique (applies to the applications table only)
  • date is in yy-mm-dd format
  • date_ended and grade are only updated the instant the application has ended
  • also, I understand that it probably is recommended for 'status' to have its own table, however I would prefer that the above tables are taken as is (minus the typos and significant errors of course)

Що я хочу виконати тут, щоб отримати всі рядки WHERE статус очікує і таке, що значення для стовпця для кожного з цих завантажених рядків є значенням останній сорт (іншими словами, рядок з останньою датою_звернення ) (у дужках вище), де статус "Завершено" для цього конкретного користувача (або рядка).

Крім того, мені потрібно буде мати перші 10 рядків результату, який буде ORDERed BY ASC класу . Після цього ( 11-й рядок до кінцевого рядка ), буде ORDERed date_of_application ASC .

Ясно, що SQL-запити не є моїм найсильнішим регіоном, тому я не впевнений, чи краще (або можливо тільки) виконувати ці ЗАМОВЛЕННЯ (-и), використовуючи 2 або більше запитів. Проте я волію, щоб це було зроблено лише за одним запитом.

Бажаний результат:

user_id   user_name   date_of_application   grade   status   track 
--------------------------------------------------------------------

 1         User A         2011-05-10        (1.5)    Pending    no
 2         User B         2011-05-15        (1.0)    Pending    no

Working code I have so far on my end [minus the possible typos], (and listed are additions to be applied):

  • останній сорт
  • ЗАМОВИТИ за класом (перші 10), ORDER BY date_of_application (з 11 до останнього рядка)

Запит:

SELECT users.user_name,
       t.track,
       a.user_id,
       a.date_of_application,
       a.status,
       (SELECT ae.grade
          FROM applications AS ae
          WHERE ae.status = 'Ended' 
            AND ae.user_id = a.user_id                                                            
         LIMIT 1) AS grade
  FROM users
  JOIN applications AS a ON users.user_id = a.user_id
  JOIN tracking AS t ON users.user_id = t.user_id
 WHERE a.status = 'Pending'
ORDER BY grade ASC
0
Це в основному репост із старого питання, опублікованого тут. Що трапилось з відповідями та коментарями там?
додано Автор wildplasser, джерело

2 Відповіді

Ви, напевно, намагаєтеся зробити забагато в одному запиті тут.

У всякому разі, якщо ви хочете щось пошкодити очі:

 select a.* from
 (
 SELECT u.user_name,
 a.user_id,
 a.date_of_application,
 td.grade,
 a.status,
 t.track
 FROM users u
 JOIN applications AS a ON u.user_id = a.user_id
 JOIN tracking AS t ON u.user_id = t.user_id
 LEFT OUTER JOIN
 (
 select ap.user_id,ap.grade 
 from applications ap
 inner join
 (select a.user_id,max(date_ended) as max_ended_date
 from applications a
 where a.status = 'Ended'
 group by a.user_id
 ) md on md.user_id = ap.user_id and ap.date_ended = md.max_ended_date
 ) as td on u.user_id = td.user_id
 WHERE a.status = 'Pending'
 ORDER BY cast(replace(replace(td.grade,'(',''),')','') as decimal(12,2)),u.user_id ASC
 LIMIT 10
 ) a
 WHERE grade is not null
 UNION ALL
 select b.* from
 (
 SELECT u.user_name,
   u.user_id,
   a2.date_of_application,
   td.grade,
   ifnull(a2.status,'No applications yet') as status,
   t2.track
 FROM users u
 LEFT OUTER JOIN (select user_id,date_of_application,status from applications where     status = 'Pending') AS a2 ON u.user_id = a2.user_id
 JOIN tracking AS t2 ON u.user_id = t2.user_id
 LEFT OUTER JOIN
 ( 
 select ap.user_id,ap.grade 
 from applications ap
 inner join
  (select a.user_id,max(date_ended) as max_ended_date
  from applications a
  where a.status = 'Ended'
  group by a.user_id
 ) md on md.user_id = ap.user_id and ap.date_ended = md.max_ended_date
 ) as td on u.user_id = td.user_id
 where u.user_id not in (
 select t1.user_id
 from (
 select ap1.user_id,ap1.grade 
  from applications ap1
  inner join
  (select a1.user_id,max(date_ended) as max_ended_date
   from applications a1
   where a1.status = 'Ended'
   group by a1.user_id
  ) md1 on md1.user_id = ap1.user_id and ap1.date_ended = md1.max_ended_date
  order by cast(replace(replace(ap1.grade,'(',''),')','') as decimal(12,2)),md1.user_id asc
  limit 10
  ) as t1
 )
 ORDER BY status desc,a2.date_of_application ASC
 ) b;

Це робить наступні припущення:

  1. У кожному users і завжди є лише один рядок для кожного user_id tracking таблиці

EDIT

Щоб трохи пояснити цей запит:

Вбудований вигляд за назвою a (aka 'Top Half') повертає список 10 кращих користувачів за їхньою останньою категорією "закінчення", що зростає. Зверніть увагу на наступну частину запиту, яка знімає будь-які квадратні дужки з класу, перетворює отримане число в десятковий до 2 знаків після коми і наказує їх за зростанням за класом, а потім, у випадку рівних оцінок за категоріями, за допомогою user_id:

ORDER BY cast(replace(replace(td.grade,'(',''),')','') as decimal(12,2)),u.user_id ASC

Вбудований вигляд b практично такий самий, як і вбудований вигляд a , окрім того, що виключає користувачів, які з'являться в верхній половині, і замовляє результати за статусом DESC (для переміщення цих користувачів без застосування до нижньої частини списку) та дату застосування ASC .

2
додано
Ваше перше припущення було правильним. Що стосується другого; добре, не завжди, користувач отримує рядок у таблиці додатків із статусом "Очікує" лише тоді, коли цей користувач фактично зареєструє програму. Також зауважте, що користувач може мати статус «Очікує», якщо всі інші його програми мають «Завершено», або це найперша програма цього користувача. Точно так само користувачеві одночасно допускається максимум 1 прикладної програми "Очікує". Який-небудь мій коментар вимагає зміни коду, який ви опублікували? Якщо це так, я дуже ціную це, якщо ви допоможете мені у цьому.
додано Автор Angelo Tan, джерело
Я щойно протестував ваш переглянутий код, і все, здається, працює як планувало, крім одного; перші 10 отриманих рядків з'явилися в порядку зниження в залежності від класу, а не необхідного зростаючого порядку. Я намагався змінити частини коду, що містить ASC/DESC, але це лише погіршує результат.
додано Автор Angelo Tan, джерело
Мої вибачення, якщо це стане вже неприємним, але зміна насправді призвела до погіршення наведеної таблиці. Я не думаю, що проблема пов'язана зі значеннями таблиці, які я тестую кодом з моменту, коли вихідний код дає передбачуваний результат бездоганно (за винятком зниження рівня випуску). Я спробував всі можливі комбінації ASC/DESC на частинах коду, які використовують 'cast', і той, який використовує DESC та DESC (оригінальна комбінація), дає кращі результати.
додано Автор Angelo Tan, джерело
Це трохи. Поправляли відповідь, щоб відобразити, що в таблиці додатків може не бути рядка. Всі інші речі повинні бути в порядку.
додано Автор Tom Mac, джерело
Хто. Помилка там Лінія 24 була єдиною, яка потребувала переходу від DESC до ASC . Ви зробили редагування.
додано Автор Tom Mac, джерело
Ах Моє погане знову. Я змінив DESC в ASC у верхньому лівому представленні, але не став його змінювати у виді списку NOT EXISTS (частина, яка виключає user_ids в 10 найвищих оцінок з рядків 11 і далі). Я його знову змінив і перевірив (двічі), щоб він зараз працював.
додано Автор Tom Mac, джерело

Це має працювати добре для вас ... Щоб з'ясувати, що відбувається, вам слід почати з внутрішньої частини запиту. Для кожного користувача знайдіть найвищу дату "Очікує" (оскільки ви вказали, що це буде лише один), а останню дату класу "Завершено". Групування за користувачем. Це гарантує один запис для кожного користувача, обидва обчислені вгору як PreQuery.

Далі, самостійно приєднуйтесь до таблиці застосунків TWICE ... один раз користувачем та датою останньої дати, далі користувачем та останньою датою очікування. Роблячи LEFT JOIN, якщо у вас є лише людина з додатком і нема кінця, вони будуть включені ... так само, якщо тільки завершений клас, у якому немає більше очікуваної програми, вони також будуть включені.

Витягніть відповідні стовпці з цих псевдонімів, щоб отримати оцінку. Поки ми знаходимося на ньому, використовуючи змінні SQL, і використовуючи порядок цього запиту за Оцінювання DESCENDING, найкращі оцінки буде від 1 до n, незважаючи на дату застосування.

Нарешті, візьміть весь цілий набір і виконайте спеціальний порядок за ... Порядок за умови, що якщо користувацький ранг менше, ніж 11, використовуйте його порядок. В іншому випадку дозвольте всім іншим мати однакову "11" значення для першого порядку частково ... Після цього замовте за датою подання заявки.

Малі шматки, що спираються на попередній набір. І це не повинно завдати шкоди вашій голові, і не вимагає жодних спілок

select
      QryRank.*
   from
      ( select
            PreQuery.User_ID,
            usr.user_Name,
            trk.Track,
            PreQuery.LastEnded,
            appEnd.Grade,
            PreQuery.LastPend as Date_Of_Application,
            @Rank := @Rank +1 UserRank
         from
            ( select
                  app.user_id,
                  max( if( app.status = "Ended", date_ended, null ) ) as LastEnded,
                  max( if( app.status = "Pending", app.date_of_application, null )) LastPend
               from
                  Applications app
               group by
                  app.user_id ) PreQuery

            LEFT JOIN Applications appEnd
               on PreQuery.User_ID = appEnd.User_ID
              AND PreQuery.LastEnded = appEnd.date_ended

            LEFT JOIN Applications appPend
               on PreQuery.User_ID = appPend.User_ID
               AND PreQuery.LastPend = appPend.date_of_application

            join Users usr   
               on PreQuery.user_id = usr.user_id

            join Tracking trk
               on PreQuery.user_id = trk.user_id,

            ( select @Rank := 0 ) sqlvars

         order by
            appEnd.Grade DESC ) QryRank
    order by
       if( QryRank.UserRank < 11, QryRank.UserRank, 11 ),
       QryRank.Date_Of_Application
1
додано