通过最大ID和积分对用户进行排名，但是(排名错误)

问题描述

我正在尝试获得最高排名的行，并选择没有rownum = rownum + 1的查询.我已经尝试过以下查询，但是我也缺少链接 http://sqlfiddle.com/#!2/fd7897/7 .

I am trying to get highest points row along with ranks and select query without rownum=rownum+1 .I have tried the query below but I am missing something also link http://sqlfiddle.com/#!2/fd7897/7 .

我正在寻找答案，就像每个接收者的最后一个条目一样，这也是排名最高的条目:我真的很感谢您的帮助.谢谢您.

I am looking for answer like for each receiver the last entry which would also be highest points entry rankwise: I really appreciate any help.Thanks in Advance.

类似这样的东西:

('2'，'4'，'test ...'，'2011-08-21 14:13:19'，'40'，'009')--- rank1

('2', '4', 'test ...','2011-08-21 14:13:19','40','009')---rank1

('4'，'2'，'test ...'，'2011-08-21 14:13:19'，'30'，'003')---- rank2

('4', '2', 'test ...','2011-08-21 14:13:19','30','003')----rank2

('1'，'3'，'test ...'，'2011-08-21 14:12:19'，'20'，'005')---- rank3

('1', '3', 'test ...','2011-08-21 14:12:19','20','005')---- rank3

查询:

    SELECT * ,
            (select count(*)
             from  tblA u2
             where u2.points > u.points or
                   u2.points = u.points and u2.id <= u.id
            ) as rank
FROM (SELECT u.receiver, MAX(u.id) AS id
      FROM tblA u
      GROUP BY  u.receiver
    ) subset JOIN
    tblA u
    ON subset.receiver = u.receiver AND subset.id = u.id order by rank;

表格:

CREATE TABLE if not exists tblA
(
id int(11) NOT NULL auto_increment ,
sender varchar(255),
receiver varchar(255),
 msg varchar(255),
 date timestamp,
  points int(255),
  refno varchar(255),
 PRIMARY KEY (id)
);

CREATE TABLE if not exists tblB
(
id int(11) NOT NULL auto_increment ,
sno varchar(255),
name varchar(255),
 PRIMARY KEY (id)
);

CREATE TABLE if not exists tblC
(
id int(11) NOT NULL auto_increment ,
data varchar(255),
  refno varchar(255),
  extrarefno varchar(255),
 PRIMARY KEY (id)
);


INSERT INTO tblA (sender, receiver,msg,date,points,refno ) VALUES
('1', '2', 'buzz ...','2011-08-21 14:11:09','10','001'),
('1', '2', 'test ...','2011-08-21 14:12:19','20','002'),
('4', '2', 'test ...','2011-08-21 14:13:19','30','003'),
('1', '3', 'buzz ...','2011-08-21 14:11:09','10','004'),
('1', '3', 'test ...','2011-08-21 14:12:19','20','005'),
('1', '4', 'buzz ...','2011-08-21 14:11:09','10','006'),
('1', '4', 'test ...','2011-08-21 14:12:19','20','007'),
('3', '4', 'test ...','2011-08-21 14:13:19','30','008'),
('2', '4', 'test ...','2011-08-21 14:13:19','40','009');



INSERT INTO tblB (sno, name ) VALUES
('1', 'Aa'),
('2', 'Bb'),
('3', 'Cc'),
('4', 'Dd'),
('5', 'Ee'),
('6', 'Ff'),
('7', 'Gg'),
('8', 'Hh');


INSERT INTO tblC (data,refno,extrarefno ) VALUES
('data1', '001', '101'),
('data2', '002', '102'),
('data3', '003', '103'),
('data4', '004', '101'),
('data5', '005', '102'),
('data6', '006', '103'),
('data7', '007', '101'),
('data8', '008', '101'),
('data9', '009', '101');

推荐答案

问题是子查询中的count(*).将其更改为count(distinct receiver):

The problem is the count(*) in the subquery. Change it to count(distinct receiver):

SELECT * ,
       (select count(distinct receiver)
        from  tblA u2
        where u2.points > u.points or
              u2.points = u.points and u2.id <= u.id
       ) as rank
FROM (SELECT u.receiver, MAX(u.id) AS id
      FROM tblA u
      GROUP BY  u.receiver
    ) subset JOIN
    tblA u
    ON subset.receiver = u.receiver AND subset.id = u.id
order by rank;

要将其创建为MySQL视图，必须在from子句中进行汇总:

To create this as a MySQL view, you have to get right of the aggregation in the from clause:

SELECT * ,
       (select count(distinct receiver)
        from  tblA u2
        where u2.points > u.points or
              u2.points = u.points and u2.id <= u.id
       ) as rank
FROM tblA u
WHERE u.id = (select max(u2.id) from tblA u2 where u2.receiver = u.receiver)
order by rank;

这篇关于通过最大ID和积分对用户进行排名，但是(排名错误)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！