代码之家 › 专栏 › 技术社区 › James McMahon

对于这种子类型/超类型关系,什么查询有效?

mysql sql

James McMahon · 技术社区 · 7 年前

情况就是这样。我有三个表,一个超级类型,两个子类型,子类型之间有关系:

|----------------|    |-------------------| |-------------------|
|      Post      |    |     Top_Level     | |      Comment      |   
|----------------|    |-------------------| |-------------------|   
| PK | ID        |    | PK, FK | Post_ID  | | PK, FK | Post_ID  |   
|    | DATE      |    |        | Title    | |     FK | TopLv_ID |   
|    | Text      |    |-------------------| |-------------------|   
|----------------|

每一篇文章,不管是评论还是顶级文章,都是独一无二的,但是实体共享一些属性。因此,comment和top-lev是post的子类型。那是一份。此外,评论与一个顶级列文帖子相关。此ER图说明了这一点: http://img11.imageshack.us/img11/9327/sampleer.png

我要找的是按活动排序的顶级文章列表,可以是创建顶级文章,也可以是对该文章的评论。

例如,假设我们有以下数据:

|------------------------|    |------------------|    |--------------------|
|      Post              |    |     Top_Level    |    |       Comment      |
|------------------------|    |------------------|    |--------------------|
| ID |    DATE    | Text |    | Post_ID  | Title |    | Post_ID  |TopLv_ID |
|----|------------|------|    |----------|-------|    |----------|---------|
|  1 | 13/03/2008 | shy  |    |     1    |  XYZ  |    |     2    |    1    |
|  2 | 14/03/2008 | mrj  |    |     3    |  ABC  |    |     4    |    1    |
|  3 | 15/03/2008 | quw  |    |     7    |  NMO  |    |     5    |    3    |
|  4 | 16/03/2008 | ksi  |    |------------------|    |     6    |    1    |
|  5 | 17/03/2008 | kso  |                            |--------------------|
|  6 | 18/03/2008 | aoo  |                            
|  7 | 19/03/2008 | all  |                            
|------------------------|     

|--------------------------------|
|            RESULT              |
|--------------------------------|
| ID |    DATE    | Title | Text |
|----|------------|-------|------|
|  7 | 19/03/2008 |  123  | all  |
|  1 | 13/03/2008 |  ABC  | shy  |
|  3 | 15/03/2008 |  XYZ  | quw  |
|--------------------------------|

这可以用一个select语句来完成吗?如果是这样,怎么办?

4 回复 | 直到 17 年前

Bill Karwin 17 年前

我尝试了这个查询,它给出了您描述的输出:

SELECT pt.id, pt.`date`, t.title, pt.`text`
FROM Top_Level t INNER JOIN Post pt ON (t.post_id = pt.id)
 LEFT OUTER JOIN (Comment c INNER JOIN Post pc ON (c.post_id = pc.id))
   ON (c.toplv_id = t.post_id)
GROUP BY pt.id
ORDER BY MAX(GREATEST(pt.`date`, pc.`date`)) ASC;

输出:

+----+------------+-------+------+
| id | date       | title | text |
+----+------------+-------+------+
|  7 | 2008-03-19 | NMO   | all  | 
|  1 | 2008-03-13 | XYZ   | shy  | 
|  3 | 2008-03-15 | ABC   | quw  | 
+----+------------+-------+------+

dkretz 17 年前

你可能无意中完全弄混了你的问题,使它很难理解和回答(至少对于我们这些头脑小的人来说),你能用合理的表和字段名,以及更完整的索引描述来重述它吗?

编辑:

您是否可能描述一种销售产品的情况,有时相同的产品也可以作为组件递归地包含在其他产品中?如果是这样,就有更多传统的成功方法来模拟这种情况。

dkeen 17 年前

只要“最后一次发生的日期”在sub1和sub2列中表示,这绝对是可能的。请注意,我列出的代码大部分是伪代码…你必须根据你的DBMS风格来填充空白。

要将结果集限制为super和sub1中的列(不带重复项):

SELECT DISTINCT sub1.*, super.*

(显然,您应该尽量避免选择*……选择您实际需要的列)

您的From子句如下所示:

FROM super INNER JOIN sub1 ON super_id
    LEFT JOIN sub2 ON super_id AND sub1_id

order by将使用coalesce函数(如果您的DBMS支持它)来确定要排序的列(coalesce选择参数列表中的第一个非空值):

ORDER BY COALESCE(sub2.dateColumn, sub1.dateColumn)

或者,如果没有可用的合并,但有可用的isNull函数,则可以链接isNull:

ORDER BY ISNULL(firstDateColumn, ISNULL(secondDateColumn, thirdDateColumn))

Dan Breslau 17 年前

您没有建议或暗示可以重新设计模式,所以我提出这个问题可能不是对您和我的时间的最佳利用,但是:您的表可以吗? 有这样设计?此应用程序是否已在生产中使用?

你的陈述引发了我的思考:

所有事件都发生在一个日期,但编辑必须与原始创建事件

我怀疑这是SUB2包含FK到SUB1的理由。但在我看来,这就像是非规范化(关系方面)或违反了 DRY 原则(用敏捷的术语)在任何一种情况下,它都可能使你的生活比需要的更复杂。

我的建议(值得你付出代价)是把fk去掉到sub1。代替它,您可以1)在页面的创建事件中包含一个select,作为查询的一部分;或者2)将创建日期移到页面表中。(您选择哪一个选项取决于模式中存在多少额外的复杂性;我通常更喜欢1而不是2。)

我认为这将简化您的更新和查询。您的里程可能会有所不同。