两张表,结构和数据如下
user
ID Name
1 zhangshan
2 lishi
3 wangwuBook
BookID UserID count BookName
1 1 3 tt
2 1 6 77
3 1 8 99
4 2 4 kuikui
5 2 7 zhan
6 3 9 dd
7 3 2 55user为主表,user表与Book表通过user.ID与Book.UserID来关联。我想的 到的结果是对应每个人中count最大组成一个数据集合返回给我。怎么写啊。谢谢各位
user
ID Name
1 zhangshan
2 lishi
3 wangwuBook
BookID UserID count BookName
1 1 3 tt
2 1 6 77
3 1 8 99
4 2 4 kuikui
5 2 7 zhan
6 3 9 dd
7 3 2 55user为主表,user表与Book表通过user.ID与Book.UserID来关联。我想的 到的结果是对应每个人中count最大组成一个数据集合返回给我。怎么写啊。谢谢各位
from [user] a
left join book b on a.id=b.userid
and not exists(select 1 from book where userid=b.userid and [count]>b.[count])
from [user] U
cross apply
(select top 1 * from Book where Book.UserID=U.UserID order by count desc) B
where a.id=b.userid group by a.name,b.userid ,b.bookname
if object_id('[user]') is not null drop table [user]
go
create table [user]([ID] int,[Name] varchar(9))
insert [user]
select 1,'zhangshan' union all
select 2,'lishi' union all
select 3,'wangwu'
if object_id('[Book]') is not null drop table [Book]
go
create table [Book]([BookID] int,[UserID] int,[count] int,[BookName] varchar(6))
insert [Book]
select 1,1,3,'tt' union all
select 2,1,6,'77' union all
select 3,1,8,'99' union all
select 4,2,4,'kuikui' union all
select 5,2,7,'zhan' union all
select 6,3,9,'dd' union all
select 7,3,2,'55'
---查询---
select a.*,bookid,b.[count],bookname
from [user] a
left join book b on a.id=b.userid
and not exists(select 1 from book where userid=b.userid and [count]>b.[count])---结果---
ID Name bookid count bookname
----------- --------- ----------- ----------- --------
1 zhangshan 3 8 99
2 lishi 5 7 zhan
3 wangwu 6 9 dd(所影响的行数为 3 行)
select U.*,B.*
from [user] U
cross apply
(select top 1 * from Book where Book.UserID=U.ID order by count desc) BID Name BookID UserID count BookName
----------- --------- ----------- ----------- ----------- --------
1 zhangshan 3 1 8 99
2 lishi 5 2 7 zhan
3 wangwu 6 3 9 dd(3 行受影响)
实际是在不用group by 语句的情况下,select出了max(count),^_^