Форум программистов [Powered by Invision Power Board]

Версия для печати темы
Нажмите сюда для просмотра этой темы в оригинальном формате

Форум программистов > Составление SQL-запросов > Не знаю как объяснить :)

Автор: zammar 18.12.2008, 09:55

tester_results

     str_id   str_test_id   str_user_id   str_time    str_mark    str_test_time    str_place    str_criteria   str_sdan

    24         20              7              1228165200          10        123                             1            2                 1
    22         21              6              1228251600            8        1235                           2            45               0
    20        17              4              1228338000            3        12                               5            2                 1
    16        17              2              1228597200          3           3                                6            2                 1
    21        20              5              1228510800            4        123                             4            2                 1
    15        17              3              1228683600          0           0                                7            2                 0
    25        21              7              1228770000           44        23                               0           45                0
    26        17              7              1228856400          789       789                              6            2                 1

site_users

su_id    su_name    su_login

7               aaaaaaa             aaaaaa
6             bbbbbbbbb           bbbbbbb
5                cccccccc                ccccccccc
4                ddddddd           ddddddd
3               eeeeeee              eeeeeee
2             fffffffffffff                ffffffffffffff

Код


SELECT tr.str_test_id,           tr.str_user_id,            sum(tr.str_mark) AS s_mark,               sum(tr.str_test_time) AS s_test_time,         tr.str_place, tr.str_criteria,             u.su_login,            u.su_name,                COUNT(DISTINCT(tr.str_test_id)) AS tested,        ?????   AS passed

FROM tester_results tr

LEFT JOIN sb_site_users u  

ON  u.su_id = tr.str_user_id

WHERE tr.str_test_id IN (17, 20, 21)

GROUP BY tr.str_user_id

tested - кол-во тестов которые пользователь вообще проходил
passed - кол-во тестов пройденных (где пользователь набрал кол-во баллов больше чем критерий сдачи str_mark >= str_criteria или str_sdan == 1)

Все в этом запросе нормально, а вот passed не могу никак получить. Можно это сделать в этом же запросе? И как его нужно изменить?

Добавлено через 2 минуты и 42 секунды
База MYSQL

Автор: Zloxa 18.12.2008, 10:43

Код


select u.su_id
          , count(distinct str_test_id) tested 
          , count(distinct case when str_mark   >=   str_criteria  or str_sdan = 1 then str_test_id end) passed 
 from site_users u
 left join tester_results tr on tr.su_id  = u.su_id 
 group by u.su_id

Добавлено через 3 минуты и 57 секунд
PS distinct в count нужен лишь в случае, если связка (str_test_id, str_user_id) может быть не уникальной

Автор: zammar 18.12.2008, 11:22

А я нашел вот такой выход.

Код


 SELECT tr.str_test_id, tr.str_user_id, sum( tr.str_mark ) AS s_mark, sum( tr.str_test_time ) AS s_test_time, tr.str_place, u.su_login, u.su_name, COUNT( DISTINCT (tr.str_test_id) ) AS tested, tests.passed

FROM tester_results tr
LEFT JOIN (

SELECT str_user_id, COUNT( str_test_id ) AS passed
FROM tester_results
WHERE str_passed =1
GROUP BY str_user_id
)tests
ON tests.str_user_id = tr.str_user_id
LEFT JOIN sb_site_users u 
ON tr.str_user_id = u.su_id
WHERE tr.str_test_id IN ( 17, 20, 21 )
GROUP BY tr.str_user_id

Но помоему, Zloxa, ваш по быстрее будет нет?

Автор: Zloxa 18.12.2008, 11:34

Цитата(zammar @ 18.12.2008, 11:22

)

нет?

Хоть одну причину придумать сможете, от чего нет? smile

Добавлено через 2 минуты и 49 секунд
Почему Вы выбираетесь из tester_results мне не ясно.
Я бы мог понять, если бы вам не были интересны пользователи, не проходившие тестов.
Однако тогда мне не ясно отчего используете left join а не inner.
Или Вам интересны в том числе и "фантомные"(отсутствующие в справочнике) пользователи?

Автор: zammar 18.12.2008, 12:48

Ну да.
С конструкцией case when then end
сейчас только познакомился smile

Спасибо!