结合 select 语句获得的结果集以创建视图 [英] combining resultset obtained by select statements to create a view

问题描述

对于 4 个不同的表，我有 4 个 Select 语句每个 Select 查询给出满足指定条件的最新记录例如:

I have 4 Select statements for 4 different tables each Select query gives latest record meeting specified condition for ex:

Select TOP 1 * from table where column_name = 'something' order by col1 DESC;

现在我必须组合所有 4 个查询的结果集并从组合结果集创建一个视图.

Now I have to combine result set of all 4 queries and create a view from combined result set.

推荐答案

有些数据库不允许您在联合查询中提供order by"子句.

Some DB's don't let you provide an "order by" clause inside on of the unioned queries.

如果您按 col1 desc 排序，则可能是某种类型的列，您可以对其应用 min() 或 max().

If you're ordering by col1 desc, it's possible that it's some type of column you can apply min() or max() to.

如果是这种情况，下面可以解决您的问题(如果没有太多记录，或者如果表很大，col1"和some_column"被索引.)

If that is the case, below could solve your issue (if there aren't too many records, or, if the tables are massive, "col1" and "some_column" are indexed.)

create view some_view as
(
select * from table1
  where some_column = 'something'
  and col1 = (select max(col1) from table1 where some_column = 'something')
UNION ALL
select * from table2 
  where some_column = 'something'
  and col1 = (select max(col1) from table2 where some_column = 'something')
UNION ALL
select * from table3 
  where some_column = 'something'
  and col1 = (select max(col1) from table3 where some_column = 'something')
UNION ALL
select * from table4 
  where some_column = 'something'
  and col1 = (select max(col1) from table4 where some_column = 'something')
)

这篇关于结合 select 语句获得的结果集以创建视图的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！