create table t_users ( userid int identity(1,1) primary key, username nvarchar(20) )insert t_users(username) select 'wang' union all select 'zhang' union all select 'liu' create table t_charters ( chapterid int identity(1,1) primary key, chaptername nvarchar(20) )insert t_charters(chaptername) select 'math' union all select 'chinese' union all select 'english'create table t_testRecord ( id int identity(1,1) primary key, fUserid int, fChapterID int , fscore int, fisPassed tinyint, ftime datetime )insert t_testRecord( fUserid , fChapterID , fscore , fisPassed , ftime ) select 1,1,59,0,getdate() union all select 2,1,60,1,getdate() union all select 1,1,60,1,getdate()--1....今天的考试记录(今天也许有好多人考过)(一个人有多门课程,一门课程每天都可以考多次) SELECT t.*, u.username,c.chaptername FROM t_testRecord r inner join t_users u on r.fuserid= i.userid inner join t_charters c inner join r.fChapterID = c.chapterid WHERE CONVERT(VARCHAR(10),Gftime,10) = CONVERT(VARCHAR(10),GETDATE(),10) --2....如果这门课程是第一次考而且及格(以前或许也考过),则记录下这样的记录每个人有多少条 select r.* from t_testRecord r where fispassed = 1 --1:及格 and exists ( select 1 from (select fuserid ,fchapterid ,count(fscore) as fscore from t_testRecord group by fuserid ,fchapterid having count(fscore)=1 ) t where t.fuserid =r.fuserid and t.fchapterid = r.fchapterid )
create table t_users
(
userid int identity(1,1) primary key,
username nvarchar(20)
)insert t_users(username)
select 'wang'
union all
select 'zhang'
union all
select 'liu'
create table t_charters
(
chapterid int identity(1,1) primary key,
chaptername nvarchar(20)
)insert t_charters(chaptername)
select 'math'
union all
select 'chinese'
union all
select 'english'create table t_testRecord
(
id int identity(1,1) primary key,
fUserid int,
fChapterID int ,
fscore int,
fisPassed tinyint,
ftime datetime
)insert t_testRecord( fUserid ,
fChapterID ,
fscore ,
fisPassed ,
ftime )
select 1,1,59,0,getdate()
union all
select 2,1,60,1,getdate()
union all
select 1,1,60,1,getdate()--1....今天的考试记录(今天也许有好多人考过)(一个人有多门课程,一门课程每天都可以考多次)
SELECT t.*, u.username,c.chaptername
FROM t_testRecord r inner join t_users u
on r.fuserid= i.userid inner join t_charters c
inner join r.fChapterID = c.chapterid
WHERE CONVERT(VARCHAR(10),Gftime,10) = CONVERT(VARCHAR(10),GETDATE(),10)
--2....如果这门课程是第一次考而且及格(以前或许也考过),则记录下这样的记录每个人有多少条
select r.* from t_testRecord r
where fispassed = 1 --1:及格
and exists (
select 1 from
(select fuserid ,fchapterid ,count(fscore) as fscore
from t_testRecord group by fuserid ,fchapterid having count(fscore)=1 ) t
where t.fuserid =r.fuserid and t.fchapterid = r.fchapterid
)