mysql – 按最大ID和点排名用户但是(排名错误)

我试图得到最高点行和排名和选择查询没有rownum = rownum 1.我已经尝试了下面的查询,但我遗漏的东西也链接 http://sqlfiddle.com/#!2/fd7897/7.

我正在寻找每个接收器的答案,最后一个条目也是排名最高的入口：我非常感谢任何帮助.谢谢你.

像这样的东西：

(‘2′,’4′,’测试……’,’2011-08-21 14:13:19′,’40’,’009′)— rank1

(‘4′,’2′,’30’,’003′)—- rank2

(‘1′,’3′,’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),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','test ...','2011-08-21 14:12:19','20','002'),('4','2011-08-21 14:13:19','30','003'),'3','004'),'005'),'4','006'),'007'),('3','008'),('2','40','009');



INSERT INTO tblB (sno,name ) VALUES
('1','Aa'),'Bb'),'Cc'),'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',('data5','005',('data6','006',('data7','007',('data8','008',('data9','009','101');

最佳答案

问题是子查询中的count(*).将其更改为count(不同的接收者)：

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子句中使用聚合：

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;

mysql – 按最大ID和点排名用户但是(排名错误)

猜你在找的MySQL相关文章