我有这3张桌子

forums_forum

+-----+--------+-------------+-------+-----+
| fid | name   | description | index | cid |
+-----+--------+-------------+-------+-----+
|  36 | gdghdf | hjghj       |    54 |   5 |
|  45 | yutuy  | iuyi        |    99 |   6 |
+-----+--------+-------------+-------+-----+

forums_threads

+----+-----+-----+-------+-------+------+-----------+------+
| id | tid | fid | moved | mfrom | view | important | lock |
+----+-----+-----+-------+-------+------+-----------+------+
|  1 |   4 |  36 |     0 | NULL  |    0 |         0 |    0 |
|  2 |  12 |  36 |     0 | NULL  |    7 |         0 |    0 |
|  3 |   9 |  15 |     0 | NULL  |    0 |         0 |    0 |
+----+-----+-----+-------+-------+------+-----------+------+

forums_posts

+----+-------+--------+--------+---------------------+--------+--------+-----+
| id | title | detail | author | date                | edited | editby | tid |
+----+-------+--------+--------+---------------------+--------+--------+-----+
|  1 | asfsd | sdfsd  |      1 | 2010-07-01 21:31:29 |      0 | NULL   |   4 |
+----+-------+--------+--------+---------------------+--------+--------+-----+

我正在尝试创建返回结果的查询 – >对于每个独特的’fid’,来自’forums_posts’的一行(ORDER BY’日期’).

forums_forum.fid = forums_threads.fid
forums_threads.tid = forums_posts.tid

谢谢

SELECT p.* FROM forums_posts p JOIN forums_threads t ON p.tid = t.tid
WHERE NOT EXISTS (
    SELECT * FROM forums_posts p2 JOIN forums_threads t2 ON p2.tid = t2.tid
    WHERE t.fid = t2.fid AND p.date < p2.date
);

SELECT * FROM orders WHERE (customer_num=104 AND order_num>1001) OR order_num=1008 

SELECT * FROM orders WHERE customer_num=104 AND order_num>1001 
UNION 
SELECT * FROM orders WHERE order_num=1008 

SELECT * FROM customer WHERE zipcode LIKE “98_ _ _” 

SELECT cust.name,rcVBles.balance,……other columns 
FROM cust,rcvbles 
WHERE cust.customer_id = rcvlbes.customer_id 
AND rcvblls.balance>0 
AND cust.postcode>“98000” 
ORDER BY cust.name 

SELECT cust.name,rcvbles.balance,rcvbles 
WHERE cust.customer_id = rcvlbes.customer_id 
AND rcvblls.balance>0 
ORDER BY cust.name 
INTO TEMP cust_with_balance 

SELECT * FROM cust_with_balance 
WHERE postcode>“98000” 

我正在尝试获取与我下载的每个帖子相关的最新1或2条评论，有点像instagram一样，因为它们显示每个帖子的最新3条评论，到目前为止，我正在获得帖子和喜欢的计数。

现在，我需要做的就是弄清楚如何获取最新评论，而不是太确定如何处理它，这就是为什么我希望拥有更多专业知识的人可以为我提供帮助！

这是我当前的查询：

(SELECT
        P.uuid,P.caption,P.imageHeight,P.path,P.date,U.id,U.fullname,U.coverImage,U.bio,U.username,U.profileImage,coalesce(Activity.LikeCNT,0),Activity.CurrentUserLiked
        FROM USERS AS U
        INNER JOIN Posts AS P 
        ON P.id = U.id
        LEFT JOIN (SELECT COUNT(DISTINCT Activity.uuidPost) LikeCNT,Activity.uuidPost,Activity.id,sum(CASE WHEN Activity.id = $id then 1 else 0 end) as CurrentUserLiked
        FROM Activity Activity
        WHERE type = 'like' 
        GROUP BY Activity.uuidPost) Activity
        ON Activity.uuidPost = P.uuid
        AND Activity.id = U.id
        WHERE U.id = $id)
UNION
        (SELECT 
        P.uuid,Activity.CurrentUserLiked
        FROM Activity AS A
        INNER JOIN USERS AS U 
        ON A.IdOtherUser=U.id
        INNER JOIN Posts AS P 
        ON P.id = U.id
        LEFT JOIN (SELECT COUNT(DISTINCT Activity.uuidPost) LikeCNT,sum(CASE WHEN Activity.id = $id then 1 else 0 end) as CurrentUserLiked
    FROM Activity Activity
    WHERE type = 'like' 
    GROUP BY Activity.uuidPost) Activity
    ON Activity.uuidPost = P.uuid
    AND Activity.id = U.id
    WHERE A.id = $id)
    ORDER BY date DESC
    LIMIT 0,5

基本上，评论与点赞存储在同一表中。

所以表是Activity，然后我有一列comment存储注释文本，然后“类型”等于“注释”。

可能解释得不是很好，但我愿意尝试并提供尽可能多的细节！

如果有人可以帮助，将不胜感激！！

更新

在给出的此查询中，我目前收到此错误：

操作’=’的排序规则（utf8_general_ci，IMPLICIT）和（utf8_unicode_ci，IMPLICIT）的非法混合

SELECT Posts.id,Posts.uuid,Posts.caption,Posts.path,Posts.date,USERS.id,USERS.username,USERS.fullname,USERS.profileImage,coalesce(A.LikeCNT,com.comment
FROM Posts 
INNER JOIN USERS 
  ON Posts.id = 145 
 AND USERS.id = 145
LEFT JOIN (SELECT COUNT(A.uuidPost) LikeCNT,A.UUIDPost
    FROM Activity A
    WHERE type =  'like' 
    GROUP BY A.UUIDPOST) A
 on A.UUIDPost=Posts.uuid
LEFT JOIN (SELECT comment,UUIDPOST,@row_num := IF(@prev_value=UUIDPOST,@row_num+1,1) as row_number,@prev_value := UUIDPOST
           FROM Activity 
           CROSS JOIN (SELECT @row_num := 1) x
           CROSS JOIN (SELECT @prev_value := '') y
           WHERE type = 'comment'
           ORDER BY UUIDPOST,date DESC) Com
  ON Com.UUIIDPOSt = Posts.UUID
 AND row_number <= 2
ORDER BY date DESC
LIMIT 0,5

最新编辑

表结构：

帖子

    ----------------------------------------------------------
    | id         | int(11)      |                 | not null |
    | uuid       | varchar(100) | utf8_unicode_ci | not null |
    | imageLink  | varchar(500) | utf8_unicode_ci | not null |
    | date       | timestamp    |                 | not null |
    ----------------------------------------------------------

用户

    -------------------------------------------------------------
    | id            | int(11)      |                 | not null |
    | username      | varchar(100) | utf8_unicode_ci | not null |
    | profileImage  | varchar(500) | utf8_unicode_ci | not null |
    | date          | timestamp    |                 | not null |
    -------------------------------------------------------------

活动

    ----------------------------------------------------------
    | id           | int(11)      |                 | not null |
    | uuid         | varchar(100) | utf8_unicode_ci | not null |
    | uuidPost     | varchar(100) | utf8_unicode_ci | not null |
    | type         | varchar(50)  | utf8_unicode_ci | not null |
    | commentText  | varchar(500) | utf8_unicode_ci | not null |
    | date         | timestamp    |                 | not null |
    ----------------------------------------------------------

这些是一些示例，在这种情况下，“活动”表中的“类型”将始终等于“注释”。

总结一切和欲望的结果：

当我查询用户帖子时，我希望能够进入“活动”表并为他的每个帖子获取 最新的
2条评论。也许没有评论，所以很明显它将返回0，或者该帖子可能有100条评论。但我只想获取最新/最近的2条评论。

一个例子可能是看Instagram如何做到这一点。对于每个帖子，显示最新的评论1、2或3 …

希望这可以帮助！