Question

之后:

SELECT forum_categories.id AS category_id, forum_categories.title, forum_topics.id AS topic_id, forum_topics.title, user
FROM forum_categories JOIN forum_topics ON forum_categories.id=forum_topics.category_id
LEFT OUTER JOIN forum_views ON forum_topics.id=forum_views.topic_id
WHERE forum_categories.id=6
ORDER BY forum_categories.id

我得出以下结果:

“entergraph

现在我要:

add another field in the select, called thereIsANull
group all my results by the field ~~user~~ category_id
setting thereIsANull to 1 if there isn t a NULL in the field user (while grouping), 0 otherwise.

因此,如下文所示,结果必须是一行:

6    Welcome    (some topic_id)     (some title)   (djfonplaz or null)   0

并且,如果所有用户都不同于NCL :

6    Welcome    (some topic_id)     (some title)   (djfonplaz)   1

我如何能够在我的Sql上这样做?

Answer 1

SELECT 
  forum_categories.id AS category_ids
  , forum_categories.title as titles
  , forum_topics.id AS topic_ids
  , forum_topics.title as topic_titles
  , count(*) as NumberOfRows
  , GROUP_CONCAT(IFNULL(user, (no user) )) AS users 
  , (count(*) - count(user)) as IsThereANull  
FROM forum_categories 
INNER JOIN forum_topics ON forum_categories.id=forum_topics.category_id
LEFT OUTER JOIN forum_views ON forum_topics.id=forum_views.topic_id
WHERE forum_categories.id=6
GROUP BY category_id
ORDER BY forum_categories.id

代码<0,如无,则不超过1。

Answer 2

我认为,下面的询问应当取得你所期望的结果:

SELECT forum_categories.id AS category_id, forum_categories.title, 
       forum_topics.id AS topic_id, forum_topics.title, user, 
       (
          SELECT MAX(IF(COUNT(*), 0, 1))
          FROM forum_categories JOIN forum_topics ON 
               forum_categories.id=forum_topics.category_id
          LEFT OUTER JOIN forum_views ON forum_topics.id=forum_views.topic_id
          WHERE forum_categories.id=6 AND user IS NULL
    ) AS thereIsANull
FROM forum_categories JOIN forum_topics ON forum_categories.id=forum_topics.category_id
LEFT OUTER JOIN forum_views ON forum_topics.id=forum_views.topic_id
WHERE forum_categories.id=6 AND user IS NOT NULL
GROUP BY user
ORDER BY forum_categories.id

Answer 3

我认为,正如你所希望的那样:

SELECT cat.id AS category_id, 
       cat.title, 
       top.id AS topic_id, 
       top.title, 
       user,
       (
           (
               SELECT COUNT(*) FROM forum_categories 
               WHERE id = cat.id AND user IS NULL
           ) > 0 -- This is to check wether if any NULL is found
       ) as thereIsANull 
FROM forum_categories as cat
JOIN forum_topics as top ON cat.id = top.category_id
LEFT OUTER JOIN forum_views as view ON top.id = view.topic_id
WHERE cat.id = 6 -- You can ofcourse change this to whatever variable your using
GROUP BY cat.id -- Just an ordinary group by category id
ORDER BY cat.id

假设外地用户在论坛——类别表格中找到,那么其他明智的做法就是修改分局,以便加入表格,使用户从用户那里获得。

注 Thiis与

问它是否正确:

友情链接