问题描述

sql子查询中列别名在主查询中无效

CREATE PROCEDURE dbo.query_test(
@i_begin_time varchar(128) IN, /* 开始时间 /
@i_end_time varchar(128) IN, / 结束时间 /
@i_bussiness_tpe varchar(512) IN, / 类型 )
as
declare @v_sql varchar(16384)/* 定义 需要执行的sql /
declare @Rtime varchar(128)/ 定义 记录时间*/
declare @Atime varchar(128)/* 定义 受理时间 /
declare @Ttime varchar(128)/ 定义 派单时间*/
declare @Wtime varchar(128)//定义日期格式，转化将00000000-》00-00-0000:00:00
set @Rtime = 'SUBSTRING(RECORD_TIME, 1, 10)'
set @Atime = 'SUBSTRING(ACCEPT_TIME, 1, 10)'
set @Ttime = 'SUBSTRING(TURN_TIME, 1, 10)'
set @Wtime=' substring(CREATE_TIME,1,4)+''-''+substring(CREATE_TIME,5,2)+''-''+substring(CREATE_TIME,7,2)+'' ''+substring(CREATE_TIME,9,2)+'':''+substring(CREATE_TIME,11,2)+'':''+substring(CREATE_TIME,13,2)'
select RECORD_TIME,count(*) index from (

select '||@Rtime||' RECORD_TIME , COUNT(*) index1 from (select '||@Wtime||'RECORD_TIME,* from GISINFO)
where RECORD_TIME >= '''|| @i_begin_time ||''' and RECORD_TIME < '''|| @i_end_time ||''' AND KF_ID in (select id from COMPNAMELIST)
GROUP BY '||@Rtime||'')base GROUP BY RECORD_TIME ORDER BY RECORD_TIME '
end ...
在执行存储过程的时候，报错invalid column RECORD_TIME ，子查询select '||@Wtime||'RECORD_TIME,* from GISINFO 中列别名RECORD_TIME 无效