create table work
(
worker char(4),
worktime decimal,
workmonth decimal,
type char(20)
)create table chanpin
(
type char(20),
cpname char(60)
)insert into work values('0001', 11, '1', 'c01')
insert into work values('0001', 11, '1', 'c02')
insert into work values('0001', 11, '2', 'c02')
insert into work values('0001', 12, '1', 'c02')
insert into work values('0001', 12, '2', 'c02')
insert into work values('0002', 13, '1', 'c02')
insert into work values('0002', 14, '1', 'c03')
insert into work values('0003', 15, '1', 'c03')insert into chanpin values('c01', 'cc0011')
insert into chanpin values('c02', 'cc0022')
insert into chanpin values('c03', 'cc0033')
select a.workerg, a.workmonth, a.ztime, cp1.worktime as cp1, cp2.worktime as cp2, cp3.worktime as cp3 from (
select worker as workerg, workmonth as workmonth, sum(worktime) as ztime
from work
group by worker, workmonth
) a
left join
(select work.worker, work.workmonth, work.worktime, chanpin.cpname from work, chanpin where work.type = chanpin.type and work.type = 'c01') cp1
on a.workerg = cp1.worker and a.workmonth = cp1.workmonth
left join
(select work.worker, work.workmonth, work.worktime, chanpin.cpname from work, chanpin where work.type = chanpin.type and work.type = 'c02') cp2
on a.workerg = cp2.worker and a.workmonth = cp2.workmonth
left join
(select work.worker, work.workmonth, work.worktime, chanpin.cpname from work, chanpin where work.type = chanpin.type and work.type = 'c03') cp3
on a.workerg = cp3.worker and a.workmonth = cp3.workmonth
(
worker char(4),
worktime decimal,
workmonth decimal,
type char(20)
)create table chanpin
(
type char(20),
cpname char(60)
)insert into work values('0001', 11, '1', 'c01')
insert into work values('0001', 11, '1', 'c02')
insert into work values('0001', 11, '2', 'c02')
insert into work values('0001', 12, '1', 'c02')
insert into work values('0001', 12, '2', 'c02')
insert into work values('0002', 13, '1', 'c02')
insert into work values('0002', 14, '1', 'c03')
insert into work values('0003', 15, '1', 'c03')insert into chanpin values('c01', 'cc0011')
insert into chanpin values('c02', 'cc0022')
insert into chanpin values('c03', 'cc0033')
select a.workerg, a.workmonth, a.ztime, cp1.worktime as cp1, cp2.worktime as cp2, cp3.worktime as cp3 from (
select worker as workerg, workmonth as workmonth, sum(worktime) as ztime
from work
group by worker, workmonth
) a
left join
(select work.worker, work.workmonth, work.worktime, chanpin.cpname from work, chanpin where work.type = chanpin.type and work.type = 'c01') cp1
on a.workerg = cp1.worker and a.workmonth = cp1.workmonth
left join
(select work.worker, work.workmonth, work.worktime, chanpin.cpname from work, chanpin where work.type = chanpin.type and work.type = 'c02') cp2
on a.workerg = cp2.worker and a.workmonth = cp2.workmonth
left join
(select work.worker, work.workmonth, work.worktime, chanpin.cpname from work, chanpin where work.type = chanpin.type and work.type = 'c03') cp3
on a.workerg = cp3.worker and a.workmonth = cp3.workmonth
解决方案 »
- 纵列改成横列,看看还有没有更合适的写法
- 如何求5^-065的值,即5的 -065 次幂在sql语句?
- 替换数据库中数据字段,replace不行
- 请问这样插入数据有什么问题啊
- [求助] 怎样根据多个字段中的两个字段来判断是否有重复记录,如果有就只取前面的,后面的也做记录并返回?
- 如何建存储
- 好象是用户数量受限制,出现如下提示,使我没办法在另外的机器连接,但我不是设为单用户模式,不只为什么
- 这条SQL语句的最优写法?
- 对视图进行触发器管理出现的问题!急
- 能否用T-SQL获取文件的路径?
- 一个表内联多个表才能显示详细信息,视图也太大了!怎么解决呢?
- sql2000中,我该如何把那个 dbo 用户去掉?
workerg workermonth ztime cp1 cp2 cp3
0001 1 34 11 12 NULL
0001 1 34 11 11 NULL
0002 1 27 NULL 13 14
0003 1 15 NULL NULL 15
0001 2 23 NULL 11 NULL
0001 2 23 NULL 12 NULL
create table work(worker varchar(4),worktime decimal,workmonth decimal,type varchar(20))
create table chanpin(type varchar(20),cpname varchar(60))
insert into work values('0001', 11, '1', 'c01')
insert into work values('0001', 11, '1', 'c02')
insert into work values('0001', 11, '2', 'c02')
insert into work values('0001', 12, '1', 'c02')
insert into work values('0001', 12, '2', 'c02')
insert into work values('0002', 13, '1', 'c02')
insert into work values('0002', 14, '1', 'c03')
insert into work values('0003', 15, '1', 'c03')insert into chanpin values('c01', 'cc0011')
insert into chanpin values('c02', 'cc0022')
insert into chanpin values('c03', 'cc0033')--执行动态交叉表查询
declare @s varchar(8000),@worker varchar(4)
set @s = ''
set @worker = '0001'select
@s=@s+',['+cpname+']=sum(case type when '''+rtrim(type)+''' then worktime else 0 end)'
from
chanpin
group by
cpname,typeset @s = 'select workmonth,worktime=sum(worktime)'+@s+' from work where worker='''+@worker+''' group by workmonth'
exec(@s)
go--输出结果
/*
1 34 11 23 0
2 23 0 23 0
*/--删除测试数据
drop table work,chanpin
go