部门表tbl_depart departID,departName票券类型表tbl_type typeID,typeName部门领用票券表 tbl_ticket id,typeID(关联tbl_type),departID(关联tbl_depart),ticketCount(领用数量)我想得到这样的结果:显示tbl_ticket的所有记录,并统计其组所拥有的不同类型票券的总和.如:tbl_ticket中原有如下记录
ID(自增) typeID departID ticketCount
1 1 1 12
2 1 1 5
3 2 1 6
4 1 2 14
5 2 2 3查询得出这样的结果
ID(自增) typeID departID ticketCount totalCount
1 1 1 12 1部门有1类型17,2类型6
2 1 1 5 1部门有1类型17,2类型6
3 2 1 6 1部门有1类型17,2类型14
4 1 2 14 2部门有1类型14,2类型3
5 2 2 3 2部门有1类型14,2类型3
还有如何得出这样的结果: 类型1票 类型2票 .......
部门1 17 6
部门2 14 3
...
,,,,
ID(自增) typeID departID ticketCount
1 1 1 12
2 1 1 5
3 2 1 6
4 1 2 14
5 2 2 3查询得出这样的结果
ID(自增) typeID departID ticketCount totalCount
1 1 1 12 1部门有1类型17,2类型6
2 1 1 5 1部门有1类型17,2类型6
3 2 1 6 1部门有1类型17,2类型14
4 1 2 14 2部门有1类型14,2类型3
5 2 2 3 2部门有1类型14,2类型3
还有如何得出这样的结果: 类型1票 类型2票 .......
部门1 17 6
部门2 14 3
...
,,,,
create table tbl_depart(departID int primary key,departName varchar(30));
insert into tbl_depart
select 1,'1部门'
union select 2,'2部门'
create table tbl_type(typeID int primary key,typeName varchar(30));
insert into tbl_type
select 1,'1类型'
union select 2,'2类型'
create table tbl_ticket (id int identity(1,1),typeID int,departID int,ticketCount int);
insert into tbl_ticket(typeID,departID,ticketCount)
select 1,1,12
union all select 1,1,5
union all select 2,1,6
union all select 1,2,14
union all select 2,2,3
go
create function f_str(@dept int)
returns varchar(1000)
as
begin
declare @str varchar(1000)
set @str =''
select @str=@str+','+typename+cast(c as varchar(20)) from (select typename,sum(ticketcount) as c from tbl_ticket,tbl_type where tbl_ticket.typeid=tbl_type.typeid and departid=@dept group by departid,typename) t;
return right(@str,len(@str)-1)
end
go
select * from tbl_depart;
select * from tbl_type;
select t.*,departname+'有'+dbo.f_str(t.departid) from tbl_ticket t,tbl_depart d,tbl_type p where t.departID=d.departID and t.typeid=p.typeid;
go
drop table tbl_depart;
drop table tbl_type;
drop table tbl_ticket;
drop function f_str;
create table tbl_depart(departID int primary key,departName varchar(30));
insert into tbl_depart
select 1,'1部门'
union select 2,'2部门'
create table tbl_type(typeID int primary key,typeName varchar(30));
insert into tbl_type
select 1,'1类型'
union select 2,'2类型'
create table tbl_ticket (id int identity(1,1),typeID int,departID int,ticketCount int);
insert into tbl_ticket(typeID,departID,ticketCount)
select 1,1,12
union all select 1,1,5
union all select 2,1,6
union all select 1,2,14
union all select 2,2,3
go
create function f_str(@dept int)
returns varchar(1000)
as
begin
declare @str varchar(1000)
set @str =''
select @str=@str+','+typename+cast(c as varchar(20)) from (select typename,sum(ticketcount) as c from tbl_ticket,tbl_type where tbl_ticket.typeid=tbl_type.typeid and departid=@dept group by departid,typename) t;
return right(@str,len(@str)-1)
end
go
select * from tbl_depart;
select * from tbl_type;
select t.*,departname+'有'+dbo.f_str(t.departid) as 'totalCount' from tbl_ticket t,tbl_depart d,tbl_type p where t.departID=d.departID and t.typeid=p.typeid;declare @sql varchar(8000)
set @sql=''
select @sql=@sql+',sum(case typeid when '+cast(typeid as varchar(10))+' then ticketcount else 0 end) as '''+typename+'''' from tbl_type
set @sql='select departname'+@sql+' from tbl_ticket,tbl_depart where tbl_ticket.departid=tbl_depart.departid group by departname'
exec(@sql)go
drop table tbl_depart;
drop table tbl_type;
drop table tbl_ticket;
drop function f_str;
create function f_str(@dept int)
returns varchar(1000)
as
begin
declare @str varchar(1000)
set @str =''
select @str=@str+','+typename+cast(c as varchar(20)) from (select typename,sum(ticketcount) as c from tbl_ticket,tbl_type where tbl_ticket.typeid=tbl_type.typeid and departid=@dept group by departid,typename) t;
return right(@str,len(@str)-1)
end
go
--第一个结果的查询语句
select t.*,departname+'有'+dbo.f_str(t.departid) as 'totalCount' from tbl_ticket t,tbl_depart d,tbl_type p where t.departID=d.departID and t.typeid=p.typeid;
--第二个结果需要用动态语句解决
declare @sql varchar(8000)
set @sql=''
select @sql=@sql+',sum(case typeid when '+cast(typeid as varchar(10))+' then ticketcount else 0 end) as '''+typename+'''' from tbl_type
set @sql='select departname as 部门'+@sql+' from tbl_ticket,tbl_depart where tbl_ticket.departid=tbl_depart.departid group by departname'
exec(@sql)