我有一个数据表如下:
name sum no data
a 10 1 2003-2-1
b 12 1 2003-2-1
c 12 1 2003-2-1
a 10 2 2003-2-1
b 12 2 2003-2-1
c 12 2 2003-2-1
a 10 3 2003-2-1
b 12 3 2003-2-1
c 12 3 2003-2-1
a 10 2 2003-2-2
b 12 2 2003-2-2
c 12 2 2003-2-2
我要显示如下:
name 1 2 3 data
a 10 10 10 2003-2-1
b 12 12 12 2003-2-1
c 12 12 12 2003-2-1
a 0 10 0 2003-2-2
b 0 12 0 2003-2-2
c 0 12 0 2003-2-2
name sum no data
a 10 1 2003-2-1
b 12 1 2003-2-1
c 12 1 2003-2-1
a 10 2 2003-2-1
b 12 2 2003-2-1
c 12 2 2003-2-1
a 10 3 2003-2-1
b 12 3 2003-2-1
c 12 3 2003-2-1
a 10 2 2003-2-2
b 12 2 2003-2-2
c 12 2 2003-2-2
我要显示如下:
name 1 2 3 data
a 10 10 10 2003-2-1
b 12 12 12 2003-2-1
c 12 12 12 2003-2-1
a 0 10 0 2003-2-2
b 0 12 0 2003-2-2
c 0 12 0 2003-2-2
数据库的格式是
名称 数量 仓库 日期
a 10 1 2003-2-1
b 20 1 2003-2-1
a 12 2 2003-2-1
报表的格式是
名称 仓库1 仓库2 日期
a 10 12 2003-2-1
b 20 0 2003-2-1
应该是下面三种之一吧:)Good LuckORACLE:
select name,
sum(decode(no,1,0,sum,0) as 1,
sum(decode(no,2,0,sum,0) as 2,
sum(decode(no,3,0,sum,0) as 3,
data
from table group by name,data
sqlserver:
select name,
sum(case no when 1 then sum else 0 end) as 1,
sum(case no when 2 then sum else 0 end) as 2,
sum(case no when 3 then sum else 0 end) as 3,
data
from table group by name,data
DB2:
select name,
sum(case no when '1' then sum else 0) as 1,
sum(case no when '2' then sum else 0) as 2,
sum(case no when '3' then sum else 0) as 3,
data
from table group by name,data
写一个过程程序,将数据送到一个clientdataset上,clientdataset用*.cds文件字段定义为name 1 2 3 data,
table1->first()
while(!table1->eof)
{
if(!clientdataset->locate(name,table->fieldbyname("name")->AsString))
{
clientdataset->appen();
}
else
clientdataset->edit();
if(table1->fieldbyname("no")->Asinteger==1)
clientdataset->fieldbyname("1")->AsInteger=table1->fieldbyname("sum")->Asinteger;
if(table1->fieldbyname("no")->Asinteger==2)
clientdataset->fieldbyname("2")->AsInteger=table1->fieldbyname("sum")->Asinteger;
if(table1->fieldbyname("no")->Asinteger==3)
clientdataset->fieldbyname("3")->AsInteger=table1->fieldbyname("sum")->Asinteger;
table1->next();
}然后用clientdataset打印ok
1 select name,no,date,sum(sum) from table group by name,no,date将数据汇总、简化;
2 select distinct no from table 得到不同NO的个数;
3 创建临时表,包括字段name,date,no1,no2,no3... 其中no字段的个数由第2步获得。你也可以先创建好,只要预留足够的no字段即可,这样可减少代码;
4 将1中的数据转填入3中,掌握规律即可
1 因为name,no,data唯一,固第一步后数据为原样(估计你的本意不是这样,而是要处理大批量数据)
2 第二步的到NO为3个:1,2,3
3 临时表为name,data,no1,no2,no3
4 填写规则:
临时表 原表
name data no1 no2 no3 name sum no data
a 2003-2-1 10 10 a 10 1 2003-2-1 将sum值填到no值对应的字段no1
b 2003-2-1 12 b 12 1 2003-2-1 将sum值填到no值对应的字段no1
c 2003-2-1 12 c 12 1 2003-2-1 将sum值填到no值对应的字段no1
a 10 2 2003-2-1 将sum值填到no值对应的字段no2
注意临时表中name+data唯一
....
table->table1
name->name1
sum->sum1
no->no1
data->data1把下面放入query中,
select
name1,
sum(decode(no1,'1',sum1,0)) "1",
sum(decode(no1,'2',sum1,0)) "2",
sum(decode(no1,'3',sum1,0)) "3",
data1
from table1
group by data1,name1;