MySql选择合格的有效期限 [英] MySql select qualification expiry dates
问题描述
我有三个MySql表:
$ b
用户:UserId,UserName
测试:TestId,TestName
$ b 通过:PassId,UserId,TestId,DateTaken
我想返回一个表格,显示每个用户传递的最新测试的日期,例如
| -------- | --------- | --------- | --------- | -------- - | --------- |
|用户|测试A |测试B |测试C |测试D |测试E |
| -------- | --------- | --------- | --------- | ------ ------ --- | --------- |
| James | Null | 6/3/11 | Null | Null | 4/3/11 |
| Mark | Null | 1/4/11 | 8/5/11 | 23/5/10 | Null |
| -------- | --------- | --------- | --------- | ------ ------ --- | --------- |
在这个例子中,James从未通过过测试A,C或D.他可能采用了测试B几次,但最新的是在6/3/11。
我打算在ASP.NET GridView中显示这些数据。什么是最好的方法 - 可以在SELECT语句中完成?请帮助!
非常感谢您提前。
选择
u.username,
max(如果testid = 1,则datetaken else null结束时)为A,
max(testid = 2时则为datetaken else作为C,
max(当testid = 4,然后datetaken else null结束时)作为B,
max(当testid = 3时,则datetaken else null结束)作为D,
max(当testid = 5时,datetaken else null结束时)作为用户的E
作为u
在u.userid = p.userid $ b $上留下连接通行证$ b $ u由u.userid
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
这篇关于MySql选择合格的有效期限的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!