我利用子查询求得分类物质的浓度占总类物质浓度的百分比,此语句在SQL Server 分析器中已经调通,但在Delphi 环境中,语句不出错,但没有查询结果。应该是有结果的?为什么?请各位高手帮忙看看。
strSQL:='SELECT Cast(ProjectID as NVARCHAR(15)) as 项目名称, CONVERT(char(10),TbVocConc.date, 111) AS 日期, '+
'Cast(tbVocsType.ChineseName as NVARCHAR(15)) as 中文名称,Cast(tbVocsType.EnglishName as NVARCHAR(20)) as 英文名称,'+
'str(sum(Concentration),10,2) as 浓度,str((sum(Concentration)/(select SUM(Concentration) from TbVocConc where ProjectID=:ID '+
'and CONVERT(char(10), Date, 111)=:Dt_F and Concentration<>-999))*100,5,2) as 百分比 '+
'from tbVocsType,TbVocConc '+
'where tbVocsType.VocsTypeID=SUBSTRING(TbVocConc.CompoundID, 1, 2) '+
'and ProjectID=:ID '+
'and CONVERT(char(10), TbVocConc.Date, 111)>=:Dt_F '+
'and CONVERT(char(10), TbVocConc.Date, 111)<=:Dt_T '+
'and Concentration<>-999 '+
'Group by ProjectID,SUBSTRING(TbVocConc.CompoundID, 1, 2),CONVERT(char(10),TbVocConc.Date, 111), '+
'Cast(tbVocsType.ChineseName as NVARCHAR(15)),Cast(tbVocsType.EnglishName as NVARCHAR(20))';
strSQL:='SELECT Cast(ProjectID as NVARCHAR(15)) as 项目名称, CONVERT(char(10),TbVocConc.date, 111) AS 日期, '+
'Cast(tbVocsType.ChineseName as NVARCHAR(15)) as 中文名称,Cast(tbVocsType.EnglishName as NVARCHAR(20)) as 英文名称,'+
'str(sum(Concentration),10,2) as 浓度,str((sum(Concentration)/(select SUM(Concentration) from TbVocConc where ProjectID=:ID '+
'and CONVERT(char(10), Date, 111)=:Dt_F and Concentration<>-999))*100,5,2) as 百分比 '+
'from tbVocsType,TbVocConc '+
'where tbVocsType.VocsTypeID=SUBSTRING(TbVocConc.CompoundID, 1, 2) '+
'and ProjectID=:ID '+
'and CONVERT(char(10), TbVocConc.Date, 111)>=:Dt_F '+
'and CONVERT(char(10), TbVocConc.Date, 111)<=:Dt_T '+
'and Concentration<>-999 '+
'Group by ProjectID,SUBSTRING(TbVocConc.CompoundID, 1, 2),CONVERT(char(10),TbVocConc.Date, 111), '+
'Cast(tbVocsType.ChineseName as NVARCHAR(15)),Cast(tbVocsType.EnglishName as NVARCHAR(20))';
用CONVERT(char(10), TbVocConc.Date, 111)>=:Dt_F 截取时间的话不能将本地时间格式设置为 tt h:mm:ss 或 t h:mm:ss 等时间格式。