select * from tbl1
union all
select * from tbl2
==========================
我这样能输出正常的结果
但是
select *
from(
select * from tbl1
union all
select * from tbl2
) _x
where t>datetime1 and t<datetime2
就报错,
服务器: 消息 8155,级别 16,状态 2,行 1
没有为第 3 列(属于 '_x')指定列。
请问是什么原因,谢谢
from(
select ID,ProNum2,timin,'进馆日期' as a1,bzdw,CONVERT(char(10), FileStarDate, 120)+ '到' + CONVERT(char(10), FileEndDate, 120) as a,sldw,bgqx,ClassiFication,Storedd from rollreg
union all
select ID,ProNum2,timin,'进馆日期' as a1,bzdw,CONVERT(char(10), FileStarDate, 120)+ '到' + CONVERT(char(10), FileEndDate, 120) as a,sldw,bgqx,ClassiFication,Storedd from rollreg_B
union all
select ID,ProNum2,timin,'进馆日期' as a1,bzdw,CONVERT(char(10), FileStarDate, 120)+ '到' + CONVERT(char(10), FileEndDate, 120) as a,sldw,bgqx,ClassiFication,Storedd from rollreg_C
union all
select ID,ProNum2,timin,'进馆日期' as a1,bzdw,CONVERT(char(10), FileStarDate, 120)+ '到' + CONVERT(char(10), FileEndDate, 120) as a,sldw,bgqx,ClassiFication,Storedd from rollreg_D
union all
select ID,ProNum2,timin,'进馆日期' as a1,bzdw,CONVERT(char(10), FileStarDate, 120)+ '到' + CONVERT(char(10), FileEndDate, 120) as a,sldw,bgqx,ClassiFication,Storedd from rollreg_E
) A
where ProNum2 LIKE '%L%'输出
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
1 L-2-1-44444-1 CCCC-2-7777 进馆日期 2007-03-22 0 2-4
1 L-2-1-43434-1 DDD-34-3434433434 进馆日期 2007-03-22 0 2-3
.
.
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++我想这样输出,就是让ID自动重新计算,如何搞呢
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
1 L-2-1-44444-1 CCCC-2-7777 进馆日期 2007-03-22 0 2-4
2 L-2-1-43434-1 DDD-34-3434433434 进馆日期 2007-03-22 0 2-3
3
4
.
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
只有借助臨時表了eg:Select
ID = Identity(Int, 1, 1),
*
Into #T
From
(
...
) A
Select * From #TDrop Table #Tselect ID,ProNum2,...
就去掉ID列
select ProNum2,...
from(
select id,a,b,c from tbl1
union all
select id,a,b,c from tbl2
) _x
where a like '%xxx%'输出
_________________________
1,001 ,002 ,003
8,0001 ,0002 ,00003现在要这样输出,就是在查询的时候让重新计算ID,
_________________________1,001 ,002 ,003
2,0001 ,0002 ,00003
_________________________