包含createtablein的词条
如何将计算结果作为新的计算的初始条件
需要用到两个技巧，1、对null的处理可以使用isnull(值,0）的方式来处理，
比如:
select isnull(收入,0) 当收入为null时将计为0
2、用自定义函数处理，可能更简单
3、以下是写的整个测试实现pre t="code" l="sql"--1、创建测试表，有序列的ID是很重要的。
create table InOutDetail(inmoney money ,outmoney money,id int identity )
go
--2、插入测试数据
insert into InOutDetail(InMoney )
values(1000)
insert into InOutDetail(InMoney )
values(500)
insert into InOutDetail(outMoney )
values(600)
insert into InOutDetail(outMoney )
values(800)
insert into InOutDetail(InMoney )
values(2000)
insert into InOutDetail(outMoney )
values(300)
Go
--3、检查表中的数据
select * from InOutDetail
GO
--4、创建值函数
Create function fn_GetSum(@id int)
returns money
as
begin
declare @sum money
select @sum=sum(isnull(inmoney,0)) -sum(isnull(outmoney,0))
from InOutDetail
where idlt;=@id
return @sum
end
GO
--5、检测结果，以后直接调用函数就可以得出结果
select inmoney,OutMoney ,dbo.fn_GetSum(id) as 余额 from InOutDetail
Go
--6、删除测试数据
drop function fn_GetSum
drop table InOutDetail
oracle调用存储过程出错 "N" 标识符无效 在create table invbasdoc 这行出错 求救
将所有的双引号替换成2个单引号即可
create or replace procedure wym
authid current_user
is
vn_ctn number;
begin
select count(*) into vn_ctn from user_all_tables a where a.table_name like upper('invbasdoc');
if vn_ctn 0 then
execute immediate 'drop table invbasdoc';
end if;
execute immediate 'create table invbasdoc as
(select * from (select qii.inspectinstitutionpid,qii.inspectinstitutionName,
count(*) as SAMPLID,
sum(case when qcr.result in(''A'') then 1 else 0 end) ''RESULT_A'', --结论
sum(case when qcr.RESULT in(''B'') then 1 else 0 end) ''RESULT_B'',
sum(case when qcr.RESULT in(''D'') then 1 else 0 end) ''RESULT_D'',
sum(case when qcr.RESULT in(''C'') then 1 else 0 end) ''RESULT_C'',
sum(case when qcr.processstate in(''N'') then 1 else 0 end) ''PROCESSSTATE'',--曲线比对异常
sum(case when qcr.experimentationstate in(''N'') then 1 else 0 end) ''EXPERIMENTATIONSTATE'', --比对结果异常
sum(case when qcr.echosampleid is not null then 1 else 0 end) ''ECHOSAMPLEID_TRUE'', --复检的样品编号_是
sum(case when qcr.echosampleid is null then 1 else 0 end) ''NOHAVEECHO_FALSE'',--复检的样品编号_否
sum(case when qcr.realtimestate in(''N'') then 1 else 0 end) ''REALTIMESTATE'',--是否实时传输状态
sum(case when qcr.modifystate in(''Y'') then 1 else 0 end) ''MODIFYSTATE'', --数据被修改
sum(case when qcr.collectionstate in(''N'') then 1 else 0 end) ''COLLECTIONSTATE'' --未采集曲线数据
from report qcr
inner join compact jc on qcr.compactpid = jc.compactpid
inner join inspectinstitution qii on jc.districtid = qii.jcjgid
group by qii.inspectinstitutionpid,qii.inspectinstitutionName
))';
end ;
自定义Dataset问题
新建一 DataSet , 并加入数据 : prepareTable
function TFrm_Print.CreateTableInMemory(const AFieldDefs:TFieldDefs):TDataSet;
var
TempTable:TClientDataSet;
begin
TempTable:=nil;
Result:=nil;
if AFieldDefsnil then
begin
try
TempTable:=TClientDataSet.Create(Application);
TempTable.FieldDefs.Assign(AFieldDefs);
TempTable.CreateDataSet;
Result:=(TempTable as TDataSet);
Except
if TempTablenil then
TempTable.Free;
raise;
end
end;
end;
procedure TFrm_Print.CreateDataSet(var gDataSet:Tdataset);
var ADataSet:TDataSet;
begin
try
if assigned(gDataSet) then gDataSet.free;
except
end;
ADataSet:=TDataSet.Create(Self);
try
with ADataSet.FieldDefs do
begin
add('OrderNo',ftInteger);
add('prjName',ftString,200);
add('srcPrice',ftFloat);
end;
gDataSet:=CreateTableInMemory(ADataSet.FieldDefs);
gDataSet.Open;
finally
ADataSet.Free;
end;
end;
procedure Tfrm_GoodsMg.prepareTable(var gDataSet:Tdataset);
procedure prepareDataSet;
begin
{ while not 数据源.eof do begin
gDataSet.append;
gDataSet.fieldbyname('OrderNo').asInteger:= I_Value;
gDataSet.fieldbyname('prjName').asString := A_Value;
gDataSet.fieldbyname('OrderNo').asFloat := D_value;
gDataSet.post;
数据源.next;
end; }
end;
begin
CreateDataSet(gDataSet);
prepareDataSet;
end;
procedure getMyDataSet()
var myDataSet:Tdataset
begin
prepareTable(myDataSet);
myDataSet.free;
end;