mysql - 从 MYSQL SELECT 到 MYSQL VIEW

Question

我不知道怎么做。

---

背景:

配置文件 -> 用户配置文件表

分数 -> 用户分数表(一个用户可以有多个分数)(这个表有一个 created_at 字段可以知道我何时引入新分数)

想象一下，分数表保存了 klout 分数数据。

---

如何将此查询转换为 View ？

SELECT  t1.id, 
    t1.name, 
    t1.screen_name, 
    t1.description, 
    t1.url, 
    t1.statuses_count, 
    t1.followers_count, 
    t1.friends_count, 
    t1.listed_count, 
    t1.favourites_count, 
    t1.utc_offset, 
    t1.time_zone, 
    t1.verified, 
    t1.lang, 
    t1.profile_image_url, 
    t1.geo_enabled, 
    t1.location, 
    t1.lat, 
    t1.lng, 
    t1.created_at, 
    t3.score, 
    t3.delta, 
    t3.detail
        FROM profiles t1 LEFT JOIN (SELECT t2.user_id, t2.score, t2.delta, t2.detail
                                                    FROM scores t2
                                                    ORDER BY t2.created_at DESC) 
                AS t3 ON t3.user_id = t1.id
        GROUP BY t1.id

Answer 1

VIEW 不能包含子查询的问题，你查询中的子查询可以直接连接到表上，因为你没有执行任何聚合。

CREATE VIEW viewName
AS
SELECT  t1.id, 
        t1.name, 
        t1.screen_name, 
        t1.description, 
        t1.url, 
        t1.statuses_count, 
        t1.followers_count, 
        t1.friends_count, 
        t1.listed_count, 
        t1.favourites_count, 
        t1.utc_offset, 
        t1.time_zone, 
        t1.verified, 
        t1.lang, 
        t1.profile_image_url, 
        t1.geo_enabled, 
        t1.location, 
        t1.lat, 
        t1.lng, 
        t1.created_at, 
        t3.score, 
        t3.delta, 
        t3.detail
FROM    profiles t1 
        LEFT JOIN scores t3
            ON t3.user_id = t1.id
-- GROUP BY t1.id

我不明白为什么您的查询中有 GROUP BY 子句。