Блог

Главная - Вопросы по программированию - MySQL выбирает даты истечения срока действия квалификации

MySQL выбирает даты истечения срока действия квалификации

#mysql #sql #gridview #select

#mysql #sql #просмотр сетки #выберите

Вопрос:

У меня есть три таблицы MySQL:

Пользователи: идентификатор пользователя, имя пользователя

Тесты: TestID, TestName

Проходы: PassID, userId, TestID, дата прохождения

Я хотел бы вернуть таблицу, содержащую даты ПОСЛЕДНИХ тестов, пройденных каждым пользователем, например

 |--------|---------|---------|---------|---------|---------|
|User    |Test A   |Test B   |Test C   |Test D   |Test E   |
|--------|---------|---------|---------|---------|---------|
|James   |Null     |6/3/11   |Null     |Null     |4/3/11   |
|Mark    |Null     |1/4/11   |8/5/11   |23/5/10  |Null     |
|--------|---------|---------|---------|---------|---------|

В этом примере Джеймс никогда не сдавал тесты A, C или D. Возможно, он несколько раз проходил тест B, но последний был 3/6/11.

Я планирую отобразить эти данные в ASP.NET Просмотр сетки. Каков наилучший метод для этого — можно ли это сделать в инструкции SELECT? Пожалуйста, помогите!

Заранее большое спасибо.

Ответ №1:

 select
u.username,
max(case when testid = 1 then datetaken else null end) as A,
max(case when testid = 2 then datetaken else null end) as B,
max(case when testid = 3 then datetaken else null end) as C,
max(case when testid = 4 then datetaken else null end) as D,
max(case when testid = 5 then datetaken else null end) as E
from users as u
left join passes as p on u.userid = p.userid
group by u.userid

Ответ №2:

Для достижения этого вам нужно будет использовать подзапросы для каждого результата теста Попробуйте это:

 SELECT User,
    (SELECT MAX(DateTaken) FROM Passes p INNER JOIN Tests t ON p.TestId=t.TestId WHERE p.UserId=u.UserId AND TestName='Test A') AS 'Test A',
    (SELECT MAX(DateTaken) FROM Passes p INNER JOIN Tests t ON p.TestId=t.TestId WHERE p.UserId=u.UserId AND TestName='Test B') AS 'Test B',
    (SELECT MAX(DateTaken) FROM Passes p INNER JOIN Tests t ON p.TestId=t.TestId WHERE p.UserId=u.UserId AND TestName='Test C') AS 'Test C',
    (SELECT MAX(DateTaken) FROM Passes p INNER JOIN Tests t ON p.TestId=t.TestId WHERE p.UserId=u.UserId AND TestName='Test D') AS 'Test D',
    (SELECT MAX(DateTaken) FROM Passes p INNER JOIN Tests t ON p.TestId=t.TestId WHERE p.UserId=u.UserId AND TestName='Test E') AS 'Test E'
FROM Users u

Комментарии:

1. Какие исправления, Адриан? Также кто-нибудь может прокомментировать, какой ответ является наиболее эффективным, пожалуйста?

2. у @James Adrian есть ожидающее утверждения редактирование, в котором имя пользователя присваивается псевдониму Name. Что касается наиболее эффективного, я бы предположил, что ник rulez’. Но вы, вероятно, можете протестировать их и посмотреть

Ответ №3:

Это довольно общее решение. MySQL поддерживает все, что я написал, однако может существовать специфичное для MySQL решение, которое может быть лучше.

 Select
    UserName,
    Max(TestA) testa,
    Max(TestB) testb,
    Max(TestC) testc,
    Max(TestD) testd,
    Max(TestE) teste
FROM
(



    SELECT
        u.UserName,
        Case When TestName = "Test A" then p.DateTaken END TestA,
        Case When TestName = "Test B" then p.DateTaken END TestB,
        Case When TestName = "Test C" then p.DateTaken END TestC,
        Case When TestName = "Test D" then p.DateTaken END TestD,
        Case When TestName = "Test E" then p.DateTaken END TestE
    FROM
        Users u
        LEFT JOIN tests t
        ON u.UserId = t.userid
        LEFT JOIN (SELECT
                Max(DateTaken) DateTaken,
                userId,
                TestId,
            FROM 
                Passes
            GROUP BY 
                userId,
                TestId) p
        ON t.testId = p.TestId) t
Group BY
    UserName