在SQL查询中需要帮助 [英] need help in a sql query

问题描述

先生，我有一个sql表，其中3列及其数据显示如下： -





EmpID - FromDate - - ToDate

101 - '01 / AUg / 2015' - '03 / Aug / 2015'

101 - '05 / AUg / 2015' - ' 06 / AUg / 2015'





现在我需要我的SQL查询来显示该表中的数据，如： -



EmpID - 日期

101 - '01 / AUg / 2015'

101 - '02 / AUg / 2015'

101 - '03 / AUg / 2015'

101 - '05 / AUg / 2015'

101 - ' 06 / AUg / 2015'

我需要  获取 日期来自'  01 / Aug / 2015'至'03 / Aug / 2015'。如果fromdate是'01 / AUg / 2015'并且Todate是'05 / AUg / 2015'那么我需要得到日期 
 
 01 / AUg / 2015 
 02 / AUg / 2015 
 03 / AUg / 2015 
 04 / AUg / 2015 
 05 / AUg / 2015 

我该怎么做..

解决方案

   -   您可以使用Common Tabl表达式 
 
 
  DECLARE   @ STARTDATE   VARCHAR （ 50 ）
  SET  @STARTDATE = '  2015-08-01' 
 
  DECLARE   @ ENDDATE   VARCHAR （ 50 ）
  SET  @ ENDDATE = '  2015-08-06' 
 
 
 
;  WITH  DATERANGE  AS  
（
  SELECT  DT = DATEADD（DD， 0 ， @ STARTDATE ）
  WHERE  DATEADD （DD， 1 ， @ STARTDATE ）< =  @ ENDDATE  
  UNION   ALL  
  SELECT  DATEADD（DD， 1 ，DT）
  FROM  DATERANGE 
  WHERE  DATEADD（DD， 1 ，DT）< =  @ ENDDATE  
）
  SELECT  *  FROM  DATERANG E 

更改您的数据库。



切勿将日期值存储为字符串：它使其变得简单您可以设置和插入，但每当您尝试使用数据时，它总会产生这样的问题。将它存储在DATE或DATETIME列中，这变得微不足道：

  SELECT  *  FROM  MyTable  ORDER   BY  Dated 

使用VARCHAR或NVARCHAR存储，这很可怕，因为默认比较是逐个字符的，并且按正确日期排序意味着首先将字符串转换为DATE（并处理不可避免的用户错误）或拆分字符串并且通过许多片段进行排序。



说真的，修复数据库设计错误似乎需要做很多工作，但是长期工作要少得多术语

Sir, i have a sql table in which 3 column and its data show like:-


EmpID -- FromDate -- ToDate
101 -- '01/AUg/2015' -- '03/Aug/2015'
101 -- '05/AUg/2015' -- '06/AUg/2015'


Now i need i sql query to show the data from that table like :-

EmpID -- Dated
101 -- '01/AUg/2015'
101 -- '02/AUg/2015'
101 -- '03/AUg/2015'
101 -- '05/AUg/2015'
101 -- '06/AUg/2015'

i need to get date from '01/Aug/2015' to '03/Aug/2015' . if fromdate is '01/AUg/2015' and Todate is '05/AUg/2015' then i need to get date like

01/AUg/2015
02/AUg/2015
03/AUg/2015
04/AUg/2015
05/AUg/2015

how can i do it..

解决方案

--you may use Common Tabl expression


DECLARE @STARTDATE VARCHAR(50)
SET @STARTDATE='2015-08-01'

DECLARE @ENDDATE VARCHAR(50)
SET @ENDDATE='2015-08-06'



;WITH DATERANGE AS
(
   SELECT DT =DATEADD(DD,0, @STARTDATE)
   WHERE DATEADD(DD, 1, @STARTDATE) <= @ENDDATE
   UNION ALL
   SELECT DATEADD(DD, 1, DT)
   FROM DATERANGE
   WHERE DATEADD(DD, 1, DT) <= @ENDDATE
)
SELECT  * FROM DATERANGE

Change your DB.

Never store date values as strings: it makes it easy for you to set up and insert, but it always creates problems like this whenever you try to use the data. Store it in a DATE or DATETIME column instead, and this becomes trivial:

SELECT * FROM MyTable ORDER BY Dated

With VARCHAR or NVARCHAR storage, that is horrible, because the default comparison is character-by-character and to order by "proper" date means converting the string to a DATE first (and dealing with the inevitable user errors) or splitting the string and ordering by a number of fragments.

Seriously, it may seem like a lot of work to fix the DB design errors, but it's a lot less work in the long term.

这篇关于在SQL查询中需要帮助的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！