UC知道一个关于 SQL 的问题 ...
来源：百度知道 编辑：UC知道 时间：2024/05/09 05:34:24
+---+------+------------+
| a | b | c |
+---+------+------------+
| 1 | 2 | dummy text |
| 2 | 2 | dummy text |
| 3 | 2 | dummy text |
| 4 | 1 | dummy text |
| 5 | 3 | dummy text |
| 6 | 3 | dummy text |
| 7 | 3 | dummy text |
+---+------+------------+
7 rows in set (0.00 sec)
现在我想要用一个 SQL 语句返回每个 b 的前两条记录 ...
也就是构造这样的结果表 ...
+---+------+------------+
| a | b | c |
+---+------+------------+
| 4 | 1 | dummy text |
| 1 | 2 | dummy text |
| 2 | 2 | dummy text |
| 5 | 3 | dummy text |
| 6 | 3 | dummy text |
+---+------+------------+
不知道做得到么 ...?
类似于 GROUP BY 的效果 ...
只是要显示两条记录 ...
谢谢 ... 得到答案后高分追加 ...
似乎我表达得不明确 ...
我的意思是 ... 能不能在一次查询里面搞定呢 ...?
另外 ... 我的 a 字段是一个自增的主键 ...?
直接查询似乎没有好办法，因为没有能唯一标识一行数据的字段，如果有个自增列是最理想的。针对你的表，可以这么写语句。
select *
from 表 as t1
where cast(a as varchar(50))+'+'+cast(b as varchar(50)) in(select top 2 cast(a as varchar(50))+'+'+cast(b as varchar(50)) from 表
where b=t1.b order by a)
order by b
a是自增列的话可以这么写，不用转换类型了。
select *
from 表 as t1
where a in(select top 2 a from 表
where b=t1.b order by a)
order by b
select * from atable
where a in (
select (select top 1 a from atable where b=t.b) from atable t group by b
union all
select (select top 1 a from atable where b=t.b and a not in (select (select top 1 a from atable where b=t.b) from atable t group by b)) from atable t group by b
)
order by b,a
虽然长了点，但可以得到你想要的结果
table table1(a,b ,c)
其中A,B为INT
(select a.* from table1 a ,(select a = min(a) from table1 group by b ) b
where a.a= b.a)
union
select c.* from table1 c,(sele