t-sql查询二个表数据,sql2000

t-sql查询2个表数据,sql2000
t-sql查询2个表数据,a 表字段 id， name，time b 表 id， name，time，使用 sql2000
要求，输入时间如 05/05/2011 查询 a 表有则显示数据，没有则从b表查询，有则补上，b表很大，几百万数据，a表小，其实还有许多表来join a，b，我做了简化，如何写sql 高效些，谢谢！
举例：输入 05/05/2011

a:
id name time
1 zhao 05/06/2011
2 zhao 05/07/2011

b:
id name time
1 zhao1 05/05/2011
2 zhao1 05/07/2011

显示

zhao1

[解决办法]
创建视图

SQL code

create view a_and_b as select * from aunion select * from bgo
[解决办法]
CREATE  table #A (ID INT,NAME VARCHAR(10),[TIME] SMALLDATETIME)
insert #A  
select 1 ,'zhao', '05/06/2011' UNION ALL
SELECT 2 ,'zhao' ,'05/07/2011'

CREATE table #B (ID INT,NAME VARCHAR(10),[TIME] SMALLDATETIME) 
insert #B  
select 1,'zhao1', '05/05/2011' UNION ALL
SELECT 2,'zhao1', '05/07/2011'

DECLARE @CDATE  SMALLDATETIME
  SET @CDATE='05/05/2011'
  IF EXISTS (SELECT * FROM #A WHERE [TIME]=@CDATE)
(SELECT * FROM #A WHERE [TIME]=@CDATE)
ELSE
(SELECT * FROM #B WHERE [TIME]=@CDATE )
  



ID          NAME       TIME
----------- ---------- -----------------------
1           zhao1      2011-05-05 00:00:00

(1 行受影响)
[解决办法]
SQL codeSELECT * FROM A WHERE TIME = '05/05/2011'IF @@ROWCOUNT = 0     SELECT * FROM B WHERE TIEM = '05/05/2011'

t-sql查询二个表数据,sql2000

热点推荐