求教:这个两个关联表一个月的数据统计SQL语句怎么写？

[eteok]

有两个表，
一个是车票类别表(Types)
TypeID TypeName
1 硬座票
2 软座票
3 卧铺票
4 学生票
5 ......

一个是车票表 (Tickets),有很多字段，我这只列出重要的
TicketID TicketTypeID Price SaleDate
1 1 50.00 2005-10-05 08:45:20
2 1 50.00 2005-10-05 10:21:10
3 1 80.00 2005-10-20 08:45:20
4 4 45.00 2005-10-20 10:21:10
5 5 48.00 2005-10-20 10:21:10
6 2 70.00 2005-10-22 10:21:10
7 3 68.00 2005-10-22 10:21:10
..... ... ..... ...

车票类别表中的类别数目不固定；
车票表中的TicketTypeID 对应的是车票类别表中的ID,每一条记录表示一张票；

我现在的目的就是生成一个月报表，以上面10月为例，
表头应该是日期+车票类别表中的记录

售票日期 硬座票 软座票 卧铺票 学生票 ...
2005-10-01 0 0 0 0
2005-10-02 0 0 0 0
..........
2005-10-05 2 0 0 0
.......... .. .. .. ..
2005-10-31 .. ..
总计 3 1 1 1


也就是统计处一个月中每一天各种票的销售统计（如果哪一天没有买票，也要列出来，数量都为0）,最后在加上一个总计
票的类别不能固定

盼高手赐教`！ 分不够再加

[mislrb]

create table t_a(TypeID int identity(1,1),TypeName varchar(10))
insert t_a
select '硬座票1' union all
select '软座票' union all
select '卧铺票' union all
select '学生票'

create table t_b(TicketID int,TicketTypeID int,Price decimal(18,2),SaleDate datetime)
insert t_b
select 1, 1, 50.00 , '2005-10-05 08:45:20' union all
select 2, 1, 50.00 , '2005-10-05 10:21:10' union all
select 3, 1, 80.00 , '2005-10-20 08:45:20' union all
select 4, 4, 45.00 , '2005-10-20 10:21:10' union all
select 5, 5, 48.00 , '2005-10-20 10:21:10' union all
select 6, 2, 70.00 , '2005-10-22 10:21:10' union all
select 7, 3, 68.00 , '2005-10-22 10:21:10'

declare @s varchar(8000)
set @s=''
select @s=@s+',['+typename+']=isnull(sum(case when t.typeid='+cast(typeid as varchar(10))+' then t.price end),0) ' from t_a
set @s='select [售票日期]=case grouping(t.saledate) when 1 then ''总和'' else t.saledate end'+@s
set @s=@s+' from (select saledate=convert(varchar(10),b.saledate,120),a.typeid,a.typename,b.price from t_a a left join t_b b on a.typeid=b.tickettypeid) t group by t.saledate with rollup order by grouping(t.saledate),t.saledate'
exec(@s)

drop table t_a,t_b

/*
售票日期 硬座票1 软座票 卧铺票 学生票
---------- ---------------------------------------- ---------------------------------------- ---------------------------------------- ----------------------------------------
2005-10-05 100.00 .00 .00 .00
2005-10-20 80.00 .00 .00 45.00
2005-10-22 .00 70.00 68.00 .00
总和 180.00 70.00 68.00 45.00

警告: 聚合或其它 SET 操作消除了空值。
*/

[hpym365]

--借用楼上的测试数据改了一下 ^_^
create table t_a(TypeID int identity(1,1),TypeName varchar(10))
insert t_a
select '硬座票1' union all
select '软座票' union all
select '卧铺票' union all
select '学生票'

create table t_b(TicketID int,TicketTypeID int,Price decimal(18,2),SaleDate datetime)
insert t_b
select 1, 1, 50.00 , '2005-10-05 08:45:20' union all
select 2, 1, 50.00 , '2005-10-05 10:21:10' union all
select 3, 1, 80.00 , '2005-10-20 08:45:20' union all
select 4, 4, 45.00 , '2005-10-20 10:21:10' union all
select 5, 5, 48.00 , '2005-10-20 10:21:10' union all
select 6, 2, 70.00 , '2005-10-22 10:21:10' union all
select 7, 3, 68.00 , '2005-10-22 10:21:10' union all
select 8, 1, 50.00 , '2008-11-25 18:38:24' union all
select 9, 1, 50.00 , '2008-11-25 23:55:55'
go
---查询语句
Select 售票日期=Convert(VarChar(10),SaleDate,102),
硬座票=sum(case TicketTypeID when 1 then 1 else 0 end),
学生票=sum(case TicketTypeID when 2 then 1 else 0 end),
软座票=sum(case TicketTypeID when 3 then 1 else 0 end),
卧铺票=sum(case TicketTypeID when 4 then 1 else 0 end)
from t_b a
where exists (select * from t_b where Month(a.SaleDate)=Month(SaleDate) and year(a.saledate)=year(saledate) and day(a.saledate)=day(saledate))
group by Convert(VarChar(10),SaleDate,102)
order by 售票日期 asc
go
------结果----------------
/*
售票日期 硬座票 学生票 软座票 卧铺票
2005.10.05 2 0 0 0
2005.10.20 1 0 0 1
2005.10.22 0 1 1 0
2008.11.25 2 0 0 0
*/
----drop table t_a,t_b

[hpym365]

---接上面
---为了方便建立一个视图
create view v_t
as
Select 售票日期=Convert(VarChar(10),SaleDate,102),
硬座票=sum(case TicketTypeID when 1 then 1 else 0 end),
学生票=sum(case TicketTypeID when 2 then 1 else 0 end),
软座票=sum(case TicketTypeID when 3 then 1 else 0 end),
卧铺票=sum(case TicketTypeID when 4 then 1 else 0 end)
from t_b a
where exists (select * from t_b where Month(a.SaleDate)=Month(SaleDate) and year(a.saledate)=year(saledate) and day(a.saledate)=day(saledate))
group by Convert(VarChar(10),SaleDate,102)
----做个临时表，放日期用的 设为31天
set rowcount 31
select identity(int,1,1) as 售票日期 into #t from sysobjects
select * from #t
set rowcount 0
go
---查询
select * from #t a
left join v_t b
on a.售票日期=day(b.售票日期)
---结果
售票日期 硬座票 学生票 软座票 卧铺票
1 NULL NULL NULL NULL NULL
2 NULL NULL NULL NULL NULL
3 NULL NULL NULL NULL NULL
4 NULL NULL NULL NULL NULL
5 2005.10.05 2 0 0 0
6 NULL NULL NULL NULL NULL
7 NULL NULL NULL NULL NULL
8 NULL NULL NULL NULL NULL
9 NULL NULL NULL NULL NULL
10 NULL NULL NULL NULL NULL
11 NULL NULL NULL NULL NULL
12 NULL NULL NULL NULL NULL
13 NULL NULL NULL NULL NULL
14 NULL NULL NULL NULL NULL
15 NULL NULL NULL NULL NULL
16 NULL NULL NULL NULL NULL
17 NULL NULL NULL NULL NULL
18 NULL NULL NULL NULL NULL
19 NULL NULL NULL NULL NULL
20 2005.10.20 1 0 0 1
21 NULL NULL NULL NULL NULL
22 2005.10.22 0 1 1 0
23 NULL NULL NULL NULL NULL
24 NULL NULL NULL NULL NULL
25 2008.11.25 2 0 0 0
26 NULL NULL NULL NULL NULL
27 NULL NULL NULL NULL NULL
28 NULL NULL NULL NULL NULL
29 NULL NULL NULL NULL NULL
30 NULL NULL NULL NULL NULL
31 NULL NULL NULL NULL NULL

[yaohai]

楼上的,能不能把左联结后的null都改为0啊

[chbvb4302]

inert into #tmp
Select Convert(VarChar(10),a.SaleDate,102) as 日期,
sum(case when a.TicketType=1 then 1 else 0 end) as 硬座票,
sum(case when a.TicketType=2 then 1 else 0 end) as 学生票,
sum(case when a.ticketType=3 then 1 else 0 end) as 软座票,
sum(case when a.ticketType=4 then 1 else 0 end) as 卧铺票,
0 as sortId
from Tickets a inner join types b on a.ticketypeid=b.typeid
where Month(SaleDate)='10'
group by Convert(VarChar(10),SaleDate,102)
order by 日期 asc

insert into #tmp select 日期 ,sum(硬座票),sum(学生票),sum(软座票),sum(卧铺票),1 as SortId
where SortId=0

select * from #tmp Order by Sortid

[chbvb4302]

上面忘了加个group by

inert into #tmp
Select Convert(VarChar(10),a.SaleDate,102) as 日期,
sum(case when a.TicketType=1 then 1 else 0 end) as 硬座票,
sum(case when a.TicketType=2 then 1 else 0 end) as 学生票,
sum(case when a.ticketType=3 then 1 else 0 end) as 软座票,
sum(case when a.ticketType=4 then 1 else 0 end) as 卧铺票,
0 as sortId
from Tickets a inner join types b on a.ticketypeid=b.typeid
where Month(SaleDate)='10'
group by Convert(VarChar(10),SaleDate,102)
order by 日期 asc

insert into #tmp select 日期 ,sum(硬座票),sum(学生票),sum(软座票),sum(卧铺票),1 as SortId
where SortId=0 group by 日期

select * from #tmp Order by Sortid

[mislrb]

if exists(select 1 from sysobjects where id=object_id(N'p_getNewTable') and xtype=N'P')
drop procedure p_getNewTable
go
create procedure p_getNewTable
@date datetime
as
declare @s varchar(8000),@row int--@date datetime,
select @s='',@row=cast(dateadd(mm,1,@date) as int)-cast(@date as int)--,@date='2005-10-01'

set rowcount @row
select id=identity(int,1,1) into #t from sysobjects
set rowcount 0

select @s=@s+',['+typename+']=isnull(sum(case when b.typeid='+cast(typeid as varchar(10))+' then b.price end),0) ' from t_a
set @s='select [售票日期]=case grouping(b.saledate) when 1 then ''总和'' else b.saledate end'+@s+',总和=sum(b.price)'
set @s=@s+' from (select saledate=convert(varchar(10),dateadd(dd,tt.id-1,cast('''+cast(@date as varchar(10))+''' as datetime)),120),bb.tickettypeid,price=isnull(bb.price,0),typeid=bb.tickettypeid '
set @s=@s+'from #t tt left join t_b bb on convert(varchar(10),bb.saledate,120)=convert(varchar(10),dateadd(dd,tt.id-1,cast('''+cast(@date as varchar(10))+''' as datetime)),120)) b '
set @s=@s+' left join t_a a on a.typeid=b.tickettypeid '
set @s=@s+'group by b.saledate with rollup order by grouping(b.saledate),b.saledate'
exec(@s)
drop table #t
go

exec dbo.p_getNewTable '2005-10-01'

[mislrb]

结果为：
/*
售票日期 硬座票1 软座票 卧铺票 学生票 总和
---------------- ------- ------ --------- -------- ------
2005-10-01 .00 .00 .00 .00 .00
2005-10-02 .00 .00 .00 .00 .00
2005-10-03 .00 .00 .00 .00 .00
2005-10-04 .00 .00 .00 .00 .00
2005-10-05 100.00 .00 .00 .00 100.00
2005-10-06 .00 .00 .00 .00 .00
2005-10-07 .00 .00 .00 .00 .00
2005-10-08 .00 .00 .00 .00 .00
2005-10-09 .00 .00 .00 .00 .00
2005-10-10 .00 .00 .00 .00 .00
2005-10-11 .00 .00 .00 .00 .00
2005-10-12 .00 .00 .00 .00 .00
2005-10-13 .00 .00 .00 .00 .00
2005-10-14 .00 .00 .00 .00 .00
2005-10-15 .00 .00 .00 .00 .00
2005-10-16 .00 .00 .00 .00 .00
2005-10-17 .00 .00 .00 .00 .00
2005-10-18 .00 .00 .00 .00 .00
2005-10-19 .00 .00 .00 .00 .00
2005-10-20 80.00 .00 .00 45.00 173.00
2005-10-21 .00 .00 .00 .00 .00
2005-10-22 .00 70.00 68.00 .00 138.00
2005-10-23 .00 .00 .00 .00 .00
2005-10-24 .00 .00 .00 .00 .00
2005-10-25 .00 .00 .00 .00 .00
2005-10-26 .00 .00 .00 .00 .00
2005-10-27 .00 .00 .00 .00 .00
2005-10-28 .00 .00 .00 .00 .00
2005-10-29 .00 .00 .00 .00 .00
2005-10-30 .00 .00 .00 .00 .00
2005-10-31 .00 .00 .00 .00 .00
总和 180.00 70.00 68.00 45.00 411.00
*/

[eteok]

感谢楼上老兄，不过还有一点就是日期要能够循环一个月的，也就是如10月的话，就从10.1 到 10.31 全部列出，某一天没有卖票(Tickets中没有该日期的记录)，也要列出来，各票种数目为0就是。当然，月份这个数据也是不确定的。

自己搞到深夜了，写出了票种固定，没有售票的日期不显示的这个，望高手继续Help!
Select Convert(VarChar(10),SaleDate,102) as 日期,
sum(case when TicketType='硬座票' then 1 else 0 end) as 硬座票,
sum(case when TicketType='学生票' then 1 else 0 end) as 学生票,
sum(case when ticketType='软座票' then 1 else 0 end) as 软座票,
sum(case when ticketType='卧铺票' then 1 else 0 end) as 卧铺票
from Tickets
where Month(SaleDate)='10'
group by Convert(VarChar(10),SaleDate,102)
order by 日期 asc

[eteok]

多谢各位大哥，新年快乐！
这两天在外，回到单位试一下就结贴！

[ReViSion]

Create table Types(TypeID int identity,TypeName varchar(10))
insert into types(TypeName) select '硬座票'
union all select '软座票'
union all select '卧铺票'
union all select '学生票'

Create table Tickets(TicketID int identity,TicketTypeID int,Price money,SaleDate datetime)
insert into tickets(TicketTypeID,Price,SaleDate)
select 1 , 50.00 ,'2005-10-05 08:45:20'
union all select 1 , 50.00 ,'2005-10-05 10:21:10'
union all select 1 , 80.00 ,'2005-10-20 08:45:20'
union all select 4 , 45.00 ,'2005-10-20 10:21:10'
union all select 5 , 48.00 ,'2005-10-20 10:21:10'
union all select 2 , 70.00 ,'2005-10-22 10:21:10'
union all select 3 , 68.00 ,'2005-10-22 10:21:10'


--功能：得到指定年月的日期列表
--@Year:年
--@Month:月
Create function Get_MonDays
(@Year char(4),@month char(2))
returns @Mondays TABLE (Mday datetime primary key)
as
begin
declare @Tmpdate datetime --临时用
declare @Enddate datetime --月末日期

--得到指定年月的最后一天的日期
set @Enddate=dateadd(d,-1,dateadd(m,1,@year+'-'+@month+'-01'))
--开始写入指定年月日期列表
set @Tmpdate=@Year+'-'+@month+'-01'
while(@Tmpdate<=@Enddate)
begin
insert into @Mondays
select @Tmpdate

--进入下一天
set @Tmpdate=dateadd(d,1,@Tmpdate)
end
return
end

/*********************************
以下为构建统计语句
*********************************/

Declare @sqlstr varchar(8000)
Declare @FieldList varchar(2000)
declare @year char(4)
declare @month char(2)
declare @enddate char(10)
--初始化各变量
set @Year='2005'
set @month='10'
set @FieldList=''

--得到指定年月的终结时间
set @enddate=convert(char(10),dateadd(d,-1,dateadd(m,1,@year+'-'+@month+'-01')),120)

--构建查询统计语句
select @FieldList=@FieldList+',['+TypeName+']' from Types
select @sqlstr='select a.mday'+@FieldList+'
from dbo.Get_MonDays('''+@year+''','''+@month+''')a
left join
(select saledate=convert(char(10),saledate,120)
'
select @sqlstr=@sqlstr+',sum(case TicketTypeID when '+cast(typeid as varchar)+' then 1 else 0 end) as ['+typename+']
' from types
select @sqlstr=@sqlstr+'from Tickets
where saledate between '''+@year+'-'+@month+'-01'' and '''+@enddate+' 23:59:59''
group by convert(char(10),SaleDate,120))b
on a.mday=b.saledate'

--执行查询
exec (@Sqlstr)