时间戳

首页 > 解决方案 > 如何获取类别和子类别sql

sql - 如何获取类别和子类别sql

问题描述

我正在尝试使用此 SQL 查询获取所有子类别的所有类别：

SELECT 
    sections.Section_Name,
    sections.Section_Page,
    topics.Topic_Name,
    topics.Topic_Descr,
    topics.Section_ID,
    (select count(*) from threads WHERE threads.Topic_ID= topics.Topic_ID and threads.Topic_ID=topics.Topic_ID) as Thread,
    (select count(*) from threads join posts on posts.Thread_ID= threads.Thread_ID where threads.Topic_ID =topics.Topic_ID) as Post,
    **(SELECT Thread_Time 
       FROM
           (SELECT Thread_Time FROM threads
            WHERE threads.Topic_ID = Topic_ID 
            ORDER BY threads.Thread_ID Desc limit 1) AS Time) AS Time**
FROM 
    topics, sections 
WHERE 
    topics.Section_ID = sections.Section_ID

它工作正常，但 Thread_Time 查询在所有行（记录）上显示相同的日期？

标签： sql

解决方案

对于此查询，使用子查询很好。如果你想要最近的时间，那么我认为你可以这样做：

select s.Section_Name, s.Section_Page,
       t.Topic_Name, t.Topic_Descr, t.Section_ID,
       (select count(*)
        from threads th 
        where th.Topic_ID = t.Topic_ID
       ) as Thread,
       (select count(*)
        from threads th join
             posts p
             on p.Thread_ID = th.Thread_ID
        where th.Topic_ID = t.Topic_ID
       ) as Post,
       (select max(th.Thread_Time)
        from threads th
        where th.Topic_ID = t.Topic_ID 
       )as Time
from topics t join
     sections s
     on t.Section_ID = s.Section_ID;

您的查询的问题是这个子句： threads.Topic_ID=Topic_ID. 这只是引用子查询中的值。

推荐阅读