CREATE TABLE test (id INT,number INT);INSERT INTO test VALUES (1,2); INSERT INTO test VALUES(1,5); INSERT INTO test VALUES(1,3); INSERT INTO test VALUES(1,10); INSERT INTO test VALUES(1,7); INSERT INTO test VALUES (2,2); INSERT INTO test VALUES(2,5); INSERT INTO test VALUES(2,3); INSERT INTO test VALUES(2,10); INSERT INTO test VALUES(2,7); INSERT INTO test VALUES (3,2); INSERT INTO test VALUES(3,5); INSERT INTO test VALUES(3,3); INSERT INTO test VALUES(3,10); INSERT INTO test VALUES(3,7);先进行数值的排序: SELECT id,number FROM test ORDER BY id,number;取得我需要提取的行: SELECT id,ROUND(COUNT(*)/2) AS ROW FROM (SELECT id,number FROM test ORDER BY id,number) a GROUP BY id;这个ROW字段就是我想要在表中取得的每个ID某一行的数据。 我想要的结果就是这样的: id number 1 5 2 5 3 5这个结果应该怎么得出来,就是这个意思。
mysql> SELECT id,number FROM test ORDER BY id,number; +------+--------+ | id | number | +------+--------+ | 1 | 2 | | 1 | 3 | | 1 | 5 | | 1 | 7 | | 1 | 10 | | 2 | 2 | | 2 | 3 | | 2 | 5 | | 2 | 7 | | 2 | 10 | | 3 | 2 | | 3 | 3 | | 3 | 5 | | 3 | 7 | | 3 | 10 | +------+--------+ 15 rows in set (0.00 sec) 以下两句均达到你的要求。 mysql> select * from test where number=5; +------+--------+ | id | number | +------+--------+ | 1 | 5 | | 2 | 5 | | 3 | 5 | +------+--------+ 3 rows in set (0.02 sec)mysql> select * -> from test a -> where 3=(select count(*) from test where id=a.id and number<=a.number); +------+--------+ | id | number | +------+--------+ | 1 | 5 | | 2 | 5 | | 3 | 5 | +------+--------+ 3 rows in set (0.00 sec)mysql>
那假设用这些数据呢: INSERT INTO test VALUES (1,2); INSERT INTO test VALUES(1,5); INSERT INTO test VALUES(1,3); INSERT INTO test VALUES(1,10); INSERT INTO test VALUES(1,7); INSERT INTO test VALUES (2,2); INSERT INTO test VALUES(2,5); INSERT INTO test VALUES(2,3); INSERT INTO test VALUES(2,10); INSERT INTO test VALUES(2,7); INSERT INTO test VALUES (3,2); INSERT INTO test VALUES(3,5); INSERT INTO test VALUES(3,3); INSERT INTO test VALUES(3,10); INSERT INTO test VALUES(3,7); INSERT INTO test VALUES(1,3); INSERT INTO test VALUES(2,12); INSERT INTO test VALUES(3,9);那结果应该是:+------+--------+ | id | number | +------+--------+ | 1 | 3 | | 2 | 5 | | 3 | 5 | +------+--------+
lz的意思是: 1 根据ID分组 2 每组按照number升序排序,取出第3条记录 ??
先根据ID和NUMBER进行排序,然后取出每个ID下面count(*)/2这一行的数据。比如说 id number 1 2 1 3 1 3 1 5 1 7 1 10 现在是已经排序完成,然后我使用 SELECT id,ROUND(COUNT(*)/2) AS ROW FROM (SELECT id,number FROM test ORDER BY id,number) a GROUP BY id;得出想要取得数据的行序列(ROW字段就是我需要取得数据的行序列) id row 1 3就是说我想取第三行的数据,也就是结果为 id number 1 3就是这样的。
选择测试用例是一种学问!。mysql> select * from test order by id,number; +------+--------+ | id | number | +------+--------+ | 1 | 2 | | 1 | 3 | | 1 | 3 | | 1 | 5 | | 1 | 7 | | 1 | 10 | | 2 | 2 | | 2 | 3 | | 2 | 5 | | 2 | 7 | | 2 | 10 | | 2 | 12 | | 3 | 2 | | 3 | 3 | | 3 | 5 | | 3 | 7 | | 3 | 9 | | 3 | 10 | +------+--------+ 18 rows in set (0.00 sec)mysql> select distinct * -> from test a -> where 3=(select count(*) from test where id=a.id and number<=a.number); +------+--------+ | id | number | +------+--------+ | 1 | 3 | | 2 | 5 | | 3 | 5 | +------+--------+ 3 rows in set (0.00 sec)mysql>
1|3还是1|5? 用13楼数据 SELECT A.ID,A.NUMBER FROM ( SELECT id,number FROM test ORDER BY id,number) A INNER JOIN (SELECT id,number FROM test ORDER BY id,number) B ON A.ID=B.ID AND A.NUMBER>=B.NUMBER GROUP BY A.ID,A.NUMBER HAVING COUNT(B.ID)=3
mysql> select * from test order by id,number; +------+--------+ | id | number | +------+--------+ | 1 | 2 | | 1 | 3 | | 1 | 5 | | 1 | 7 | | 1 | 10 | | 2 | 2 | | 2 | 3 | | 2 | 5 | | 2 | 7 | | 2 | 10 | | 3 | 2 | | 3 | 3 | | 3 | 5 | | 3 | 7 | | 3 | 10 | +------+--------+ 15 rows in set (0.00 sec)mysql> select a.* from test a, -> (select id,count(*) ct from test group by id) b -> where a.id=b.id and floor(b.ct/2)+1 = -> (select count(*) from test where id=a.id and number<=a.number) -> order by id; +------+--------+ | id | number | +------+--------+ | 1 | 5 | | 2 | 5 | | 3 | 5 | +------+--------+ 3 rows in set (0.00 sec)
看上面的数据总是有点误导,我重新模拟了一些数据: INSERT INTO test VALUES (1,2); INSERT INTO test VALUES(1,5); INSERT INTO test VALUES(1,3); INSERT INTO test VALUES(1,10); INSERT INTO test VALUES(1,7); INSERT INTO test VALUES (2,2); INSERT INTO test VALUES(2,3); INSERT INTO test VALUES(2,10); INSERT INTO test VALUES(2,7); INSERT INTO test VALUES (3,2); INSERT INTO test VALUES(3,5); INSERT INTO test VALUES(3,3); INSERT INTO test VALUES(3,10); INSERT INTO test VALUES(3,7); INSERT INTO test VALUES(1,3); INSERT INTO test VALUES(2,12); INSERT INTO test VALUES (1,22); INSERT INTO test VALUES(1,52); INSERT INTO test VALUES (2,52); INSERT INTO test VALUES(2,57); INSERT INTO test VALUES(2,38); INSERT INTO test VALUES(2,103); INSERT INTO test VALUES(2,71); INSERT INTO test VALUES (3,29); INSERT INTO test VALUES(3,54); INSERT INTO test VALUES(3,33); INSERT INTO test VALUES(3,109); INSERT INTO test VALUES(3,70); INSERT INTO test VALUES(1,37); INSERT INTO test VALUES(2,123); INSERT INTO test VALUES(3,90); INSERT INTO test VALUES(3,90); INSERT INTO test VALUES(3,900); INSERT INTO test VALUES(3,290); INSERT INTO test VALUES(3,970); INSERT INTO test VALUES(3,80);使用SELECT id,number FROM test ORDER BY id,number;排序结果: id number 1 2 1 3 1 3 1 5 1 7 1 10 1 22 1 37 1 52 2 2 2 3 2 7 2 10 2 12 2 38 2 52 2 57 2 71 2 103 2 123 3 2 3 3 3 5 3 7 3 10 3 29 3 33 3 54 3 70 3 80 3 90 3 90 3 109 3 290 3 900 3 970使用SELECT id,ROUND(COUNT(*)/2) AS ROW FROM (SELECT id,number FROM test ORDER BY id,number) a GROUP BY id;取得需要的行: id row 1 5 2 6 3 8 也就是说我需要id为1的第5行记录,id为2的第6行记录,id为3的第8行记录。结果应该是: id number 1 7 2 38 3 54
mysql> select a.* from test a, -> (select id,count(*) ct from test group by id) b -> where a.id=b.id and round(b.ct/2) = -> (select count(*) from test where id=a.id and number<=a.number) -> order by id; +------+--------+ | id | number | +------+--------+ | 1 | 7 | | 2 | 38 | | 3 | 54 | +------+--------+ 3 rows in set (0.00 sec)
id number
1 2
1 3
1 4
1 5
1 6
2 3
2 4
2 5
2 6
2 7
需要的结果为:
id number
1 4
2 5
on a.id=a.id and a.number>=b.number
having count(b.id)=3
1 4
2 7
1 2
1 3
1 4
1 5
1 6
2 3
2 4
2 5
2 6
2 7
先按照降序排列得出:
id number
1 2
1 3
1 4
1 5
1 6
2 3
2 4
2 5
2 6
2 7
再取每个ID的第round(count(*)/2)+1 行,得出结果:
id number
1 4
2 5是这样来的。
from table1 a
where 3=(select count(*) from table1 where id=a.id and number<=a.number)
为什么 3=。
就是这样的 ,现在就卡在如何取得特定行的值。
建议你列出你的表结构,并提供测试数据以及基于这些测试数据的所对应正确结果。
参考一下这个贴子的提问方式http://topic.csdn.net/u/20091130/20/8343ee6a-417c-4c2d-9415-fa46604a00cf.html
1. 你的 create table xxx .. 语句
2. 你的 insert into xxx ... 语句
3. 结果是什么样,(并给以简单的算法描述)
4. 你用的数据库名称和版本(经常有人在MS SQL server版问 MySQL)
这样想帮你的人可以直接搭建和你相同的环境,并在给出方案前进行测试,避免文字描述理解上的误差。
INSERT INTO test VALUES(1,5);
INSERT INTO test VALUES(1,3);
INSERT INTO test VALUES(1,10);
INSERT INTO test VALUES(1,7);
INSERT INTO test VALUES (2,2);
INSERT INTO test VALUES(2,5);
INSERT INTO test VALUES(2,3);
INSERT INTO test VALUES(2,10);
INSERT INTO test VALUES(2,7);
INSERT INTO test VALUES (3,2);
INSERT INTO test VALUES(3,5);
INSERT INTO test VALUES(3,3);
INSERT INTO test VALUES(3,10);
INSERT INTO test VALUES(3,7);先进行数值的排序:
SELECT id,number FROM test ORDER BY id,number;取得我需要提取的行:
SELECT id,ROUND(COUNT(*)/2) AS ROW FROM (SELECT id,number FROM test ORDER BY id,number) a GROUP BY id;这个ROW字段就是我想要在表中取得的每个ID某一行的数据。
我想要的结果就是这样的:
id number
1 5
2 5
3 5这个结果应该怎么得出来,就是这个意思。
+------+--------+
| id | number |
+------+--------+
| 1 | 2 |
| 1 | 3 |
| 1 | 5 |
| 1 | 7 |
| 1 | 10 |
| 2 | 2 |
| 2 | 3 |
| 2 | 5 |
| 2 | 7 |
| 2 | 10 |
| 3 | 2 |
| 3 | 3 |
| 3 | 5 |
| 3 | 7 |
| 3 | 10 |
+------+--------+
15 rows in set (0.00 sec)
以下两句均达到你的要求。
mysql> select * from test where number=5;
+------+--------+
| id | number |
+------+--------+
| 1 | 5 |
| 2 | 5 |
| 3 | 5 |
+------+--------+
3 rows in set (0.02 sec)mysql> select *
-> from test a
-> where 3=(select count(*) from test where id=a.id and number<=a.number);
+------+--------+
| id | number |
+------+--------+
| 1 | 5 |
| 2 | 5 |
| 3 | 5 |
+------+--------+
3 rows in set (0.00 sec)mysql>
INSERT INTO test VALUES (1,2);
INSERT INTO test VALUES(1,5);
INSERT INTO test VALUES(1,3);
INSERT INTO test VALUES(1,10);
INSERT INTO test VALUES(1,7);
INSERT INTO test VALUES (2,2);
INSERT INTO test VALUES(2,5);
INSERT INTO test VALUES(2,3);
INSERT INTO test VALUES(2,10);
INSERT INTO test VALUES(2,7);
INSERT INTO test VALUES (3,2);
INSERT INTO test VALUES(3,5);
INSERT INTO test VALUES(3,3);
INSERT INTO test VALUES(3,10);
INSERT INTO test VALUES(3,7);
INSERT INTO test VALUES(1,3);
INSERT INTO test VALUES(2,12);
INSERT INTO test VALUES(3,9);那结果应该是:+------+--------+
| id | number |
+------+--------+
| 1 | 3 |
| 2 | 5 |
| 3 | 5 |
+------+--------+
1 根据ID分组
2 每组按照number升序排序,取出第3条记录
??
id number
1 2
1 3
1 3
1 5
1 7
1 10
现在是已经排序完成,然后我使用
SELECT id,ROUND(COUNT(*)/2) AS ROW FROM (SELECT id,number FROM test ORDER BY id,number) a GROUP BY id;得出想要取得数据的行序列(ROW字段就是我需要取得数据的行序列)
id row
1 3就是说我想取第三行的数据,也就是结果为
id number
1 3就是这样的。
+------+--------+
| id | number |
+------+--------+
| 1 | 2 |
| 1 | 3 |
| 1 | 3 |
| 1 | 5 |
| 1 | 7 |
| 1 | 10 |
| 2 | 2 |
| 2 | 3 |
| 2 | 5 |
| 2 | 7 |
| 2 | 10 |
| 2 | 12 |
| 3 | 2 |
| 3 | 3 |
| 3 | 5 |
| 3 | 7 |
| 3 | 9 |
| 3 | 10 |
+------+--------+
18 rows in set (0.00 sec)mysql> select distinct *
-> from test a
-> where 3=(select count(*) from test where id=a.id and number<=a.number);
+------+--------+
| id | number |
+------+--------+
| 1 | 3 |
| 2 | 5 |
| 3 | 5 |
+------+--------+
3 rows in set (0.00 sec)mysql>
用13楼数据
SELECT A.ID,A.NUMBER FROM (
SELECT id,number FROM test ORDER BY id,number) A
INNER JOIN
(SELECT id,number FROM test ORDER BY id,number) B
ON A.ID=B.ID AND A.NUMBER>=B.NUMBER
GROUP BY A.ID,A.NUMBER
HAVING COUNT(B.ID)=3
mysql> select * from test order by id,number;
+------+--------+
| id | number |
+------+--------+
| 1 | 2 |
| 1 | 3 |
| 1 | 5 |
| 1 | 7 |
| 1 | 10 |
| 2 | 2 |
| 2 | 3 |
| 2 | 5 |
| 2 | 7 |
| 2 | 10 |
| 3 | 2 |
| 3 | 3 |
| 3 | 5 |
| 3 | 7 |
| 3 | 10 |
+------+--------+
15 rows in set (0.00 sec)mysql> select a.* from test a,
-> (select id,count(*) ct from test group by id) b
-> where a.id=b.id and floor(b.ct/2)+1 =
-> (select count(*) from test where id=a.id and number<=a.number)
-> order by id;
+------+--------+
| id | number |
+------+--------+
| 1 | 5 |
| 2 | 5 |
| 3 | 5 |
+------+--------+
3 rows in set (0.00 sec)
INSERT INTO test VALUES (1,2);
INSERT INTO test VALUES(1,5);
INSERT INTO test VALUES(1,3);
INSERT INTO test VALUES(1,10);
INSERT INTO test VALUES(1,7);
INSERT INTO test VALUES (2,2);
INSERT INTO test VALUES(2,3);
INSERT INTO test VALUES(2,10);
INSERT INTO test VALUES(2,7);
INSERT INTO test VALUES (3,2);
INSERT INTO test VALUES(3,5);
INSERT INTO test VALUES(3,3);
INSERT INTO test VALUES(3,10);
INSERT INTO test VALUES(3,7);
INSERT INTO test VALUES(1,3);
INSERT INTO test VALUES(2,12);
INSERT INTO test VALUES (1,22);
INSERT INTO test VALUES(1,52);
INSERT INTO test VALUES (2,52);
INSERT INTO test VALUES(2,57);
INSERT INTO test VALUES(2,38);
INSERT INTO test VALUES(2,103);
INSERT INTO test VALUES(2,71);
INSERT INTO test VALUES (3,29);
INSERT INTO test VALUES(3,54);
INSERT INTO test VALUES(3,33);
INSERT INTO test VALUES(3,109);
INSERT INTO test VALUES(3,70);
INSERT INTO test VALUES(1,37);
INSERT INTO test VALUES(2,123);
INSERT INTO test VALUES(3,90);
INSERT INTO test VALUES(3,90);
INSERT INTO test VALUES(3,900);
INSERT INTO test VALUES(3,290);
INSERT INTO test VALUES(3,970);
INSERT INTO test VALUES(3,80);使用SELECT id,number FROM test ORDER BY id,number;排序结果:
id number
1 2
1 3
1 3
1 5
1 7
1 10
1 22
1 37
1 52
2 2
2 3
2 7
2 10
2 12
2 38
2 52
2 57
2 71
2 103
2 123
3 2
3 3
3 5
3 7
3 10
3 29
3 33
3 54
3 70
3 80
3 90
3 90
3 109
3 290
3 900
3 970使用SELECT id,ROUND(COUNT(*)/2) AS ROW FROM (SELECT id,number FROM test ORDER BY id,number) a GROUP BY id;取得需要的行:
id row
1 5
2 6
3 8
也就是说我需要id为1的第5行记录,id为2的第6行记录,id为3的第8行记录。结果应该是:
id number
1 7
2 38
3 54
mysql> select a.* from test a,
-> (select id,count(*) ct from test group by id) b
-> where a.id=b.id and round(b.ct/2) =
-> (select count(*) from test where id=a.id and number<=a.number)
-> order by id;
+------+--------+
| id | number |
+------+--------+
| 1 | 7 |
| 2 | 38 |
| 3 | 54 |
+------+--------+
3 rows in set (0.00 sec)