首页 \ 问答 \ SQL Server 2005查询DateDate(SQL Server 2005 Query DateDate)

SQL Server 2005查询DateDate(SQL Server 2005 Query DateDate)

WHERE
   StartDate BETWEEN 
       CAST(FLOOR(CAST(DATEADD("month", - 12, ISNULL(RunDate.testdate, GETDATE()) - DAY(ISNULL(RunDate.testdate, GETDATE())) + 1) AS FLOAT)) AS DATETIME)
       AND 
       CAST(FLOOR(CAST(ISNULL(RunDate.testdate, GETDATE()) - DAY(ISNULL(RunDate.testdate, GETDATE())) + 1 AS FLOAT)) AS DATETIME)

/************************************************* 
SQL Server 2005 (9.0 SP4)
StartDate is of TYPE DATETIME
StartDate date looks like 2012-07-05 12:45:10.227
RunDate is a TABLE with no records
RunDate.testdate is always NULL
**************************************************/
 
 这个WHERE子句可以运行并产生业务预期的预期结果，但似乎过于凌乱。  
 所有这一切都试图实现的是过去12个月的所有记录，在任何一年的7月份运行。 所以2013年7月1日至2014年6月30日的记录。 

WHERE
   StartDate BETWEEN 
       CAST(FLOOR(CAST(DATEADD("month", - 12, ISNULL(RunDate.testdate, GETDATE()) - DAY(ISNULL(RunDate.testdate, GETDATE())) + 1) AS FLOAT)) AS DATETIME)
       AND 
       CAST(FLOOR(CAST(ISNULL(RunDate.testdate, GETDATE()) - DAY(ISNULL(RunDate.testdate, GETDATE())) + 1 AS FLOAT)) AS DATETIME)

/************************************************* 
SQL Server 2005 (9.0 SP4)
StartDate is of TYPE DATETIME
StartDate date looks like 2012-07-05 12:45:10.227
RunDate is a TABLE with no records
RunDate.testdate is always NULL
**************************************************/
 
This WHERE clause works and produces the expected results for what the business expects but seems excessively untidy. 
All that this is attempting to achieve is to bring across all records for the last 12 months, when run in July of any year. So records from 1 July 2013 to 30 June 2014.

原文：https://stackoverflow.com/questions/24644234

更新时间：2022-02-21 12:02

最满意答案

 你可以这样做：  
select . . .
from t left join
     division d
     on t.id = concat('D', d.id) left join
     lab l
     on t.id = concat('L', l.id);

How I solved it. 
SELECT * FROM user 

LEFT JOIN general_group 
    ON general_group.id = user.id 
LEFT JOIN `div` 
    ON `div`.id = substr(general_group.where_id,2)

WHERE general_group.where_id LIKE 'D%'

UNION

SELECT * FROM user 

LEFT JOIN general_group 
    ON general_group.id = user.id 
LEFT JOIN `lab` 
    ON `lab`.id = substr(general_group.where_id,2)

WHERE general_group.where_id LIKE 'L%';

SQL Server 2005查询DateDate(SQL Server 2005 Query DateDate)

最满意答案

相关问答

有什么数据库专业书籍介绍？[2022-05-19]

独立地滚动矩阵的行(Roll rows of a matrix independently)[2024-03-05]

使用MySQL LEFT JOIN删除行(Deleting rows with MySQL LEFT JOIN)[2022-07-31]

使用MySQL Join的问题(Problem using MySQL Join)[2022-08-07]

具有唯一结果行的MYSQL JOIN(MYSQL JOIN with unique result rows)[2024-01-21]

mysql - LEFT JOIN不返回所有行(mysql - LEFT JOIN not returning all rows)[2022-05-27]

MySQL INNER JOIN返回所有行(MySQL INNER JOIN returning all rows)[2022-03-03]

MySQL JOIN获取非现有行(MySQL JOIN get non existing rows)[2021-12-21]

是否可以独立加入MySQL行(Is it possible to join MySQL rows independently)[2023-06-28]

MYSQL，JOIN，AS，LIKE(MYSQL, JOIN, AS, LIKE)[2021-07-09]

相关文章

最新问答