如何在SQL Server 2000中的许多表中使用SELECT子句插入表时使用UNION

I am using SQL Server 2000. I have multiple criterias which i have to use to extract different sets of records from mumtiple table in another table. I am using

我正在使用SQL Server 2000.我有多个标准,我必须使用它从另一个表中的mumtiple表中提取不同的记录集。我在用

INSERT INTO T1(A,B)
(SELECT E,R FROM T2)
UNION
(SELECT Z,X FROM T3)

Query analyzer is throwing error that while using UNION, you should use ORDER BY clause with SELECT. Even after doing that i m not able to union two different queries which are returing same columns in their select clause.

查询分析器抛出错误,在使用UNION时,您应该使用SELECT的ORDER BY子句。即使在这样做之后,我也无法将两个不同的查询联合起来,这些查询在其select子句中返回相同的列。

What is the right way to insert using SELECTS with UNIONS and ORDER BY.

使用带有UNIONS和ORDER BY的SELECTS插入的正确方法是什么。

1 个解决方案

#1

The pseudo is too cryptic (reduced?) It is very unlikely to get 2 columns per cross join of 2 tables in each of the union components

伪是太神秘了(减少了吗?)每个联合组件中每个交叉连接的2个表是非常不可能的

INSERT INTO T1(A,B)
(SELECT * FROM E,R)
UNION
(SELECT * FROM Z,X)

Note: If you have ANY order by clause at all, it must be at the end of the union

注意:如果你有任何order by子句,它必须在union的末尾

INSERT T1(A,B)
SELECT P,Q FROM E,R
UNION
SELECT R,S FROM Z,X

@updated based on error text "Server: Msg 104, Level 15, State 1, Line 1 ORDER BY items must appear in the select list if the statement contains a UNION operator"

@updated基于错误文本“服务器:消息104,级别15,状态1,行1 ORDER BY项目必须出现在选择列表中,如果该语句包含UNION运算符”

This occurs when you have a union that attempts to perform ORDER BY on a column that does not appear in the result. Consider a normal ORDER BY involving non-selected columns

如果您的联合尝试对未出现在结果中的列执行ORDER BY,则会发生这种情况。考虑涉及非选定列的正常ORDER BY

select top 10 name from syscolumns order by xtype

The rows are consistent and the query can be satisfied. However, if you did

行是一致的,并且可以满足查询。但是,如果你这样做了

select top 10 name from syscolumns where xtype > 50
union all
select top 10 name from syscolumns where xtype < 50
order by xtype

EVEN IF xtype exists in both parts of the UNION, but the time it gets presented to ORDER BY (which works at the END over the entire result set), the column is not there. You would have to rewrite it (if you didn't want to show xtype) as

即使在UNION的两个部分中都存在xtype,但是它被呈现给ORDER BY的时间(在整个结果集的END上工作),该列也不存在。你必须重写它(如果你不想显示xtype)

select name from (
    select top 10 name, xtype from syscolumns where xtype > 50
    union all
    select top 10 name, xtype from syscolumns where xtype < 50
) x
order by xtype

Hope that helps

希望有所帮助

#1