如何构造这个SQL查询？ [英] How to structure this SQL query?

问题描述

所以基本上我在我的网站上收到新内容的通知。我有4个表格 -

• 文章


• 媒体


• 更新


• 评论

每个表格都有一组自己的列这些如果有人想）。每个表格都有一个不同的列，这是 time（）函数中的时间戳列（ big int ）。我得到最后30个修改的解决方案是从这4个表中选择前30个行按时间戳降序排序。

这是我到目前为止的查询，它不工作，我想知道是否有人可以帮助我。 -

  SELECT * FROM`articles` 
 UNION SELECT * FROM`media` 
 UNION SELECT * FROM `updates'
 UNION SELECT * FROM`comments` 
 ORDER BY`timestamp` DESC 
 LIMIT 30 
  

EDIT：
我之前也使用了另一个查询 -

  SELECT * FROM`articles`，`media`，`updates`，`comments` 
 ORDER BY`timestamp` DESC 
 LIMIT 30 
  / pre> 
 
 
并持续收到此错误 -  
 在订单子句中的'timestamp'列是不明确的 
 
 
 
  EDIT 2  
 
 
现在我意识到我必须使用我的声明将这些结果合并成一个表。 
解决方案
 
  SELECT a。*，m。*，u。*，c。从文章AS a 
 LEFT JOIN媒体AS m ON（m.timestamp = a.timestamp）
 LEFT JOIN更新AS u ON（u.timestamp = a.timestamp）
 LEFT JOIN注释AS c ON（c.timestamp = a.timestamp）
 ORDER BY timestamp desc LIMIT 30 
  
 
So basically I'm getting notifications of new content on my website. I have 4 tables -


articles
media
updates
comments


Each table has a set of its own columns (I can include these if anyone wants). There is one distinct column every table has, this is the timestamp column (a big int formatted column with data from the PHP time() function). My solution to getting the last 30 modifications is to select the first 30 rows from these 4 tables ordered by timestamp descending. 

Here is the query I have so far, it doesn't work and I'm wondering if someone could help me. -
SELECT * FROM `articles` 
UNION SELECT * FROM `media` 
UNION SELECT * FROM `updates` 
UNION SELECT * FROM `comments` 
ORDER BY `timestamp` DESC 
LIMIT 30
EDIT:
I was also using another query before - 
SELECT * FROM `articles` ,`media` ,`updates` ,`comments` 
ORDER BY `timestamp` DESC 
LIMIT 30
and kept getting this error -
Column 'timestamp' in order clause is ambiguous

EDIT 2

I realise now I have to use the AS clause in my statement to combine these results into one table. 
解决方案
SELECT a.*,m.*,u.*,c.* from articles AS a
LEFT JOIN media AS m ON (m.timestamp = a.timestamp)
LEFT JOIN updates AS u ON (u.timestamp = a.timestamp)
LEFT JOIN comments AS c ON (c.timestamp = a.timestamp)
ORDER BY timestamp desc LIMIT 30


                        
这篇关于如何构造这个SQL查询？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！