------建立环境
create table #PortShip (Port_ProductID int,Port_CurrHaven varchar(100),Port_Number numeric,Port_InsertTime datetime )create table #Product (Product_ID int,产品名称 varchar(100))insert into #PortShip values (1,'宁波港',456,'2004-01-11')
insert into #PortShip values (1,'宁波港',456,'2004-01-12')
insert into #PortShip values (2,'上海港',456,'2004-07-11')
insert into #PortShip values (3,'上海港',456,'2004-12-11')
insert into #PortShip values (4,'宁波港',456,'2004-5-26')
insert into #PortShip values (1,'别的港',456,'2004-4-2')
insert into #PortShip values (3,'上海港',456,'2004-2-4')
insert into #PortShip values (2,'别的港',456,'2004-01-8')insert into #Product values (1,'苯乙烯')
insert into #Product values (2,'乙二醇 ')
insert into #Product values (3,'正构烷烃')
insert into #Product values (4,'基础油')--------你要的
select t2.产品名称,
"1月"=(select sum(Port_Number) from #PortShip where
Port_ProductID=t1.Port_ProductID and convert(varchar(7),Port_InsertTime,120)=convert(varchar(7),'2004-01',120)),
"2月"=(select sum(Port_Number) from #PortShip where
Port_ProductID=t1.Port_ProductID and convert(varchar(7),Port_InsertTime,120)=convert(varchar(7),'2004-02',120)),
"3月"=(select sum(Port_Number) from #PortShip where
Port_ProductID=t1.Port_ProductID and convert(varchar(7),Port_InsertTime,120)=convert(varchar(7),'2004-03',120)),
"4月"=(select sum(Port_Number) from #PortShip where
Port_ProductID=t1.Port_ProductID and convert(varchar(7),Port_InsertTime,120)=convert(varchar(7),'2004-04',120)),
"5月"=(select sum(Port_Number) from #PortShip where
Port_ProductID=t1.Port_ProductID and convert(varchar(7),Port_InsertTime,120)=convert(varchar(7),'2004-05',120)),
"6月"=(select sum(Port_Number) from #PortShip where
Port_ProductID=t1.Port_ProductID and convert(varchar(7),Port_InsertTime,120)=convert(varchar(7),'2004-06',120)),
"7月"=(select sum(Port_Number) from #PortShip where
Port_ProductID=t1.Port_ProductID and convert(varchar(7),Port_InsertTime,120)=convert(varchar(7),'2004-07',120)),
"8月"=(select sum(Port_Number) from #PortShip where
Port_ProductID=t1.Port_ProductID and convert(varchar(7),Port_InsertTime,120)=convert(varchar(7),'2004-08',120)),
"9月"=(select sum(Port_Number) from #PortShip where
Port_ProductID=t1.Port_ProductID and convert(varchar(7),Port_InsertTime,120)=convert(varchar(7),'2004-09',120)),
"10月"=(select sum(Port_Number) from #PortShip where
Port_ProductID=t1.Port_ProductID and convert(varchar(7),Port_InsertTime,120)=convert(varchar(7),'2004-10',120)),
"11月"=(select sum(Port_Number) from #PortShip where
Port_ProductID=t1.Port_ProductID and convert(varchar(7),Port_InsertTime,120)=convert(varchar(7),'2004-11',120)),
"12月"=(select sum(Port_Number) from #PortShip where
Port_ProductID=t1.Port_ProductID and convert(varchar(7),Port_InsertTime,120)=convert(varchar(7),'2004-12',120))
from #PortShip t1,#Product t2 where t1.Port_ProductID=t2.Product_ID group by t1.Port_ProductID,t2.产品名称
---删除环境
drop table #PortShip
drop table #Product
解决方案 »
- 消息 207,级别 16,状态 1,过程 sp_testdll,第 15 行 列名 'c:\welcome.txt' 无效。
- 按日期分组统计
- 在安装时出现“安装程序配置服务器失败”的问题,无法解决,请求帮助
- 帮小妹优化一条SQL语句拉,谢谢各位!!!
- 一个小小的触发器的问题
- 请问在SQL中建立了登录用户,如何禁止它的备份权限?
- 关于事务处理得问题,请高手做答。。。
- 关于where in 的问题?????????
- 如何把visul foxpro的dbf表利用dts转换成access表
- 都半年了还没有什么长进。。。
- 表名为变量的 插入问题
- 请兄弟门帮忙,看我的这条语句写的对不对! 运行的结果好象不对!!
表中有两个字段
F_Column varchar
F_Name varchar
内容是
1 1月份
2 2月份
3 3月份
4 4月份
5 5月份
6 6月份
7 7月份
8 8月份
9 9月份
10 10月份
11 11月份
12 12月份
然后执行下列语句
declare @var nvarchar(2000)
set @var=''
select @var=@var+ 'sum(case month(Port_InsertTime) when '''+ a.F_column +''' then Port_Number else 0 end) as '''+ a.F_name +''',' from t_month a
set @var=left(@var,len(@var)-1)
set @var='select port_productid, '+ @var +' from PortShip where Port_CurrHaven=''宁波港'' group by port_productid '
exec sp_executesql @var
select port_productid, sum(case month(Port_InsertTime) when '1' then Port_Number else 0 end) as '1月份',sum(case month(Port_InsertTime) when '2' then Port_Number else 0 end) as '2月份',sum(case month(Port_InsertTime) when '3' then Port_Number else 0 end) as '3月份',sum(case month(Port_InsertTime) when '4' then Port_Number else 0 end) as '4月份',sum(case month(Port_InsertTime) when '5' then Port_Number else 0 end) as '5月份',sum(case month(Port_InsertTime) when '6' then Port_Number else 0 end) as '6月份',sum(case month(Port_InsertTime) when '7' then Port_Number else 0 end) as '7月份',sum(case month(Port_InsertTime) when '8' then Port_Number else 0 end) as '8月份',sum(case month(Port_InsertTime) when '9' then Port_Number else 0 end) as '9月份',sum(case month(Port_InsertTime) when '10' then Port_Number else 0 end) as '10月份',sum(case month(Port_InsertTime) when '11' then Port_Number else 0 end) as '11月份',sum(case month(Port_InsertTime) when '12' then Port_Number else 0 end) as '12月份' from PortShip where Port_CurrHaven='宁波港' group by port_productid
建立一个表month
表中有两个字段
F_Column varchar
F_Name varchar
内容是
1 1月份
2 2月份
3 3月份
4 4月份
5 5月份
6 6月份
7 7月份
8 8月份
9 9月份
10 10月份
11 11月份
12 12月份
然后执行下列语句
declare @var nvarchar(2000)
set @var=''
select @var=@var+ 'sum(case month(Port_InsertTime) when '''+ a.F_column +''' then Port_Number else 0 end) as '''+ a.F_name +''',' from t_month a
set @var=left(@var,len(@var)-1)
set @var='select port_productid, '+ @var +' from PortShip where Port_CurrHaven=''宁波港'' group by port_productid '
exec sp_executesql @var
insert into #PortShip values (1,'宁波港',232,'2004-01-12')
insert into #PortShip values (2,'上海港',4023,'2004-07-11')
insert into #PortShip values (3,'上海港',423,'2004-12-11')
insert into #PortShip values (4,'宁波港',423,'2004-5-26')
insert into #PortShip values (1,'别的港',456,'2004-4-2')
insert into #PortShip values (3,'上海港',456,'2004-2-4')
insert into #PortShip values (2,'别的港',456,'2004-01-8')insert into #Product values (1,'苯乙烯')
insert into #Product values (2,'乙二醇 ')
insert into #Product values (3,'正构烷烃')
insert into #Product values (4,'基础油')
select b.产品名称,
sum(case when month(a.port_insertTime)=1 then port_number else 0 end) as '01月份',
sum(case when month(a.port_insertTime)=2 then port_number else 0 end) as '02月份',
sum(case when month(a.port_insertTime)=3 then port_number else 0 end) as '03月份',
sum(case when month(a.port_insertTime)=4 then port_number else 0 end) as '04月份',
sum(case when month(a.port_insertTime)=5 then port_number else 0 end) as '05月份',
sum(case when month(a.port_insertTime)=6 then port_number else 0 end) as '06月份',
sum(case when month(a.port_insertTime)=7 then port_number else 0 end) as '07月份',
sum(case when month(a.port_insertTime)=8 then port_number else 0 end) as '08月份',
sum(case when month(a.port_insertTime)=9 then port_number else 0 end) as '09月份',
sum(case when month(a.port_insertTime)=10 then port_number else 0 end) as '10月份',
sum(case when month(a.port_insertTime)=11 then port_number else 0 end) as '11月份',
sum(case when month(a.port_insertTime)=12 then port_number else 0 end) as '12月份'
from #portship a left join #product b on a.port_productid=b.Product_ID
where a.port_currhaven='宁波港'
group by b.产品名称
as
select product_name,
select 一月份=sum(case when month(port_inserttime)=1 then port_number else 0) from portship where year(port_inserttime)=@year,
二月份=sum(case when month(port_inserttime)=2 then port_number else 0) from portship where year(port_inserttime)=@year,
三月份=sum(case when month(port_inserttime)=3 then port_number else 0) from portship where year(port_inserttime)=@year,
四月份=sum(case when month(port_inserttime)=4 then port_number else 0) from portship where year(port_inserttime)=@year,
五月份=sum(case when month(port_inserttime)=5 then port_number else 0) from portship where year(port_inserttime)=@year,
六月份=sum(case when month(port_inserttime)=6 then port_number else 0) from portship where year(port_inserttime)=@year,
七月份=sum(case when month(port_inserttime)=7 then port_number else 0) from portship where year(port_inserttime)=@year,
八月份=sum(case when month(port_inserttime)=8 then port_number else 0) from portship where year(port_inserttime)=@year,
九月份=sum(case when month(port_inserttime)=9 then port_number else 0) from portship where year(port_inserttime)=@year,
十月份=sum(case when month(port_inserttime)=10 then port_number else 0) from portship where year(port_inserttime)=@year,
十一月份=sum(case when month(port_inserttime)=11 then port_number else 0) from portship where year(port_inserttime)=@year,
十二月份=sum(case when month(port_inserttime)=12 then port_number else 0) from portship where year(port_inserttime)=@year
from product where Port_CurrHaven=@port group by product_id
********************************
exec pro_port '宁波港',2004
谢谢.非常感谢
from #PortShip t1 right outer join #Product t2 on t1.Port_ProductID=t2.Product_ID group by
.............
Port_ProductID=t1.Port_ProductID and convert(varchar(7),Port_InsertTime,120)=convert(varchar(7),'2004-12',120)),
"合计"=(select sum(Port_Number) from #PortShip where
Port_ProductID=t1.Port_ProductID and convert(varchar(4),Port_InsertTime,120)= '2004' )
from #PortShip t1 right outer join #Product t2
on t1.Port_ProductID=t2.Product_ID group by t1.Port_ProductID,t2.产品名称
...............