select * from ( SELECT 0 AS typ, a,b,c FROM BB1 where a=:X and b=:YUNION ALLSELECT 0 AS typ, a,b,c FROM BB2 where a=:X and b=:YUNION ALLSELECT 1 AS typ, a,b,c FROM BB3 where a=:Xand b=:Y )tem ORDER BY a,b,typ,c
最好建立一个视图 create view view1 as SELECT 0 AS typ, a,b,c FROM BB1 UNION ALLSELECT 0 AS typ, a,b,c FROM BB2UNION ALLSELECT 1 AS typ, a,b,c FROM BB3 使用的时候 select * from view1 order by a,b,typ,c 当然这个是在你数据不大情况下使用的。如果你的数据量很大。建议你建议个参数视图。呵呵~~~
SELECT 0 AS typ, a,b,c FROM BB1 where a=:X and b=:Y ORDER BY a,b,typ,cUNION ALLSELECT 0 AS typ, a,b,c FROM BB2 where a=:X and b=:YUNION ALLSELECT 1 AS typ, a,b,c FROM BB3 where a=:Xand b=:Y
把Union去掉(如果这三个记录集没有重复),这样会快很多 SELECT 0 AS typ, a,b,c FROM BB1 where a=:X and b=:YUNION SELECT 0 AS typ, a,b,c FROM BB2 where a=:X and b=:YUNION SELECT 1 AS typ, a,b,c FROM BB3 where a=:Xand b=:YORDER BY a,b,typ,c
SELECT 0 AS typ, a,b,c
FROM BB1 where a=:X and b=:YUNION ALLSELECT 0 AS typ, a,b,c
FROM BB2 where a=:X and b=:YUNION ALLSELECT 1 AS typ, a,b,c
FROM BB3 where a=:Xand b=:Y
)tem
ORDER BY a,b,typ,c
create view view1
as
SELECT 0 AS typ, a,b,c
FROM BB1 UNION ALLSELECT 0 AS typ, a,b,c
FROM BB2UNION ALLSELECT 1 AS typ, a,b,c
FROM BB3
使用的时候
select * from view1 order by a,b,typ,c
当然这个是在你数据不大情况下使用的。如果你的数据量很大。建议你建议个参数视图。呵呵~~~
FROM BB1 where a=:X and b=:Y
ORDER BY a,b,typ,cUNION ALLSELECT 0 AS typ, a,b,c
FROM BB2 where a=:X and b=:YUNION ALLSELECT 1 AS typ, a,b,c
FROM BB3 where a=:Xand b=:Y
SELECT 0 AS typ, a,b,c
FROM BB1 where a=:X and b=:YUNION SELECT 0 AS typ, a,b,c
FROM BB2 where a=:X and b=:YUNION SELECT 1 AS typ, a,b,c
FROM BB3 where a=:Xand b=:YORDER BY a,b,typ,c