如何用SQL SERVER取分组数据第一条
表如下:
table1
ID name time content
1 a 10 kkkk
2 b 20 llll
3 c 2 dddd
table2
ID table1_ID user
1 1 dddd
2 1 eee
3 2 ffff
4 2 kkkk
5 1 ljk
表1中的ID和表2中的table1_ID是关联的。现要将表2合并到表1中。并且查询出合并后新表每个分组数据的第一条记录。SQL SERVER代码应该如何写? 展开
根据table1_id进行分组所得结果:
select * from (select a.id as a_id,a.name,a.time,a.content,b.id as b_id,b.user from table1 a inner join table2 b on a.id = b.table1_ID) new_tbl where b_id in (select min(id) from table2 group by table1_ID)
扩展资料:
注意事项
在SQL Server数据库中,使用top关键字:SELECT TOP number|percent column_name(s) FROM table_name
在MySQL数据库中,使用LIMIT关键字:SELECT column_name(s) FROM table_name LIMIT number
例子:SELECT * FROM Persons LIMIT 1
select bookName from book where price > 20 limit 1;
limit 1;
or
limit 0,1;
在Oracle数据库中,使用ROWNUM关键字:
SELECT column_name(s) FROM table_name WHERE ROWNUM <= number
例子:SELECT * FROM Persons WHERE ROWNUM <= 1
推荐于2017-09-02 · 知道合伙人金融证券行家
用row_number函数可以解决。
1、创建测试表及插入数据:
create table test
(id int,
name varchar(10),
score int,
classname varchar(20));
insert into test values (1,'张三',100,'一班');
insert into test values (2,'李四',89,'一班');
insert into test values (3,'王五',97,'一班');
insert into test values (4,'赵六',87,'二班');
insert into test values (5,'孙七',94,'二班');
insert into test values (6,'杨八',76,'二班');
2、查出每个班级的成绩第一名,执行以下语句:
select t.* from
(select test.*,row_number() over (partition by classname order by score desc) rn
from test) t
where rn=1;
3、结果截图:
select * from (select a.id as a_id,a.name,a.time,a.content,b.id as b_id,b.user from table1 a inner join table2 b on a.id = b.table1_ID) new_tbl where b_id in (select min(id) from table2 group by table1_ID)
left join 表2 b on a.字段1=b.字段1
left join 表3 c on a.字段1=c.字段1
group by 字段1 order
by c.字段2 desc) as abcd limit 1