求SQL语句
我有一表id rq pm sl je pj
1 2012-02-01 铅笔 100 500.00 5.00
2 2012-02-01 钢笔 50 1000.00 20.00
3 2012-02-01 笔记本 60 600.00 10.00
4 2012-02-02 铅笔 80 480.00 6.00
5 2012-02-02 钢笔 70 2100.00 30.00
6 2012-02-02 笔记本 60 1200.00 20.00
7 2012-02-03 铅笔 50 450.00 9.00
8 2012-02-03 钢笔 90 3600.00 40.00
9 2012-02-03 笔记本 60 1800.00 30.00直至2012-02-29,每天都有数据我要得到另一表id rq 总sl 总je 总pj 铅笔sl 钢笔sl 笔记本sl 铅笔je 钢笔je 笔记本je
1 2012-02-0 210 2100.00 10.00 100 50 60 500.00 1000.00 600.00
2 2012-02-0 210 3780.00 23.30 80 70 60 480.00 2100.00 1200.00
3 2012-02-03 200 4850.00 292.50 50 90 60 450.00 3600.00 1800.00合计 620 10730.00 230 210 180 1430.00 6700.00 3600.00 求SQL语句
我有一表id rq pm sl je pj
1 2012-02-01 铅笔 100 500.00 5.00
2 2012-02-01 钢笔 50 1000.00 20.00
3 2012-02-01 笔记本 60 600.00 10.00
4 2012-02-02 铅笔 80 480.00 6.00
5 2012-02-02 钢笔 70 2100.00 30.00
6 2012-02-02 笔记本 60 1200.00 20.00
7 2012-02-03 铅笔 50 450.00 9.00
8 2012-02-03 钢笔 90 3600.00 40.00
9 2012-02-03 笔记本 60 1800.00 30.00直至2012-02-29,每天都有数据我要得到另一表id rq 总sl 总je 总pj 铅笔sl 钢笔sl 笔记本sl 铅笔je 钢笔je 笔记本je
1 2012-02-0 210 2100.00 10.00 100 50 60 500.00 1000.00 600.00
2 2012-02-0 210 3780.00 23.30 80 70 60 480.00 2100.00 1200.00
3 2012-02-03 200 4850.00 292.50 50 90 60 450.00 3600.00 1800.00合计 620 10730.00 230 210 180 1430.00 6700.00 3600.00 求SQL语句
1 2012-02-01 210 2100.00 10.00 100 50 60 500.00 1000.00 600.00
2 2012-02-02 210 3780.00 23.30 80 70 60 480.00 2100.00 1200.00
3 2012-02-03 200 4850.00 292.50 50 90 60 450.00 3600.00 1800.00合计 620 10730.00 230 210 180 1430.00 6700.00 3600.00
--1 2012-02-01 铅笔 100 500.00 5.00
--2 2012-02-01 钢笔 50 1000.00 20.00
--3 2012-02-01 笔记本 60 600.00 10.00
--4 2012-02-02 铅笔 80 480.00 6.00
--5 2012-02-02 钢笔 70 2100.00 30.00
--6 2012-02-02 笔记本 60 1200.00 20.00
--7 2012-02-03 铅笔 50 450.00 9.00
--8 2012-02-03 钢笔 90 3600.00 40.00
--9 2012-02-03 笔记本 60 1800.00 30.00if OBJECT_ID('[Table1]') is not null drop table [Table1]
create table [Table1](id int,rq datetime,pm nvarchar(50),sl decimal(15,2),je decimal(15,2),pj decimal(15,2))insert into [Table1]
select 1, '2012-02-01','铅笔',100,500.00,5.00 union all
select 2, '2012-02-01','钢笔',50,1000.00,20.00 union all
select 3, '2012-02-01','笔记本', 60, 600.00, 10.00 union all
select 4, '2012-02-02', '铅笔', 80, 480.00, 6.00 union all
select 5, '2012-02-02', '钢笔', 70, 2100.00, 30.00 union all
select 6, '2012-02-02', '笔记本',60, 1200.00, 20.00 union all
select 7, '2012-02-03', '铅笔', 50, 450.00, 9.00 union all
select 8, '2012-02-03', '钢笔', 90, 3600.00, 40.00 union all
select 9, '2012-02-03', '笔记本',60, 1800.00, 30.00select ROW_NUMBER() over(order by rq) as id,rq,SUM(sl) as 总sl,SUM(je) as 总je,SUM(pj) as 总pj,
sum(case when pm='铅笔' then sl else 0 end) as 铅笔sl,
sum(case when pm='钢笔' then sl else 0 end) as 钢笔sl,
sum(case when pm='笔记本' then sl else 0 end) as 笔记本sl,
sum(case when pm='铅笔' then je else 0 end) as 铅笔je,
sum(case when pm='钢笔' then je else 0 end) as 钢笔je,
sum(case when pm='笔记本' then je else 0 end) as 笔记本je,
sum(case when pm='铅笔' then pj else 0 end) as 铅笔pj,
sum(case when pm='钢笔' then pj else 0 end) as 钢笔pj,
sum(case when pm='笔记本' then pj else 0 end) as 笔记本pj
from [Table1]
group by rqdrop table [Table1]--id rq 总sl 总je 总pj 铅笔sl 钢笔sl 笔记本sl 铅笔je 钢笔je 笔记本je 铅笔pj 钢笔pj 笔记本pj
--1 2012-02-01 00:00:00.000 210.00 2100.00 35.00 100.00 50.00 60.00 500.00 1000.00 600.00 5.00 20.00 10.00
--2 2012-02-02 00:00:00.000 210.00 3780.00 56.00 80.00 70.00 60.00 480.00 2100.00 1200.00 6.00 30.00 20.00
--3 2012-02-03 00:00:00.000 200.00 5850.00 79.00 50.00 90.00 60.00 450.00 3600.00 1800.00 9.00 40.00 30.00
ROW_NUMBER() 函数不能识别