正在生效:
select id from table where find_in_set(month(curdate()),allowmonth) and unix_timestamp(curdate()) between unix_timestamp(sdate) and unix_timestamp(edate);
即将生效:
select id from table where unix_timestamp(curdate()) between unix_timestamp(sdate) and time_stamp(edate);
已经过期:
select id from table where unix_timestamp(curdate()) > unix_timestamp(edate);这个解决办法.还差一些...字段有些调整...
select id from table where find_in_set(month(curdate()),allowmonth) and unix_timestamp(curdate()) between unix_timestamp(sdate) and unix_timestamp(edate);
即将生效:
select id from table where unix_timestamp(curdate()) between unix_timestamp(sdate) and time_stamp(edate);
已经过期:
select id from table where unix_timestamp(curdate()) > unix_timestamp(edate);这个解决办法.还差一些...字段有些调整...
(CASE ( SELECT 999 FROM Sys_Stock WHERE ID=A.ID AND (MONTH(GETDATE()) BETWEEN MONTH(A.sdate) AND MONTH(B.edate)) AND MONTH(GETDATE()) IN (A.allowmonth) )
WHEN 999 THEN '有效'
Else '过期'
END)AS 标记
FROM 表 AS A老虎不发威你当我是病猫,这语句估计要牛人优化一下,我暂时没考虑优化,只考虑结果你查一次看看是不是你要的结果,这次有效和过期一起查!SQL Server 2005 测试通过
sql语句能否在1,2,3,4中找到最大值4?
你现在的需求是如果edate年份为本年,如果当前月份大于disp_month中最大的月份,则过期。否则为即将生效?
insert T select 1, '2006-12-12', '2008-12-12', '1,2,3,4,10,12'
union all select 2, '2006-11-12', '2007-12-12', '1,9'
union all select 3, '2006-1-12', '2006-12-12', '1,2,9'
union all select 4, '2006-1-12', '2005-12-12', '1'
union all select 5, '2006-12-12', '2008-12-12', '10,12'
union all select 6, '2006-12-12', '2007-12-12', '1,2,'
(CASE ( SELECT '有效' FROM Sys_Stock AS TEMP WHERE TEMP.ID=Sys_Stock.ID AND (GETDATE() BETWEEN TEMP.InsertTime AND TEMP.UpdateTime) AND MONTH(GETDATE()) IN (1,2,3) )
WHEN '有效' THEN ( CASE ( SELECT '临近过期' FROM Sys_Stock AS TEMP1 WHERE TEMP1.ID=Sys_Stock.ID AND MONTH( TEMP1.UpdateTime )=MONTH( GETDATE() ) )
WHEN '临近过期' THEN '临近过期'
ELSE '有效'
End )
Else ( CASE ( SELECT '即将生效' FROM Sys_Stock AS TEMP2 WHERE TEMP2.ID=Sys_Stock.ID AND MONTH( TEMP2.InsertTime )=MONTH( GETDATE() ) )
WHEN '即将生效' THEN '即将生效'
ELSE '过期'
End )
END)AS 标记
FROM Sys_Stock