mysql - 如何计算个人资料浏览量？

Question

这是我的表结构:

-- users
+----+--------+---------------------+----------+--------------------+
| id |  name  |       email         | password |      cookie        |
+----+--------+---------------------+----------+--------------------+
| 1  | Jack   | jack0011@gmail.com  | 098u23r8 | kj3409t34034u8rf   |
| 2  | Peter  | wanter_s@gmail.com  | k0io34r4 | m32i0949y834rf34   |
| 3  | Martin | dr.mrtn@yahoo.com   | fg345t34 | rf34r89p23r49i34   |
+----+--------+---------------------+----------+--------------------+

-- user_details
+---------+------+----------------------+----------------+
| user_id | age  |          about       |   birth_date   |
+---------+------+----------------------+----------------+
| 1       | 32   | NULL                 | NULL           |
| 2       | NULL | A senior developer   | 1992-02-10     |
| 3       | 18   | NULL                 | 1999-01-06     |
+---------+------+----------------------+----------------+

-- profile_views
+----+---------+-----------+
| id | user_id | viewer_id |
+----+---------+-----------+
| 1  | 2       | 1         |
| 2  | 1       | 2         |
| 3  | 1       | 3         |
+----+---------+-----------+
-- Unique Index: seen(user_id,viewer_id)

这是我的VIEW:

CREATE VIEW `user` AS 
SELECT u.id,
       u.name,
       ud.age,
       ud.about,
       ud.birth_date
FROM users u
INNER JOIN user_details ON u.id = ud.user_id

---

当前输出:

-- Assuming :user_id = 1
-- SELECT * FROM user WHERE id = :user_id
+----+--------+------+----------------------+----------------+
| id |  name  | age  |          about       |   birth_date   |
+----+--------+------+----------------------+----------------+
| 1  | Jack   | 32   | NULL                 | NULL           |
+----+--------+------+----------------------+----------------+

预期输出:

+----+--------+------+----------------------+----------------+---------------+
| id |  name  | age  |          about       |   birth_date   | profile_views |
+----+--------+------+----------------------+----------------+---------------+
| 1  | Jack   | 32   | NULL                 | NULL           | 2             |
+----+--------+------+----------------------+----------------+---------------+

我该怎么做？

Answer 1

您必须加入个人资料 View 以及用户详细信息，例如

SELECT distinct u.id,
       u.name,
       ud.age,
       ud.about,
       ud.birth_date
       count(pf.viewer_id)
FROM users u
INNER JOIN user_details ud ON u.id = ud.user_id
INNER JOIN profile_views pf ON u.id = pf.user_id
group by u.id,
           u.name,
           ud.age,
           ud.about,
           ud.birth_date