doujiu4643
doujiu4643
2014-09-10 10:03

在SQLite3中查看

已采纳

I have little troubles with my SQL code. I have following table:

CREATE TABLE post (
    post INTEGER,
    content TEXT,
    time INTEGER,
    owner TEXT,
    thread TEXT, 
    pharse TEXT);

Of course the table have default values, options, etc. owner has a foreign key to another table with users. Column thread could have multiple same values.

I want to build a view like this:

CREATE VIEW thread AS
SELECT 
    thread,
    MIN(time) AS creation,
    owner,
    pharse,
    MAX(time) AS last,
    COUNT(post) as count
FROM post
GROUP BY thread
ORDER BY time DESC;

thread group my view, creation is a creation time of first post, owner is a creator of first post, pharse is a pharse of first post, last is a creation time of last post, and count is a count of all posts with a same thread value.

And everything would be fine except the MAX(time) AS last, this column forces the view to show the owner of the last post, when I want to see the owner of first. When I kick off that line from code everything works fine, but I need it to sort my view, and this is only cause I want to keep it - for sorting.

Order of columns has no mean for me. I'm using this queries in PHP if it has any importance.

If there would be another way or somebody could tell me how to fix my code I will be pleased.

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享
  • 邀请回答

1条回答

  • duandangqin0559 duandangqin0559 7年前

    To prevent the MAX() from influencing the rest of the query, you have to move it into a subquery:

    SELECT thread,
           MIN(time) AS creation,
           owner,
           pharse,
           (SELECT MAX(time)
            FROM post AS p2
            WHERE p2.thread = post.thread
           ) AS last,
           COUNT(post) as count
    FROM post
    GROUP BY thread
    
    点赞 评论 复制链接分享

相关推荐