CREATE TABLE [Test] ( [id] [int] IDENTITY (1, 1) NOT NULL , [name] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL , [subject] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL , [Source] [numeric](18, 0) NULL ) ON [PRIMARY]GOINSERT INTO [test] ([name],[subject],[Source]) values (N'张三',N'语文',60)INSERT INTO [test] ([name],[subject],[Source]) values (N'李四',N'数学',70)INSERT INTO [test] ([name],[subject],[Source]) values (N'王五',N'英语',80)INSERT INTO [test] ([name],[subject],[Source]) values (N'王五',N'数学',75)INSERT INTO [test] ([name],[subject],[Source]) values (N'王五',N'语文',57)INSERT INTO [test] ([name],[subject],[Source]) values (N'李四',N'语文',80)INSERT INTO [test] ([name],[subject],[Source]) values (N'张三',N'英语',100)Go
交叉表语句的实现:--用于:交叉表的列数是确定的select name,sum(case subject when '数学' then source else 0 end) as '数学', sum(case subject when '英语' then source else 0 end) as '英语', sum(case subject when '语文' then source else 0 end) as '语文' from test group by name --用于:交叉表的列数是不确定的declare @sql varchar(8000)set @sql = 'select name,' select @sql = @sql + 'sum(case subject when '''+subject+''' then source else 0 end) as '''+subject+''',' from (select distinct subject from test) as a select @sql = left(@sql,len(@sql)-1) + ' from test group by name'exec(@sql)go 运行结果:
交叉表语句的实现:--用于:交叉表的列数是确定的select name,sum(case subject when '数学' then source else 0 end) as '数学', sum(case subject when '英语' then source else 0 end) as '英语', sum(case subject when '语文' then source else 0 end) as '语文' from test group by name --用于:交叉表的列数是不确定的declare @sql varchar(8000)set @sql = 'select name,' select @sql = @sql + 'sum(case subject when '''+subject+''' then source else 0 end) as '''+subject+''',' from (select distinct subject from test) as a select @sql = left(@sql,len(@sql)-1) + ' from test group by name'exec(@sql)go 运行结果:
解决方案 »
- sql 时间格式,精确到 秒 后面
- 有没有办法让两个查询数量的SQL语句放到一个SQL里
- 本人菜鸟。。请教个问题!
- 100分,数据库连接问题
- 横向排序的问题?
- 求当源表发生修改、插入和删除操作时,将源表发生改变的数据写到目标表中的sql
- 备份数据库的时候,提示“无法打开备份设备h:\freehost\cnzc\db\a.bak,设备出现错误或脱机”
- SQL2005性能疑难问题
- insert,触发器不执行怎么办,
- 我菜啊:如何修改sa的密码?与此相同,如何修改数据库的“用户”的密码?或者删除数据的“用户”?
- begin tran再使用远程查询和BEGIN DISTRIBUTED TRANSACTION都是分布式事务吗?有什么区别?用OPENDATASOURCE('MSDASQL'和SQLOLEDB'区别?相
- 急啊,只有5天了,这个过程就苦了一天,还没有出来,在线等你们,急啊!!!!!!!!!!
我看了,我菜鸟,学sql还不到一个星期,看的有些头晕,我再看看
create table [user]
(
id int identity,
name char(10),
pass char(10),
date datetime,
userno char(10)
)create table sort
(
id int identity,
name char(30),
price decimal(10,2)
)create table pay
(
id int identity,
uid int,
sid int,
date datetime,
)create table money
(
id int identity,
userno int,
price decimal(10,2),
date datetime
)
--测试数据 ,楼主提供的测试数据,与产生的报表数据不匹配
insert [user] values('tmyu','123','2003-11-21','201')
insert [user] values('tttt','123','2003-11-21','202')
insert [user] values('rrrr','123','2003-11-21','301')
insert [user] values('ffff','123','2003-11-21','302')
insert [user] values('gggg','123','2003-11-21','303')
insert sort values('鸡蛋',2)
insert sort values('鸭蛋',3)
insert sort values('电视机',15)
insert sort values('电脑',20)
insert sort values('衣服',25)
insert pay values(1,2,'2003-11-21')
insert pay values(1,3,'2003-11-20')
insert pay values(2,5,'2003-11-15')
insert pay values(5,4,'2003-11-11')
insert pay values(3,1,'2003-11-15')
insert pay values(1,2,'2003-11-15')
insert pay values(4,1,'2003-11-11')delete from money
insert money values('201',21,'2003-11-21')
insert money values('202',23,'2003-11-20')
insert money values('301',2,'2003-11-15')
insert money values('302',2,'2003-11-11')
insert money values('303',23,'2003-11-15')--比较烦,表字段的数据类型我自己定的--建视图
create view test
as
select
u.userno 班次序号,
u.name 用户,
sum(case when sid = 1 then s.price else 0 end) 类1,
sum(case when sid = 2 then s.price else 0 end) 类2,
sum(case when sid = 3 then s.price else 0 end) 类3,
sum(case when sid = 4 then s.price else 0 end) 类4,
sum(case when sid = 5 then s.price else 0 end) 类5,
sum(s.price) 合计,
m.price 实收,
case when m.price >= sum(s.price) then m.price - sum(s.price) else 0 end 长款,
case when m.price < sum(s.price) then sum(s.price) - m.price else 0 end 短款,
sum(s.price) 应收
from pay p join [user] u on p.uid = u.id join sort s on p.sid = s.id join money m on u.userno = m.userno
group by u.userno,u.name,m.price--实现, 实在不能推断出误差是怎么计算的!!!!
select 班次序号,用户,类1,类2,类3,类4,类5,合计,实收,长款,短款,应收
from
(
select u.userno 班次序号,
u.name 用户,
sum(case when sid = 1 then s.price else 0 end) 类1,
sum(case when sid = 2 then s.price else 0 end) 类2,
sum(case when sid = 3 then s.price else 0 end) 类3,
sum(case when sid = 4 then s.price else 0 end) 类4,
sum(case when sid = 5 then s.price else 0 end) 类5,
sum(s.price) 合计,
m.price 实收,
case when m.price >= sum(s.price) then m.price - sum(s.price) else 0 end 长款,
case when m.price < sum(s.price) then sum(s.price) - m.price else 0 end 短款,
sum(s.price) 应收,
left(u.userno,1) flag1,
0 flag2
from pay p join [user] u on p.uid = u.id join sort s on p.sid = s.id join money m on u.userno = m.userno
group by u.userno,u.name,m.price
union all
select '小计' ,cast(count(*) as varchar) + '人',sum(类1),sum(类2),sum(类3),sum(类4),sum(类5),sum(合计),sum(实收),sum(长款),sum(短款),sum(应收),left(班次序号,1) flag1,1 flag2
from test
group by left(班次序号,1)
union all
select '合计' , cast(count(*) as varchar) + '人',sum(类1),sum(类2),sum(类3),sum(类4),sum(类5),sum(合计),sum(实收),sum(长款),sum(短款),sum(应收),9 flag1,2 flag2
from (select 0 flag,* from test)test
group by flag
) a
order by a.flag1 , a.flag2 班次序号 用户 类1 类2 类3 类4 类5 合计 实收 长款 短款 应收
------- ------- ---------- ----- ---------------------------------------- --------------
201 tmyu .00 6.00 15.00 .00 .00 21.00 21.00 .00 .00 21.00
202 tttt .00 .00 .00 .00 25.00 25.00 23.00 .00 2.00 25.00
小计 2人 .00 6.00 15.00 .00 25.00 46.00 44.00 .00 2.00 46.00
303 gggg .00 .00 .00 20.00 .00 20.00 23.00 3.00 .00 20.00
301 rrrr 2.00 .00 .00 .00 .00 2.00 2.00 .00 .00 2.00
302 ffff 2.00 .00 .00 .00 .00 2.00 2.00 .00 .00 2.00
小计 3人 4.00 .00 .00 20.00 .00 24.00 27.00 3.00 .00 24.00
合计 5人 4.00 6.00 15.00 20.00 25.00 70.00 71.00 3.00 2.00 70.00(所影响的行数为 8 行)