使用另一个表值获取以前的数据 [英] Fetching previous Datas by using another table value

问题描述

我要在一个表中获取数据，它包含current_month值为2013.007。在另一个表中保存此currentmon的timeid。现在我想选择2013.007到6之前的时间ID，这意味着（2012.001 t0 2013.007）。在mbrVersion表中只包含一个当前月份。在时间表中只包含7data的ID如何获得此ID？



这里我的代码仅为2013.007其示例值。我想绑定值？

  SELECT  SUM（SIGNEDDATA）
  FROM  FACPLAN 
  WHERE  TIMEID  IN  
（ SELECT  TIMEID  FROM  < span class =code-keyword>时间 
  WHERE  ID  IN  
（ SELECT  CURRENT_MONTH  FROM  mbrVERSION  WHERE  CURRENT_MONTH！= ' '））

解决方案

  DECLARE   @ CURRENT_MONTH  < span class =code-keyword> VARCHAR （ 20 ）
  DECLARE   @ YEAR   CHAR （ 4 ）
  DECLARE   @ PERIOD   NUMERIC （ 3 ）
 
  SET  @ CURRENT_MONTH =（  SELECT  CURRENT_MOTH  FROM  dbo.VERSION  WHERE  CURRENT_MONTH ！= ' '）
  PRINT   @ CURRENT_MONTH  
  SET  @YEAR =（ select  substring（ @ CURRENT_MONTH ， 0 ， 5 ））
  SET  @PERIOD =（ SELECT  SUBSTRING（ @ CURRENT_MONTH ， 6 ， 9 ））
 
 
  SELECT  SUM（SIGNEDDATA） FROM  dbo.PLANNING 
  WHERE  
 TIMEID  IN  
（ SELECT  TIMEID  FROM  dbo.mbrTime 
  WHERE  
 ID  BETWEEN  
 @ YEAR + ' < span class =code-string>。' +（ SELECT   RIGHT （'  000' + CAST（ @ PERIOD-6   AS   varchar ）， 3 ） ）
  AND  
  @ YEAR  + ' 。' +（ SELECT   RIGHT （'  000' + CAST（ @ PERIOD   AS   varchar ）， 3 ）））

Hi,
i am going to fetch data in one table it contains the current_month values as 2013.007. In another table hold the timeid for this currentmonth. now i want to choose the time id for 2013.007 to before 6 value that means(2012.001 t0 2013.007). In mbrVersion table only contains the one curretn month. in Time table only contain the id for 7data how can i get this id?

here i have the code for only 2013.007 its example value only. i want to bind the value?

SELECT SUM(SIGNEDDATA)
FROM FACPLAN
WHERE TIMEID IN
(SELECT TIMEID FROM Time
WHERE ID IN
(SELECT CURRENT_MONTH FROM mbrVERSION WHERE CURRENT_MONTH!=''))

解决方案

DECLARE @CURRENT_MONTH VARCHAR(20)
  DECLARE @YEAR CHAR(4)
  DECLARE @PERIOD NUMERIC(3)

SET @CURRENT_MONTH= (SELECT CURRENT_MOTH FROM dbo.VERSION WHERE CURRENT_MONTH!='')
  PRINT @CURRENT_MONTH
 SET @YEAR=(select substring(@CURRENT_MONTH, 0, 5))
 SET @PERIOD=(SELECT SUBSTRING(@CURRENT_MONTH,6,9))


  SELECT SUM(SIGNEDDATA) FROM dbo.PLANNING
  WHERE
TIMEID IN
  ( SELECT TIMEID FROM dbo.mbrTime
   WHERE
   ID BETWEEN
   @YEAR+'.'+(SELECT RIGHT ('000'+ CAST (@PERIOD-6 AS varchar), 3))
   AND
    @YEAR +'.'+(SELECT RIGHT ('000'+ CAST (@PERIOD AS varchar), 3)))

这篇关于使用另一个表值获取以前的数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！