--如果仅仅在数据库中处理--用楼主自己的建表 CREATE TABLE sampleTable ( lantaiID int IDENTITY (1,1) primary key, OperateDate datetime not null, OperateCode int, WorkFlow nvarchar (30) not null, CargoName nvarchar(10) not null, Quantity int, Weight float )go--查询用下面的存储过程 create proc p_qry as set nocount on declare @s nvarchar(4000) set @s='' select @s=@s+',['+rtrim(CargoName) +'_数量]=sum(case CargoName when '''+rtrim(CargoName) +''' then Quantity else 0 end),['+rtrim(CargoName) +'_重量]=sum(case CargoName when '''+rtrim(CargoName) +''' then Weight else 0 end)' from sampleTable group by CargoName exec('select OperateDate,OperateCode,WorkFlow'+@s+' from sampleTable group by OperateDate,OperateCode,WorkFlow') go
建表:
create table tablename
(
OperateDate datetime ,
lantaiID int,
WorkFlow nvarchar(30) ,
Quantity int,
Weight float,
)计算共发货:
select sum(weight) from tablename
(
lantaiID int IDENTITY (1,1) primary key,
OperateDate datetime not null,
OperateCode int,
WorkFlow nvarchar (30) not null,
CargoName nvarchar(10) not null,
Quantity int,
Weight float
)-------------------
"lantaiID int IDENTITY (1,1) primary key"这个字段好象对你实际需求没什么用处的吧?如果没用的话,对以后查询也没什么帮助的话,那就不用了~~
create table tablename
(
OperateDate datetime ,
lantaiID int,
WorkFlow nvarchar(30) ,
Quantity int,
Weight float,
)---创建视图:
create view viewname
as
select quantity*weight as totalweight from tablename--计算共发货:
select sum(totalweight) from viewname
(
OperateDate varchar(10) not null default convert(varchar(120),getdate(),120),
OperateCode varchar(10) not null,
WorkFlow varchar (30) not null,
CargoName varchar(10) not null,
Quantity float,
Weight float
)create clustered index IX_OperateCode on sampleTable (OperateCode)
日期 编号 流向 货物名 数量 重量 表内编号
::表内编号:是针对货物的,一张单子里有多种货物,将这些货物进行一个表内编号.标识有多少种货物.
表内编号在“单子”(你的编号)内要有唯一性,
可以用:编号+表内编号做联合主键,当然在操作时注意一下也可。建立你所需的报表时,根据“表内编号”来判定有多少种货物(需要多少个格)。计算一共发了多少时,可以忽略“表内编号”的存在,直接对"数量"进行sum就可
CREATE TABLE sampleTable
(
lantaiID int IDENTITY (1,1) primary key,
OperateDate datetime not null,
OperateCode int,
WorkFlow nvarchar (30) not null,
CargoName nvarchar(10) not null,
Quantity int,
Weight float
)go--查询用下面的存储过程
create proc p_qry
as
set nocount on
declare @s nvarchar(4000)
set @s=''
select @s=@s+',['+rtrim(CargoName)
+'_数量]=sum(case CargoName when '''+rtrim(CargoName)
+''' then Quantity else 0 end),['+rtrim(CargoName)
+'_重量]=sum(case CargoName when '''+rtrim(CargoName)
+''' then Weight else 0 end)'
from sampleTable group by CargoName
exec('select OperateDate,OperateCode,WorkFlow'+@s+'
from sampleTable
group by OperateDate,OperateCode,WorkFlow')
go