存储架构

MySQL selects the expiration dates of the qualification

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 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
阅读原文...

Hello, buddy!

Apple on Spotify App Store complaint: It’s all about the money

上一篇

小红书测试新产品"hey":可发布短视频打卡生活

下一篇

您也可能喜欢

评论已经被关闭。

插入图片
MySQL selects the expiration dates of the qualification

长按储存图像,分享给朋友