我有一个问题与以下查询:(注意，长查询!!)
SELECT
c.frm_category_id,
c.name,
c.slug,
s.frm_category_id,
s.name,
s.slug,
s.description,
IFNULL(topics_count,0),
IFNULL(messages_count,0),
n.frm_message_id,
n.user_id,
n.frm_topic_id,
n.timestamp,
n.title,
n.slug,
u.account_type,
u.username
FROM
frm_categories AS c
LEFT JOIN(
SELECT
frm_category_id,
parent_frm_category_id,
name,
slug,
description
FROM
frm_categories
WHERE
parent_frm_category_id != 0
) AS s
ON
c.frm_category_id = s.parent_frm_category_id
LEFT JOIN(
SELECT
frm_category_id,
frm_topic_id,
COUNT(frm_topic_id) AS topics_count
FROM
frm_topics
GROUP BY
frm_category_id
) AS t
ON
s.frm_category_id = t.frm_category_id
LEFT JOIN(
SELECT
COUNT(frm_message_id) AS messages_count,
frm_topic_id,
frm_category_id
FROM
frm_messages
GROUP BY
frm_category_id
) AS m
ON
t.frm_topic_id = m.frm_topic_id
LEFT JOIN(
SELECT
MAX(m.frm_message_id) AS frm_message_id,
m.user_id,
m.frm_category_id,
m.frm_topic_id,
m.timestamp,
t.title,
t.slug
FROM
frm_messages AS m,
frm_topics AS t
WHERE
m.frm_topic_id = t.frm_topic_id
GROUP BY
m.frm_topic_id
) AS n
ON
s.frm_category_id = n.frm_category_id
LEFT JOIN
users AS u
ON
n.user_id = u.user_id
WHERE
c.parent_frm_category_id = 0
ORDER BY
c.frm_category_id ASC,
s.frm_category_id ASC
解释:
SELECT =>我选择父类别的category_id,name和slug(因为WHERE parent_frm_category_id = 0)
FIRST LEFT JOIN =>我选择父类别的子类别(多于1)
SECOND LEFT JOIN =>我计算每个子类别中的所有主题
THIRD LEFT JOIN =>我计算每个子类别中的所有消息
第四个左JOIN =>我选择每个子类别的最后一条消息(出错的地方)
第五个左JOIN =>我选择每个子类别的最后一条消息的account_type和用户名
有什么问题吗?
I不选择最后一条消息，而是一个随机消息。
有人能帮我一下吗?:)提前感谢!
这能满足您的需求吗?
SELECT c.frm_category_id,
c.name,
c.slug,
s.frm_category_id,
s.name,
s.slug,
s.description,
IFNULL(topics_count,0),
IFNULL(messages_count,0),
n.frm_message_id,
n.user_id,
n.frm_topic_id,
n.timestamp,
n.title,
n.slug,
u.account_type,
u.username
FROM frm_categories AS c
LEFT JOIN frm_categories s ON s.frm_category_id = c.parent_frm_category_id AND s.parent_frm_category_id != 0
LEFT JOIN(
SELECT frm_category_id,
COUNT(frm_topic_id) AS topics_count
FROM frm_topics
GROUP BY frm_category_id
) AS t ON t.frm_category_id = s.frm_category_id
LEFT JOIN(
SELECT COUNT(frm_message_id) AS messages_count,
frm_category_id
FROM frm_messages
GROUP BY frm_category_id
) AS m ON m.frm_category_id = t.frm_category_id
LEFT JOIN(
SELECT MAX(m.frm_message_id) AS frm_message_id,
m.user_id,
m.frm_category_id,
m.frm_topic_id,
m.timestamp,
t.title,
t.slug
FROM frm_messages AS m,
frm_topics AS t
WHERE m.frm_topic_id = t.frm_topic_id
GROUP BY m.frm_category_id
) AS n ON s.frm_category_id = n.frm_category_id
LEFT JOIN users AS u ON n.user_id = u.user_id
WHERE c.parent_frm_category_id = 0
ORDER BY c.frm_category_id ASC, s.frm_category_id ASC