1. SELECT b.id, b.click, (SELECT COUNT(1)+1 FROM t_test1 A WHERE A.click > B.click) PM FROM t_test1 as b order by pm; 2.select t.uid, t.cnt, (SELECT COUNT(1) + 1 FROM (SELECT b.uid, count(1) as cnt FROM t_test2 as b group by b.uid) A WHERE A.cnt > t.cnt) PM from (SELECT b.uid, count(1) as cnt FROM t_test2 as b group by b.uid) t order by pm;
1. SELECT b.id, b.click, (SELECT COUNT(1)+1 FROM t_test1 A WHERE A.click > B.click) PM FROM t_test1 as b where b.id='3' order by pm;第一个click 字段不能是varchar类型,你要改成int 类型。2.select t.uid, t.cnt, (SELECT COUNT(1) + 1 FROM (SELECT b.uid, count(1) as cnt FROM t_test2 as b group by b.uid) A WHERE A.cnt > t.cnt) PM from (SELECT b.uid, count(1) as cnt FROM t_test2 as b where b.uid='5' group by b.uid) t order by pm;
SELECT
b.id,
b.click,
(SELECT
COUNT(1)+1
FROM
t_test1 A
WHERE
A.click > B.click) PM
FROM
t_test1 as b
order by pm;2.http://select
t.uid,
t.cnt,
(SELECT
COUNT(1) + 1
FROM
(SELECT
b.uid, count(1) as cnt
FROM
t_test2 as b
group by b.uid) A
WHERE
A.cnt > t.cnt) PM
from
(SELECT
b.uid, count(1) as cnt
FROM
t_test2 as b
group by b.uid) t
order by pm;
SELECT
b.id,
b.click,
(SELECT
COUNT(1)+1
FROM
t_test1 A
WHERE
A.click > B.click) PM
FROM
t_test1 as b
order by pm;
2.select
t.uid,
t.cnt,
(SELECT
COUNT(1) + 1
FROM
(SELECT
b.uid, count(1) as cnt
FROM
t_test2 as b
group by b.uid) A
WHERE
A.cnt > t.cnt) PM
from
(SELECT
b.uid, count(1) as cnt
FROM
t_test2 as b
group by b.uid) t
order by pm;
array(5) {
[0] => array(3) {
["id"] => string(4) "1007"
["click"] => string(2) "56"
["PM"] => string(1) "1"
}
[1] => array(3) {
["id"] => string(4) "1000"
["click"] => string(3) "231"
["PM"] => string(1) "2"
}
[2] => array(3) {
["id"] => string(4) "1008"
["click"] => string(2) "16"
["PM"] => string(1) "3"
}
[3] => array(3) {
["id"] => string(4) "1005"
["click"] => string(4) "1320"
["PM"] => string(1) "4"
}
[4] => array(3) {
["id"] => string(4) "1006"
["click"] => string(2) "12"
["PM"] => string(1) "5"
}
}排名不对。-----------------------------------------------------------------------------------------------------------------------------------------------------------
第二个是正确的,谢谢了,但是这把所有的会员都列出来 了,我只是取其中一个会员,得到他的排名,请问怎么加个where条件呢
SELECT
b.id,
b.click,
(SELECT
COUNT(1)+1
FROM
t_test1 A
WHERE
A.click > B.click) PM
FROM
t_test1 as b
where b.id='3'
order by pm;第一个click 字段不能是varchar类型,你要改成int 类型。2.select
t.uid,
t.cnt,
(SELECT
COUNT(1) + 1
FROM
(SELECT
b.uid, count(1) as cnt
FROM
t_test2 as b
group by b.uid) A
WHERE
A.cnt > t.cnt) PM
from
(SELECT
b.uid, count(1) as cnt
FROM
t_test2 as b
where b.uid='5'
group by b.uid) t
order by pm;