这样的查询结果，应该怎样写sql语句

[strayatman]

买书日期 买书数
2001/1/1 4
2001/4/1 5
我想查某年度的各个月的买书数量，作个统计图‘意思是只能有12个查询记录
应该怎样写sql?????

[twht]

select month(买书日期) as 买书月份， count(买书数) 买书数 from yourtable where year(买书日期) = 2000 group by month(买书日期)

如果不条语句不用GROUP BY可以运行吗？？

[蓝天]

select 1 as 买书月份， isnull(sum(买书数),0) as 买书数 from yourtable where year(买书日期) = 2000 and month(买书日期)=1
union
select 2 as 买书月份， isnull(sum(买书数),0) as 买书数 from yourtable where year(买书日期) = 2000 and month(买书日期)=2
union
select 3 as 买书月份， isnull(sum(买书数),0) as 买书数 from yourtable where year(买书日期) = 2000 and month(买书日期)=3
union
select 4 as 买书月份， isnull(sum(买书数),0) as 买书数 from yourtable where year(买书日期) = 2000 and month(买书日期)=4

.......

[蓝天]

2000年的这么写：

select 1 as 买书月份， isnull(sum(买书数),0) as 买书数 from yourtable where year(买书日期) = 2000 and month(买书日期)=1
union
select 2 as 买书月份， isnull(sum(买书数),0) as 买书数 from yourtable where year(买书日期) = 2000 and month(买书日期)=2
union
select 3 as 买书月份， isnull(sum(买书数),0) as 买书数 from yourtable where year(买书日期) = 2000 and month(买书日期)=3
union
select 4 as 买书月份， isnull(sum(买书数),0) as 买书数 from yourtable where year(买书日期) = 2000 and month(买书日期)=4
union
select 5 as 买书月份， isnull(sum(买书数),0) as 买书数 from yourtable where year(买书日期) = 2000 and month(买书日期)=5
union
select 6 as 买书月份， isnull(sum(买书数),0) as 买书数 from yourtable where year(买书日期) = 2000 and month(买书日期)=6
union
select 7 as 买书月份， isnull(sum(买书数),0) as 买书数 from yourtable where year(买书日期) = 2000 and month(买书日期)=7
union
select 8 as 买书月份， isnull(sum(买书数),0) as 买书数 from yourtable where year(买书日期) = 2000 and month(买书日期)=8
union
select 9 as 买书月份， isnull(sum(买书数),0) as 买书数 from yourtable where year(买书日期) = 2000 and month(买书日期)=9
union
select 10 as 买书月份， isnull(sum(买书数),0) as 买书数 from yourtable where year(买书日期) = 2000 and month(买书日期)=10
union
select 11 as 买书月份， isnull(sum(买书数),0) as 买书数 from yourtable where year(买书日期) = 2000 and month(买书日期)=11
union
select 12 as 买书月份， isnull(sum(买书数),0) as 买书数 from yourtable where year(买书日期) = 2000 and month(买书日期)=12

[orcale]

create proc sp_find
@ye varchar(10)
@mon varchar(10)
as
sql='select '买书日期','买书数' from book where @ye=year('买书日期')and @mon=month('买书日期') '
exec(sql)

[qqqdong]

怎么两个
http://expert.csdn.net/Expert/topic/1124/1124029.xml?temp=.2979853

[newyu1127]

例如：
表book
字段buy_date（买书日期 varchar(10)）,num（买书数 int）
建一个函数
create function mon(@year varchar(4))
returns table
as
return(select sum(num) number from book where substring(buy_date,1,4)=@year group by substring(buy_date,5,6))
go
查询时比如查年的各个月的买书数量
select * from dbo.mon('2002')

[newyu1127]

不好意思写了不完整应该是：
create function mon(@year varchar(4))
returns table
as
return(select substring(buy_date,5,6)month,sum(num) number from book where substring(buy_date,1,4)=@year group by substring(buy_date,5,6))
go

[蓝天]

select month(买书日期) as 买书月份， count(买书数) 买书数 from yourtable where year(买书日期) = 2000 group by month(买书日期)