求教，下面的语句都是查询一个均价的，现在我想把他们合并一条语句，目的是只查询最近时间的一个价格，因为变的都是一个时间，比如如果3月份的结果为NULL则显示2月份的结果，如果3月份的结果为null则显示1月份的，以此类推。如何实现合并一条语句？或者用分组怎么实现之 求指教？
select AVG(Price) from A WHERE
name = 1 and num = 2 and Y = 3 Date >= '2014-03-1' and Date <=GETDATE()
select AVG(Price) from A WHERE
name = 1 and num = 2 and Y = 3 Date >= '2014-02-1' and Date <=GETDATE()
select AVG(Price) from A WHERE
name = 1 and num = 2 and Y = 3 Date >= '2014-01-1' and Date <=GETDATE()
select AVG(Price) from A WHERE
name = 1 and num = 2 and Y = 3 Date >= '2013-12-1' and Date <=GETDATE()
------解决方案--------------------
这样？
SELECT AVG(Price)
FROM A
WHERE name = 1
AND num = 2
AND Y = 3
AND ( Date >= '2014-03-1'
AND Date <= GETDATE()
)
OR ( Date >= '2014-02-1'
AND Date <= GETDATE()
)
OR ( Date >= '2014-01-1'
AND Date <= GETDATE()
)
OR ( Date >= '2013-12-1'
AND Date <= GETDATE()
)
------解决方案--------------------
select * from A where
Date between Convert(varchar(7),(select Max(Date) from A),23)+'-01' and getdate()
and name = 1 and num = 2 and Y = 3
------解决方案--------------------
--用union all 连接
select AVG(Price) avg_price from A
WHERE name = 1 and num = 2 and Y = 3 Date >= '2014-03-1' and Date <=GETDATE()
union all
select AVG(Price) from A
WHERE name = 1 and num = 2 and Y = 3 Date >= '2014-02-1' and Date <=GETDATE()
union all
select AVG(Price) from A
WHERE name = 1 and num = 2 and Y = 3 Date >= '2014-01-1' and Date <=GETDATE()
union all
select AVG(Price) from A
WHERE name = 1 and num = 2 and Y = 3 Date >= '2013-12-1' and Date <=GETDATE()
------解决方案--------------------
declare @begintime varchar(20)
declare @sql varchar(max)
select @begintime= CONVERT(varchar(7),MAX([Date]),121)+'-01 00:00:00' from A
set @sql = 'select AVG(Price) from A WHERE
name = 1 and num = 2 and Y = 3 [Date] >= '+@begintime+' and [Date] <=GETDATE() '
exec (@sql)
------解决方案--------------------
;with
wang as
(
select dd=convert(char(6),date,112),price=AVG(Price) avg_price from A
WHERE Date <=GETDATE()
group by convert(char(6),date,112)
)
select dd,price=isnull(price,select top1 price from wang where dd<t.dd order by dd desc)
from wang t
------解决方案--------------------
select top 1 AVG(Price)price,convert(varchar(8),Date,120)+'01''count_date
from A
WHERE name = 1 and num = 2 and Y = 3 Date >= '2013-12-1' and Date <=GETDATE()
group by convert(varchar(8),Date,120)+'01''