/*==============================================================*/
/* Table: Users 用户表 */
/*==============================================================*/
-- drop table users
create table Users (
userId int constraint PK_Users primary key identity(1,1) ,
[Name] varchar(50) not null,
Sex varchar(2) not null,
Age int null
)
go/*==============================================================*/
/* Table: RestoreNote 得分表 */
/*==============================================================*/-- drop table RestoreNotecreate table RestoreNote (
Id int constraint PK_RestoreNote primary key identity(1,1) ,
Score int not null,
userId int not null,
restorenotetime datetime not null
)
goalter table RestoreNote
add constraint FK_RestoreNote_REFERENCE_Users foreign key (userId)
references Users (userId)
go
alter table RestoreNote
drop constraint FK_RestoreNote_REFERENCE_Users
goINSERT INTO Users VALUES ('小华','男',18)
INSERT INTO Users VALUES ('刘德华','男',48)
INSERT INTO Users VALUES ('武松','男',38)
INSERT INTO Users VALUES ('张伯芝','女',34)
INSERT INTO Users VALUES ('得得','男',18)
INSERT INTO RestoreNote VALUES (80,1,'2010-01-5')
INSERT INTO RestoreNote VALUES (70,1,'2009-12-25')
INSERT INTO RestoreNote VALUES (90,1,'2010-01-4')
INSERT INTO RestoreNote VALUES (50,1,'2009-12-20')
INSERT INTO RestoreNote VALUES (30,1,'2009-11-1')INSERT INTO RestoreNote VALUES (75,1,'2010-01-5')
INSERT INTO RestoreNote VALUES (85,1,'2010-01-1')
INSERT INTO RestoreNote VALUES (60,1,'2009-11-11')
select * from Users
select * from RestoreNote
/*
1.查询 本月 得分 最高的 一名用户 只 显示 用户名 得分2.查询 本周 得分 最高的 一名用户 只 显示 用户名 得分
*/
/* Table: Users 用户表 */
/*==============================================================*/
-- drop table users
create table Users (
userId int constraint PK_Users primary key identity(1,1) ,
[Name] varchar(50) not null,
Sex varchar(2) not null,
Age int null
)
go/*==============================================================*/
/* Table: RestoreNote 得分表 */
/*==============================================================*/-- drop table RestoreNotecreate table RestoreNote (
Id int constraint PK_RestoreNote primary key identity(1,1) ,
Score int not null,
userId int not null,
restorenotetime datetime not null
)
goalter table RestoreNote
add constraint FK_RestoreNote_REFERENCE_Users foreign key (userId)
references Users (userId)
go
alter table RestoreNote
drop constraint FK_RestoreNote_REFERENCE_Users
goINSERT INTO Users VALUES ('小华','男',18)
INSERT INTO Users VALUES ('刘德华','男',48)
INSERT INTO Users VALUES ('武松','男',38)
INSERT INTO Users VALUES ('张伯芝','女',34)
INSERT INTO Users VALUES ('得得','男',18)
INSERT INTO RestoreNote VALUES (80,1,'2010-01-5')
INSERT INTO RestoreNote VALUES (70,1,'2009-12-25')
INSERT INTO RestoreNote VALUES (90,1,'2010-01-4')
INSERT INTO RestoreNote VALUES (50,1,'2009-12-20')
INSERT INTO RestoreNote VALUES (30,1,'2009-11-1')INSERT INTO RestoreNote VALUES (75,1,'2010-01-5')
INSERT INTO RestoreNote VALUES (85,1,'2010-01-1')
INSERT INTO RestoreNote VALUES (60,1,'2009-11-11')
select * from Users
select * from RestoreNote
/*
1.查询 本月 得分 最高的 一名用户 只 显示 用户名 得分2.查询 本周 得分 最高的 一名用户 只 显示 用户名 得分
*/
on A.userId=B.userId
group by [name],left(B.restorenotetime)
from [Users] a,
RestoreNote b
where a.userId = b.userId and datepart(mm,restorenotetime) = datepart(mm,getdate())
group by a.name
order by 2
select top 1 r.name,t.score
from Users r join RestoreNote t
on r.userId=t.userId
where convert(varchar(6),restorenotetime,112)=convert(varchar(6),getdate(),112)
order by t.score desc
--2
select top 1 r.name,t.score
from Users r join RestoreNote t
on r.userId=t.userId
where datepart(week,restorenotetime)=datepart(week,getdate())
order by t.score desc
on A.userId=B.userId
group by [name],left(B.restorenotetime,7)
userId int constraint PK_Users primary key identity(1,1) ,
[Name] varchar(50) not null,
Sex varchar(2) not null,
Age int null
) create table RestoreNote (
Id int constraint PK_RestoreNote primary key identity(1,1) ,
Score int not null,
userId int not null,
restorenotetime datetime not null
)
go INSERT INTO Users VALUES ('小华','男',18)
INSERT INTO Users VALUES ('刘德华','男',48)
INSERT INTO Users VALUES ('武松','男',38)
INSERT INTO Users VALUES ('张伯芝','女',34)
INSERT INTO Users VALUES ('得得','男',18)
INSERT INTO RestoreNote VALUES (80,1,'2010-01-5')
INSERT INTO RestoreNote VALUES (70,1,'2009-12-25')
INSERT INTO RestoreNote VALUES (90,1,'2010-01-4')
INSERT INTO RestoreNote VALUES (50,1,'2009-12-20')
INSERT INTO RestoreNote VALUES (30,1,'2009-11-1') INSERT INTO RestoreNote VALUES (75,1,'2010-01-5')
INSERT INTO RestoreNote VALUES (85,1,'2010-01-1')
INSERT INTO RestoreNote VALUES (60,1,'2009-11-11')
--1.查询 本月 得分 最高的 一名用户 只 显示 用户名 得分
select top 1 m.Name , sum(n.Score) Score from Users m,RestoreNote n where m.userId = n.userId and datediff(mm,restorenotetime,getdate()) = 0 group by m.Name order by score desc
/*
Name Score
-------------------------------------------------- -----------
小华 330(所影响的行数为 1 行)
*/--2.查询 本周 得分 最高的 一名用户 只 显示 用户名 得分
select top 1 m.Name , sum(n.Score) Score from Users m,RestoreNote n where m.userId = n.userId and datediff(week,restorenotetime,getdate()) = 0 group by m.Name order by score desc
/*
Name Score
-------------------------------------------------- -----------
小华 245(所影响的行数为 1 行)
*/drop table Users ,RestoreNote
and convert(varchar(7),a.restorenotetime,120) =convert(varchar(7),getdate(),120)
group by b.name
--1
select top 1 r.name,sum(t.score) as score
from Users r join RestoreNote t
on r.userId=t.userId
where convert(varchar(6),restorenotetime,112)=convert(varchar(6),getdate(),112)
group by r.name
order by t.score desc
--2
select top 1 r.name,sum(t.score) as score
from Users r join RestoreNote t
on r.userId=t.userId
where datepart(week,restorenotetime)=datepart(week,getdate())
group by r.name
order by t.score desc
on A.userId=B.userId
group by [name],left(convert(nvarchar(20),B.restorenotetime,120),7)
select top 1 r.name,sum(t.score) as score
from Users r join RestoreNote t
on r.userId=t.userId
where convert(varchar(6),restorenotetime,112)=convert(varchar(6),getdate(),112)
group by r.name
order by sum(t.score) desc
--2
select top 1 r.name,sum(t.score) as score
from Users r join RestoreNote t
on r.userId=t.userId
where datepart(week,restorenotetime)=datepart(week,getdate())
group by r.name
order by sum(t.score) desc
IF OBJECT_ID('RestoreNote') IS NOT NULL DROP TABLE RestoreNote
GO
/*==============================================================*/
/* Table: Users 用户表 */
/*==============================================================*/
-- drop table users
create table Users (
userId int constraint PK_Users primary key identity(1,1) ,
[Name] varchar(50) not null,
Sex varchar(2) not null,
Age int null
)
go/*==============================================================*/
/* Table: RestoreNote 得分表 */
/*==============================================================*/-- drop table RestoreNotecreate table RestoreNote (
Id int constraint PK_RestoreNote primary key identity(1,1) ,
Score int not null,
userId int not null,
restorenotetime datetime not null
)
goalter table RestoreNote
add constraint FK_RestoreNote_REFERENCE_Users foreign key (userId)
references Users (userId)
go
alter table RestoreNote
drop constraint FK_RestoreNote_REFERENCE_Users
go
INSERT INTO Users VALUES ('小华','男',18)
INSERT INTO Users VALUES ('刘德华','男',48)
INSERT INTO Users VALUES ('武松','男',38)
INSERT INTO Users VALUES ('张伯芝','女',34)
INSERT INTO Users VALUES ('得得','男',18)
INSERT INTO RestoreNote VALUES (80,1,'2010-01-5')
INSERT INTO RestoreNote VALUES (70,1,'2009-12-25')
INSERT INTO RestoreNote VALUES (90,1,'2010-01-4')
INSERT INTO RestoreNote VALUES (50,1,'2009-12-20')
INSERT INTO RestoreNote VALUES (30,1,'2009-11-1')INSERT INTO RestoreNote VALUES (75,1,'2010-01-5')
INSERT INTO RestoreNote VALUES (85,1,'2010-01-1')
INSERT INTO RestoreNote VALUES (60,1,'2009-11-11')
select * from Users
select * from RestoreNote
/*
1.查询 本月 得分 最高的 一名用户 只 显示 用户名 得分2.查询 本周 得分 最高的 一名用户 只 显示 用户名 得分
*/SELECT TOP 1 ID ,U.[NAME]
FROM RESTORENOTE T1
INNER JOIN USERS U ON T1.USERID=U.USERID
WHERE YEAR(RESTORENOTETIME)=YEAR(GETDATE())
AND MONTH(RESTORENOTETIME)=MONTH(GETDATE())
AND NOT EXISTS(
SELECT 1 FROM RESTORENOTE T2 WHERE YEAR(T2.RESTORENOTETIME)=YEAR(GETDATE())
AND MONTH(T2.RESTORENOTETIME)=MONTH(GETDATE()) AND T2.SCORE>T1.SCORE
)
--3 小华SELECT TOP 1 ID ,U.[NAME]
FROM RESTORENOTE T1
INNER JOIN USERS U ON T1.USERID=U.USERID
WHERE YEAR(RESTORENOTETIME)=YEAR(GETDATE())
AND MONTH(RESTORENOTETIME)=MONTH(GETDATE())
AND DATEPART(WK,RESTORENOTETIME)=DATEPART(WK,GETDATE())
AND NOT EXISTS(
SELECT 1 FROM RESTORENOTE T2 WHERE YEAR(T2.RESTORENOTETIME)=YEAR(GETDATE())
AND MONTH(T2.RESTORENOTETIME)=MONTH(GETDATE())
AND DATEPART(WK,T2.RESTORENOTETIME)=DATEPART(WK,GETDATE())
AND T2.SCORE>T1.SCORE
)
--3 小华