现在可以用这条语句得到结果,但是有一个连接操作效率不高select distinct A.pid, A.aid from tbtest as A inner join tbtest as B on A.aid=B.aid order by B.pid
首先按照第一列pid排序,而具有相同aid的行会排在一起这样即可 select * from tbname order by aid ,pid ;
你要按物理顺序排序, SET @a=0; SET @b=0; SELECT a.* FROM ttq a INNER JOIN ( SELECT aid,MIN(js) AS pl FROM ( SELECT *,@a:=IF(@b=aid,@a,@a+1) AS js,@b:=aid FROM ttq) a GROUP BY aid) b ON a.aid=b.aid ORDER BY b.pl
1、插入有自增字段的临时表,字段ID自增,再运行 SELECT a.* FROM ttq a INNER JOIN ( SELECT aid,MIN(ID) AS pl FROM 临时表 a GROUP BY aid) b ON a.aid=b.aid ORDER BY b.pl2、将 11楼代码生成VIEW、SP,再调用。
+-----+------+
| pid | aid |
+-----+------+
| 1 | 1 |
| 2 | 2 |
| 3 | 2 |
| 4 | 1 |
+-----+------+ +-----+------+
| pid | aid |
+-----+------+
| 2 | 2 |
| 3 | 2 |
| 1 | 1 |
| 4 | 1 |
+-----+------+
| pid | aid |
+-----+------+
| 1 | 1 |
| 2 | 2 |
| 3 | 2 |
| 4 | 1 |
+-----+------+
要求结果是什么?
pid aid
2 2
3 2
1 1
4 1
| pid | aid |
+-----+------+
| 1 | 1 |
| 2 | 2 |
| 3 | 2 |
| 4 | 1 |
+-----+------+
希望得到的是
+-----+------+
| pid | aid |
+-----+------+
| 1 | 1 |
| 4 | 1 |
| 2 | 2 |
| 3 | 2 |
+-----+------+
排序过程是先选出pid最小的一行,然后与这行具有相同aid的都选出,按pid升序排列;依次继续选择
create table tbtest (pid int not null primary key, aid int);insert into tbtest values(1, 2);
insert into tbtest values(2, 1);
insert into tbtest values(3, 1);
insert into tbtest values(4, 2);/*
希望结果
pid aid
1 2
4 2
2 1
3 1
*/大家帮帮忙,尽量效率高些。
| pid | aid |
+-----+------+
| 1 | 3
| 2 | 1 |
| 3 | 2 |
| 4 | 2 |
| 5 | 1 |
| 6 | 3 |
+-----+------+
要求结果是什么?
| pid | aid |
+-----+------+
| 1 | 3 |
| 2 | 1 |
| 3 | 2 |
| 4 | 2 |
| 5 | 1 |
| 6 | 3 |
+-----+------+
希望得到的结果是
+-----+------+
| pid | aid |
+-----+------+
| 1 | 3 |
| 6 | 3 |
| 2 | 1 |
| 5 | 1 |
| 3 | 2 |
| 4 | 2 |
+-----+------+
tbtest as B
on A.aid=B.aid
order by B.pid
select * from tbname order by aid ,pid ;
SET @a=0;
SET @b=0;
SELECT a.* FROM ttq a INNER JOIN (
SELECT aid,MIN(js) AS pl FROM (
SELECT *,@a:=IF(@b=aid,@a,@a+1) AS js,@b:=aid FROM ttq) a GROUP BY aid) b
ON a.aid=b.aid ORDER BY b.pl
String sql = "*******";
ResultSet rs = stmt.executeQuery(sql);
from tbtest a,tbtest b
where a.aid=b.aid
group by a.pid,a.aid
order by 3,1
+-----+------+
| pid | aid |
+-----+------+
| 1 | 3
| 2 | 1 |
| 3 | 2 |
| 4 | 2 |
| 5 | 1 |
| 6 | 3 |
| 1 | 4 |
+-----+------+
结果是什么?pid aid
1 3
6 3
2 1
5 1
3 2
4 2
1 4
?
SELECT a.* FROM ttq a INNER JOIN (
SELECT aid,MIN(ID) AS pl FROM 临时表 a GROUP BY aid) b
ON a.aid=b.aid ORDER BY b.pl2、将
11楼代码生成VIEW、SP,再调用。