不能那样引用别名的: select tab1.* from ( select 1 as a,2 as b )tab1 union select tab2.* from ( select 1 as a,3 as b )tab2 where tab2.a not in (select a from (select 1 as a,2 as b)tab1)
select a,b=min(b) from (select 1 as a,2 as b union select 1 ,3)tb group by a
--再潜套一层 select tab1.* from (select 1 as a, 2 as b) tab1 union select tab2.* from (select 1 as a, 3 as b) tab2 where tab2.a not in (select a from (select 1 as a, 2 as b) tab1)
建个临时表 select iid,col into # from (select 1 as iid,2 as col)tab1 再用union select iid,col from (select 1 as iid,2 as col)tab1 union select tab2.iid,tab2.col from (select 1 as iid,3 as col)tab2,# where (#.col>tab2.col) drop table #
select tab1.* from
(
select 1 as a,2 as b
)tab1
union
select tab2.* from
(
select 1 as a,3 as b
)tab2 where tab2.a not in (select a from (select 1 as a,2 as b)tab1)
select a,b=min(b) from
(select 1 as a,2 as b
union
select 1 ,3)tb group by a
select tab1.*
from (select 1 as a,
2 as b) tab1
union
select tab2.*
from (select 1 as a,
3 as b) tab2
where tab2.a not in (select a
from (select 1 as a,
2 as b) tab1)
用min(b)也不好,因为我没有一个可以字段可以用来判断的,
谢再指教!
select iid,col into # from (select 1 as iid,2 as col)tab1
再用union
select iid,col from (select 1 as iid,2 as col)tab1
union
select tab2.iid,tab2.col from
(select 1 as iid,3 as col)tab2,#
where (#.col>tab2.col)
drop table #