创建一个获取用户记录排名、用户名和计数的视图 [英] create a view which gets rank,username and count of user records

问题描述

我在下面有一张表.我无法创建视图，该视图显示来自另一个表和排名的用户和用户名的记录计数，让我们说类别1".如何创建视图?我真的很感谢任何帮助.提前致谢.

I have a table below .I am unable to create view which displays count of records w.r.t user and username from another table and rank ,lets say for category '1'.How do I create a view ? I really appreciate any help .Thanks in Advance.

CREATE TABLE if not exists tblA
(
id int(11) NOT NULL auto_increment ,
user varchar(255),
 category int(255),
 PRIMARY KEY (id)
);

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


INSERT INTO tblA (user, category ) VALUES
('1', '1'),
('1', '2'),
('1', '3'),
('1', '1'),
('2', '1'),
('2', '1'),
('2', '1'),
('2', '1'),
('3', '1'),
('2', '1'),
('4', '1'),
('4', '1'),
('2', '1');


INSERT INTO tblB (userid, username ) VALUES
('1', 'A'),
('2', 'B'),
('3', 'C'),
('4', 'D'),
('5', 'E');

我试过了，但没有用:

create view v as
SELECT
  tblB.username,
  groups.*,
  @rank:=@rank+1 AS rank
FROM
  (select 
    user,
    category,
    count(*) as num
  from 
    tblA
  where 
    category=1 
  group by 
    user, 
    category
  order by 
    num desc,
    user) AS groups
  -- left join: in case if data integrity fails:
  left join
    tblB ON groups.user=tblB.userid
  CROSS JOIN (SELECT @rank:=0) AS init

结果应该是:

  username   user      category     num   rank  
    B           2      1            6       1
    A           1      1            2       2
    D           4      1            2       3
    C           3      1            1       4

推荐答案

您走对了.只需要做些小改动.以下查询将为您提供所需的结果.在内部查询中得到了前 4 列，为了让排名交叉加入 (SELECT @curRank := 0) r 这是 MySQL 获得排名的技巧.最后只需要通过 Cnt 订购即可.

You were on the right track. Just needed to make minor changes. The following query will give you the desired results. In inner query got the first 4 columns and to get rank cross joined that to (SELECT @curRank := 0) r which is MySQL trick for getting rank. in the end just needed to order by Cnt to make it work.

SELECT username
    ,userid
    ,category
    ,Cnt
    ,@curRank := @curRank + 1 AS rank
    FROM (
            SELECT b.Username
                ,B.userid
                ,A.category
                ,count(*) Cnt
            FROM tblb B 
            JOIN tbla A
                ON B.UserID = A.User
            WHERE a.Category = 1
            GROUP BY b.username
        )a
,(SELECT @curRank := 0) r
Order by cnt desc

为了将其放入 View，您可以使用 @Gordon-Linoff 在这个问题中

In order to put it into View you can use hack described by @Gordon-Linoff in this question

结束代码看起来像这样.

End code will look something like this.

CREATE VIEW TestView1
AS
    SELECT b.Username
           ,B.userid
           ,A.category
           ,COUNT(*) Cnt
        FROM tblb B
        JOIN tbla A
            ON B.UserID = A.User
        WHERE a.Category = 1
        GROUP BY b.username
        ORDER BY cnt DESC;

CREATE VIEW TestView2
AS
    SELECT t1.*
           ,( SELECT 1 + COUNT(*)
                FROM TestView1 AS t2
                WHERE t2.Cnt > t1.Cnt
                    OR (
                         t2.Cnt = t1.Cnt
                         AND t2.userid < t1.userid ) ) AS Rank
        FROM TestView1 AS t1

TestView1 用于获取您定义的前 4 列.TestView2 您只需从第一个视图中选择所有内容，然后添加列来检查您选择的值是否大于或小于该视图的第一个实例中的值.

TestView1 is used to get first 4 columns that you defined. TestView2 you just select everything from first view and than add column that checks to see if value that you selecting is ether bigger or smaller than value in first instance of that view.

这篇关于创建一个获取用户记录排名、用户名和计数的视图的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！