union 并列查询问题

[che2piaopiao]

select * from productt where fuid in (select id from classtp where jibie<>1 and id<>144) order by fuid,sortid desc union
select * from productt where fuid in(
select id from classtp where id not in (select distinct fuid from classtp where jibie=0) and jibie=1 and id<>144) order by fuid,sortid desc

SQL1 union SQL2

想要的结果：

sql1的数据
sql2的数据

但出现的结果却是。。

SQL1+SQL2 order by fuid，，，重新排序了。

求救了。 顺祝各位天天开心!

[che2piaopiao]

您说的太深我听不懂啊。。


能说出来看下吗。。 谢谢了

[ws_hgo]

select * from
(
 select * from SQL1
union all
select * from SQL2 
) tt
order by fuid

[ws_hgo]

看我3楼写的

[黄_瓜]

select * from 
(
select * from productt where fuid in (select id from classtp where jibie <>1 and id <>144) order by fuid,sortid desc  union  
select * from productt where fuid in( 
select id from classtp where  id not in (select distinct fuid from classtp where jibie=0) and jibie=1 and id <>144) 
) a 
order by fuid,sortid desc 

[幸运的意外]

去掉每个SQL中的排序,将最后的结果排序一次就可以了.

[yingzhilian2008]

order by fuid,sortid desc

[hhiew]

[Quote=引用 6 楼 beirut 的回复:]
SQL codeselect*from
(select*from producttwhere fuidin (select idfrom classtpwhere jibie<>1and id<>144)orderby fuid,sortiddescunionselect*from producttwhere fuidin(select idfrom classtpwhere idnotin ?-
[/Quote]ORDER BY不能用在子查询中的。。。不行的。

[hhiew]

楼主是不是想SQL1 排序过后结果在上。SQL2排序后结果在下？而不是SQL1+SQL2一起排？试试这个，如果不想显示虚拟的列名，可以把*换成具体的字段名

select * from 
(select * ,'sql1' as 'sql' from productt where fuid in (select id from classtp where jibie <>1 and id <>144) order by fuid,sortid desc  
union  
select * ,'sql2' as 'sql2' from productt where fuid in( 
select id from classtp where  id not in (select distinct fuid from classtp where jibie=0) and jibie=1 and id <>144) )
order by sql,fuid,sortid desc 

[hhiew]

上面的代码要把子查询中的ORDER BY全删了。

[jack15850798154]

select * from
(
select * from SQL1
union all
select * from SQL2
) tt order by fuid

[忆轩辕]

select * from 
( 
select *,1 as tbidx from SQL1 
union all 
select *,2 as tbidx from SQL2 
) tt order by tbidx,fuid 

[feegle_develop]

在外面排序

SELECT *
FROM
(
select * from productt where fuid in (select id from classtp where jibie <>1 and id <>144)-- order by fuid,sortid desc  union 
select * from productt where fuid in(
select id from classtp where  id not in (select distinct fuid from classtp where jibie=0) and jibie=1 and id <>144)-- order by fuid,sortid desc
) AS A
order by fuid,sortid desc

[ChinaITOldMan]

你在外面嵌套一层 再排序回来就行了

[fwacky]

select A.*from
(
select * from productt where fuid in (select id from classtp where jibie <>1 and id <>144)
union
select * from productt where fuid in(
select id from classtp where id not in (
select distinct fuid from classtp where jibie=0) and jibie=1 and id <>144)
)A
order by A.fuid,A.sortid desc

[cl18850511]

union all 用这个 应该就不会排序，但是sql1和sql2会有重复的项，需要给sql2加where条件sql2.主键 not in sql1.主键

[老黎]

楼主可以试一下这种写法

select top 10000000000 * from table1 order by id,...
union all
select top 10000000000 * from table2 order by id,...

引用楼主 che2piaopiao 的回复:

select * from productt where fuid in (select id from classtp where jibie <>1 and id <>144) order by fuid,sortid desc  union 
select * from productt where fuid in(
select id from classtp where  id not in (select distinct fuid from classtp where jibie=0) and jibie=1 and id <>144) order by fuid,sortid desc

SQL1 union SQL2

想要的结果： sql1的数据 sql2的数据 但出现的结果却是。。 SQL1+SQL2 order by fuid，，，重新排序了。

求救了。 顺祝各位天天开心!

[老黎]

--不好意思，应该是这样
select * from (select top 10000000000 * from table1 order by id,...) a
union all
select * from (select top 10000000000 * from table2 order by id,...) b

引用 17 楼 kerafan 的回复:

楼主可以试一下这种写法
SQL codeselecttop10000000000*from table1orderby id,...unionallselecttop10000000000*from table2orderby id,...引用楼主 che2piaopiao 的回复:select * from productt where fuid in (select id from classtp where jibie <>1 and id <>144) order by fuid,sortid desc  union  select * from productt where fuid in( select id from classtp where  id not in (select distinct fuid from classtp where jibie=0) and jibie=1 and id <>144) order by fuid,sortid desc SQL1 union SQL2 想要的结果： sql1的数据 sql2的数据 但出现的结果却是。。 SQL1+SQL2 order by fuid，，，重新排序了。 求救了。 顺祝各位天天开心!

[cxmcxm]

select * from 
(select 1 as f1,* from productt where fuid in (select id from classtp where jibie <>1 and id <>144) 
union  
select 2,* from productt where fuid in( 
select id from classtp 
where  id not in (select distinct fuid from classtp where jibie=0) and jibie=1 and id <>144) 
) as a
order by f1,fuid,sortid desc 

[ChinaJiaBing]

union和union all 关键字都是将两个结果集合合并为一个，但这两者从使用和效率上来说都有所不同。 

UNION在进行表链接后会筛选掉重复的记录，所以在表链接后会对所产生的结果集进行排序运算，删除重复的记录再返回结果。实际大部分应用中是不会产生重复的记录，最常见的是过程表与历史表UNION。如： 

select * from 表
union 
select * from 表 

[Sweet-Tang]

select * from 
( 
select *,1 as tbidx from SQL1 
union all 
select *,2 as tbidx from SQL2 
) tt order by tbidx,fuid 

select * from (select * from table1 order by id) a
union all
select * from (select * from table2 order by id) b