MySql select qualification expiry dates
I have three MySql tables:
Users: UserId, UserName
Tests: TestId, TestName
Passes: PassId, UserId, TestId, DateTaken
I would like to return a table showing dates of the LATEST tests passed by each user, e.g.
|--------|---------|---------|---------|---------|---------|
|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 |
|--------|---------|---------|---------|---------|---------|
In this example, James has never passed Tests A, C or 开发者_StackOverflow社区D. He may have taken Test B several times, but the latest was on 6/3/11.
I plan to display this data in an ASP.NET GridView. What is the best method for this - can it be done in a SELECT statement? Please help!
Many thanks in advance.
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
To achieve this you would need to use subqueries for each test result Try this:
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
This is fairly generic solution. MySQL does support everything I wrote, however there may be a MySQL specific solution that may be better.
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
精彩评论