有点晕的SQL语句

一个表(PW_二氧化硫收费标准),数据类型如下:



表中数据如下:

 1         1900-1-1          2004-7-1          .2
 2         2004-7-1          2005-7-1          .4
 3         2005-7-1          2008-11-1        .6

表中数据代表不同时期内二氧化硫不同的收费标准

要求根据输入的年月(格式如"200501")查询出当前年月的二氧化硫收费标准

SQL语句如下:

                            SELECT 
                                收费标准
                            
FROM 
                                (
                                    
SELECT 
                                        
CAST
                                            (
                                                
CAST(YEAR(开始时间) AS CHAR(4)) + 
                                                    (    
                                                        
CASE 
                                                            
LEN(CAST(MONTH(开始时间) AS VARCHAR(2))) 
                                                        
WHEN 
                                                            
1 
                                                        
THEN 
                                                            
'0' + CAST(MONTH(开始时间) AS VARCHAR(2)) 
                                                        
ELSE 
                                                            
CAST(MONTH(开始时间) AS VARCHAR(2)) 
                                                        
END
                                                    ) 
                                                
AS INT
                                            ) 
                                        
AS 
                                        开始时间年和月份, 
                                        
CAST
                                            (
                                                
CAST(YEAR(结束时间) AS CHAR(4)) + 
                                                    (
                                                        
CASE 
                                                            
LEN(CAST(MONTH(结束时间) AS VARCHAR(2))) 
                                                        
WHEN 
                                                            
1 
                                                        
THEN
                                                            
'0' + CAST(MONTH(结束时间) AS VARCHAR(2)) 
                                                        
ELSE 
                                                            
CAST(MONTH(结束时间) AS VARCHAR(2)) 
                                                        
END
                                                    ) 
                                                
AS INT
                                            ) 
                                        
AS 
                                        结束时间年和月份,
                                        收费标准
                                
FROM 
                                    
[PW_二氧化硫收费标准]
                            )
                             二氧化硫收费标准转换
                        
WHERE 
                                (二氧化硫收费标准转换.开始时间年和月份 
<= '200501'
                            
AND 
                                (二氧化硫收费标准转换.结束时间年和月份 
> '200501')


其实设计数据库的时候把表结构和其中的数据考虑周全点,就不用这么麻烦啦,呵呵……

posted @ 2005-04-04 23:19  蜡人张  阅读(713)  评论(2)    收藏  举报