mysql,从同一个表的列创建视图

0mkxixxg  于 2021-06-18  发布在  Mysql
关注(0)|答案(2)|浏览(319)

我有这张table:

CREATE TABLE one (
  id bigint(11) primary key,
  email varchar(100),
  refer_link varchar(8),
  referrer varchar (8)
);

当用户提交表单时,他们会得到一个唯一的refer链接(refer\u link)。当另一个用户提交表单时,在他的referer列中插入了该链接。
因此,在示例中,我将有一个表:

id      email                   refer_link       referrer
---------------------------------------------------------
1       jerry@jerry.com         ref11111 
2       elaine@elaine.com       ref22222         ref11111
3       george@george.com       ref33333         ref22222
4       kramer@kramer.com       ref44444         ref11111 
5       cosmo@cosmo.com         ref55555         ref44444

如何创建此视图?

email                   refer_email         refer_count
--------------------------------------------------------
jerry@jerry.com                                2 
elaine@elaine.com       jerry@jerry.com        1
george@george.com       elaine@elaine.com      0
kramer@kramer.com       jerry@jerry.com        1 
cosmo@cosmo.com         kramer@kramer.com      0

非常感谢你的帮助!

3gtaxfhh

3gtaxfhh1#

尝试下面的子查询,

$query = "select o.email, 
          IFNULL( (select email from one where o.referrer = refer_link ),'') as refer_email, 
          (select count(referrer) from one where referrer = o.refer_link ) as refer_count 
          from one as o order by id  ";
xdnvmnnf

xdnvmnnf2#

DROP TABLE IF EXISTS my_table;

CREATE TABLE my_table 
(id SERIAL PRIMARY KEY
,email VARCHAR(50) NOT NULL
,referrer INT NULL
);

INSERT INTO my_table VALUES
(1,'jerry@jerry.com',NULL),
(2,'elaine@elaine.com',1),
(3,'george@george.com',2),
(4,'kramer@kramer.com',1),
(5,'cosmo@cosmo.com',4);

SELECT x.*, COUNT(y.id) refer_count FROM my_table x LEFT JOIN my_table y ON y.referrer = x.id GROUP BY x.id;
+----+-------------------+----------+-------------+
| id | email             | referrer | refer_count |
+----+-------------------+----------+-------------+
|  1 | jerry@jerry.com   |     NULL |           2 |
|  2 | elaine@elaine.com |        1 |           1 |
|  3 | george@george.com |        2 |           0 |
|  4 | kramer@kramer.com |        1 |           1 |
|  5 | cosmo@cosmo.com   |        4 |           0 |
+----+-------------------+----------+-------------+

相关问题