MYSQL查询问题：选择所有线程，按线程对应的号数排序

发布于 2024-11-03 06:39:08 字数 918 浏览 0 评论 0原文

我的标题可能有点令人困惑，所以这是我的问题。

这是我的表：

CREATE TABLE `b_posts` (
  `thread` int(12) NOT NULL,
  `no` int(12) NOT NULL,
  `comment` text NOT NULL,
  `gone` int(1) NOT NULL default '0',
  PRIMARY KEY  (`no`),
  FULLTEXT KEY `comment` (`comment`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

我现在需要一个查询，选择所有未消失的线程（意味着消失为 0，如果完成为 1，则意味着该线程已被删除）。选择线程的顺序应该根据与原始线程具有相同线程的否的数量。

进一步说明：

thread | no | comment | gone
100 | 100 | hello there, this is the thread! | 0
100 | 102 | this is a reply in the thread 100 | 0
100 | 103 | another reply in the same thread | 0
104 | 104 | this is a different thread | 0
104 | 105 | a reply to the different thread | 0

我现在想通过查询按以下顺序获取以下数据：（

thread | no | comment | gone
100 | 100 | hello there, this is the thread! | 0
104 | 104 | this is a different thread | 0

线程启动器是在线程 == no 时定义的）

原文

My title probably is a bit confusing, so here is my problem.

This is my table:

CREATE TABLE `b_posts` (
  `thread` int(12) NOT NULL,
  `no` int(12) NOT NULL,
  `comment` text NOT NULL,
  `gone` int(1) NOT NULL default '0',
  PRIMARY KEY  (`no`),
  FULLTEXT KEY `comment` (`comment`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

I now need a query which selects ALL threads which are not gone (means gone is 0, if done is 1 this would mean the thread has been deleted). The order of which the threads are selected should be by the amount of no's who have the same thread like the original.

Further explanation:

thread | no | comment | gone
100 | 100 | hello there, this is the thread! | 0
100 | 102 | this is a reply in the thread 100 | 0
100 | 103 | another reply in the same thread | 0
104 | 104 | this is a different thread | 0
104 | 105 | a reply to the different thread | 0

I now want to get the following data in the following order with my query:

thread | no | comment | gone
100 | 100 | hello there, this is the thread! | 0
104 | 104 | this is a different thread | 0

(Thread starter is defined when thread == no)

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

土豪我们做朋友吧 2024-11-10 06:39:08

由于您的限定符“thread = no”和“gone = 0”，我认为将它们包含在结果列中没有意义......但是，我确实包含了每个线程总条目的子计数

select b.thread,
       b.comment,
       postCounts.TotalEntries
   from
      b_posts b
         join ( select b2.thread, count(*) as TotalEntries 
                    from b_posts b2
                    group by b2.thread ) postCounts
            on b.thread = postcounts.thread
   where
          b.thread = b.no
      and b.gone = 0
   order by
      postCounts.TotalEntries DESC

Since your qualifier of "thread = no" and "gone = 0", I saw no point in including them in the result columns... however, I did include the sub-count of total entries per thread

select b.thread,
       b.comment,
       postCounts.TotalEntries
   from
      b_posts b
         join ( select b2.thread, count(*) as TotalEntries 
                    from b_posts b2
                    group by b2.thread ) postCounts
            on b.thread = postcounts.thread
   where
          b.thread = b.no
      and b.gone = 0
   order by
      postCounts.TotalEntries DESC

回复收藏 0 原文

~没有更多了~