自己容易犯错误

来源：互联网发布：淘宝怎么刷上去编辑：程序博客网时间：2024/04/27 20:33

//input 明细
SELECT PARTSINPUTBODY.BILLCODE, PARTSINPUTBODY.PARTNAME,
      PARTSINPUTBODY.PARTCODE, PARTSINPUTHAND.PROVIDER,
      PARTSINPUTHAND.BILLDATE, PARTSINPUTHAND.TOTALSUM,
      PARTSINPUTBODY.QUANTITY
FROM PARTSINPUTHAND INNER JOIN
      PARTSINPUTBODY ON
      PARTSINPUTHAND.BILLCODE = PARTSINPUTBODY.BILLCODE
WHERE (PARTSINPUTBODY.PARTCODE LIKE '01002%') AND
      (PARTSINPUTHAND.BILLDATE BETWEEN '2004-12-01' AND '2004-12-31')

//output 明细
SELECT PARTSOUTPUTHAND.BILLCODE, PARTSOUTPUTHAND.CLIENTNAME,
      PARTSOUTPUTBODY.PARTNAME, PARTSOUTPUTBODY.PARTCODE,
      PARTSOUTPUTHAND.BILLDATE, PARTSOUTPUTBODY.TOTALSUM
FROM PARTSOUTPUTBODY INNER JOIN
      PARTSOUTPUTHAND ON
      PARTSOUTPUTBODY.BILLCODE = PARTSOUTPUTHAND.BILLCODE
//********************
//小计某商品的金额INPUT
SELECT SUM(PARTSINPUTBODY.TOTALSUM) AS Expr1,
      PARTSINPUTBODY.PARTNAME, PARTSINPUTBODY.PARTCODE
FROM PARTSINPUTBODY CROSS JOIN
      PARTSINPUTHAND
WHERE (PARTSINPUTBODY.PARTCODE LIKE '01%')
GROUP BY PARTSINPUTBODY.PARTNAME, PARTSINPUTBODY.PARTCODE

//小计某类商品的金额(INPUT)
SELECT SUM(E) AS Expr1
FROM (SELECT SUM(PARTSINPUTBODY.TOTALSUM) AS E,
              PARTSINPUTBODY.PARTNAME, PARTSINPUTBODY.PARTCODE
        FROM PARTSINPUTBODY CROSS JOIN
              PARTSINPUTHAND
        WHERE (PARTSINPUTBODY.PARTCODE LIKE '01%')
        GROUP BY PARTSINPUTBODY.PARTNAME,
              PARTSINPUTBODY.PARTCODE) DERIVEDTBL
//*******************************
//小计某商品的金额(OUTPUT)
SELECT SUM(PARTSOUTPUTBODY.TOTALSUM) AS Expr1,
      PARTSOUTPUTBODY.PARTNAME, PARTSOUTPUTBODY.PARTCODE
FROM PARTSOUTPUTBODY CROSS JOIN
      PARTSOUTPUTHAND
where PARTSOUTPUTBODY.PARTCODE like '01%'
GROUP BY PARTSOUTPUTBODY.PARTNAME, PARTSOUTPUTBODY.PARTCODE

//小计某类商品的金额(OUTPUT)
SELECT SUM(E) AS Expr1
FROM (SELECT SUM(PARTSINPUTBODY.TOTALSUM) AS E,
              PARTSINPUTBODY.PARTNAME, PARTSINPUTBODY.PARTCODE
        FROM PARTSINPUTBODY CROSS JOIN
              PARTSINPUTHAND
        WHERE (PARTSINPUTBODY.PARTCODE LIKE '01%')
        GROUP BY PARTSINPUTBODY.PARTNAME,
              PARTSINPUTBODY.PARTCODE) DERIVEDTBL

//****************************************************

WHERE (onlyid NOT IN
(SELECT Onlyid
FROM customer))

private
    { Private declarations }
   // FId:string;
public
    { Public declarations }
    property Id read Fid write Fid ;

CREATE PROCEDURE sp_operator
@name varchar(30),
@Department varchar(10)
AS
select *
from operator
where name=@name and Department=@Department
Parameters.CreateParameter(Name,DataType,Direction,Size,Value)

tmpsp.Parameters.CreateParameter('@jgfl',ftstring,pdinput,12,EditText);
par.value

pre
execproc

//ParamCheck := False;
   Parameters.Clear;
   //Parameters.createParameter('@BgnTime',ftdate,pdinput,10,now);
   //Parameters.createParameter('@EndTime',ftdate,pdinput,10,now);

with MyDM.ADOSP do
begin
    ProcedureName := 'sp_spaceused'; // 设置存储过程名
    Close;   // 关闭以前的数据集
    Parameters.Clear; // 清空以前的参数
    Parameters.CreateParameter('@objname',ftString,pdInput,100,'table1'); //生成新的参数
    Open;// 或者ExecProc,不返回数据集
end;

错误:parameter 无法从子选择的查询语句中获得在准备命令前请设置参数信息
function TFrmQueryX.getinfoname(id:string):string;
var
TmpQuery:Tadoquery;
sT:string;
begin
     TmpQuery:=Tadoquery.Create(self);
    with TmpQuery do
    begin
       close;
       ConnectionString:=OceanQueryXpartsinput.ConnectionString;
       sql.Clear;
       sql.Add('select name from partinfo where code='+QuotedStr(FId));
       open ;//exe error
       sT:=fieldbyname('name').Value;
       free;
    end;
    Result:=sT;
end;

库存统计表
序号项目名称上月结存本月入库本月领用本月库存

上月结存=上月库存量-上月领用量 [月份不同]
本月库存=本月入库-本月领用

假设现在是 12月商品名是A
上月结存应该是 11月份的结存
select partinputbody.quantity

SELECT PARTSINPUTBODY.PARTNAME AS 商品名称,
      PARTSINPUTBODY.PARTCODE AS 商品编码, PARTSINPUTHAND.PROVIDER,
      PARTSINPUTHAND.BILLDATE, PARTSINPUTHAND.TOTALSUM,
      PARTSINPUTBODY.QUANTITY AS 十一月入库量
FROM PARTSINPUTHAND INNER JOIN
      PARTSINPUTBODY ON
      PARTSINPUTHAND.BILLCODE = PARTSINPUTBODY.BILLCODE
WHERE (PARTSINPUTHAND.BILLDATE BETWEEN '2004-12-01' AND '2004-12-31')

SELECT PARTSOUTPUTBODY.PARTNAME AS 商品名称,
      PARTSOUTPUTBODY.PARTCODE AS 商品编码,
      PARTSOUTPUTHAND.BILLDATE, PARTSOUTPUTHAND.TOTALSUM,
      PARTSOUTPUTBODY.QUANTITY AS 十一月领用量,
      PARTSOUTPUTHAND.CLIENTNAME
FROM PARTSOUTPUTHAND INNER JOIN
      PARTSOUTPUTBODY ON
      PARTSOUTPUTHAND.BILLCODE = PARTSOUTPUTBODY.BILLCODE
WHERE (PARTSOUTPUTHAND.BILLDATE BETWEEN '2004-12-01' AND '2004-12-31')

SELECT VIEW1.十一月入库量 - VIEW2.十一月领用量 AS 十一月结存,
VIEW2.十一月领用量, VIEW1.十一月入库量
FROM VIEW1 INNER JOIN
VIEW2 ON VIEW1.十一月入库量 > VIEW2.十一月领用量

//***************
SELECT a.入库量 - b.领用量 AS 本月结存, b.领用量,
      a.入库量
FROM (SELECT PARTSINPUTBODY.PARTNAME, PARTSINPUTBODY.PARTCODE,
              PARTSINPUTHAND.PROVIDER, PARTSINPUTHAND.BILLDATE,
              PARTSINPUTHAND.TOTALSUM,
              PARTSINPUTBODY.QUANTITY AS 入库量
        FROM PARTSINPUTHAND INNER JOIN
              PARTSINPUTBODY ON
              PARTSINPUTHAND.BILLCODE = PARTSINPUTBODY.BILLCODE
        WHERE (PARTSINPUTHAND.BILLDATE BETWEEN @MonthDateBegin AND
              @MonthDateEnd)) a CROSS JOIN
          (SELECT PARTSOUTPUTBODY.PARTNAME,
               PARTSOUTPUTBODY.PARTCODE, PARTSOUTPUTHAND.BILLDATE,
               PARTSOUTPUTHAND.TOTALSUM,
               PARTSOUTPUTBODY.QUANTITY AS 领用量,
               PARTSOUTPUTHAND.CLIENTNAME
         FROM PARTSOUTPUTHAND INNER JOIN
               PARTSOUTPUTBODY ON
               PARTSOUTPUTHAND.BILLCODE = PARTSOUTPUTBODY.BILLCODE
         WHERE (PARTSINPUTHAND.BILLDATE BETWEEN @MonthDateBegin AND
              @MonthDateEnd)) b
WHERE (a.PARTCODE = '01001')

////某商品或某类商品月统计含每次操作数量
SELECT a.入库量 - b.领用量 AS 本月结存, b.领用量, a.入库量
FROM (SELECT PARTSINPUTBODY.PARTNAME, PARTSINPUTBODY.PARTCODE,
              PARTSINPUTHAND.PROVIDER, PARTSINPUTHAND.BILLDATE,
              PARTSINPUTHAND.TOTALSUM,
              PARTSINPUTBODY.QUANTITY AS 入库量
        FROM PARTSINPUTHAND INNER JOIN
              PARTSINPUTBODY ON
              PARTSINPUTHAND.BILLCODE = PARTSINPUTBODY.BILLCODE
        WHERE (PARTSINPUTHAND.BILLDATE BETWEEN '2004-12-01' AND '2004-12-31'))
      a INNER JOIN
          (SELECT PARTSOUTPUTBODY.PARTNAME, PARTSOUTPUTBODY.PARTCODE,
               PARTSOUTPUTHAND.BILLDATE, PARTSOUTPUTHAND.TOTALSUM,
               PARTSOUTPUTBODY.QUANTITY AS 领用量,
               PARTSOUTPUTHAND.CLIENTNAME
         FROM PARTSOUTPUTHAND INNER JOIN
               PARTSOUTPUTBODY ON
               PARTSOUTPUTHAND.BILLCODE = PARTSOUTPUTBODY.BILLCODE
         WHERE (PARTSoutPUTHAND.BILLDATE BETWEEN '2004-12-01' AND '2004-12-31'))
      b ON a.入库量 > b.领用量
WHERE (a.PARTCODE = '01001')

//某商品或某类商品月统计小计

SELECT a.PARTNAME, SUM(a.入库量 - b.领用量) AS 本月结存, SUM(b.领用量) AS 本月领用量,
      SUM(a.入库量) AS 本月入库量
FROM (SELECT PARTSINPUTBODY.PARTNAME, PARTSINPUTBODY.PARTCODE,
              PARTSINPUTHAND.PROVIDER, PARTSINPUTHAND.BILLDATE,
              PARTSINPUTHAND.TOTALSUM,
              PARTSINPUTBODY.QUANTITY AS 入库量
        FROM PARTSINPUTHAND INNER JOIN
              PARTSINPUTBODY ON
              PARTSINPUTHAND.BILLCODE = PARTSINPUTBODY.BILLCODE
        WHERE (PARTSINPUTHAND.BILLDATE BETWEEN '2004-12-01' AND '2004-12-31'))
      a INNER JOIN
          (SELECT PARTSOUTPUTBODY.PARTNAME, PARTSOUTPUTBODY.PARTCODE,
               PARTSOUTPUTHAND.BILLDATE, PARTSOUTPUTHAND.TOTALSUM,
               PARTSOUTPUTBODY.QUANTITY AS 领用量,
               PARTSOUTPUTHAND.CLIENTNAME
         FROM PARTSOUTPUTHAND INNER JOIN
               PARTSOUTPUTBODY ON
               PARTSOUTPUTHAND.BILLCODE = PARTSOUTPUTBODY.BILLCODE
         WHERE (PARTSoutPUTHAND.BILLDATE BETWEEN '2004-12-01' AND '2004-12-31'))
      b ON a.入库量 > b.领用量
WHERE (a.PARTCODE LIKE '01001%')
GROUP BY a.PARTNAME
//***************

引用 fastreport 设计器
uses
FR_Class, FR_DSet, FR_DBSet, Db, DBTables, FR_Chart,FR_Desgn;
.
frReport1.DesignReport ;

//某商品当前获得审批但是没有入库
SELECT PARTCODE, PARTNAME
FROM dbo.STOCKBILLBODY
WHERE (PARTCODE NOT IN
          (SELECT partcode
         FROM partsinputbody INNER JOIN
               partsinputhand ON partsinputbody.billcode = partsinputhand.billcode
         WHERE partsinputhand.billdate BETWEEN '2004-12-01' AND '2004-12-31'))

//某商品当前获得审批库存有/库存没有没有领用
SELECT PARTCODE, PARTNAME
FROM dbo.STOCKBILLBODY
WHERE (PARTCODE NOT IN
          (SELECT partcode
         FROM partsoutputbody INNER JOIN
               partsoutputhand ON partsoutputbody.billcode = partsoutputhand.billcode
         WHERE partsoutputhand.billdate BETWEEN '2004-12-01' AND '2004-12-31'))

//(_'
--0-09000000000000000000000000000000000000000000000000000000000000000000000000
---- 要创建向数据表中输入数据的存储过程，代码编写举例如下：

CREATE PROC ProcTest1
@inttest smallint
,@strtest char(4) output
AS
INSERT INTO TEST_User.TRANS_TEST
valueS(@inttest,@strtest)
SELECT @strtest,* FROM TEST_User.TRANS_TEST
---- 以上代码向数据表 TEST_User.TRANS_TEST 添加数据，该数据表包括两个字段：第一个字段为 smallint 类型的数据，参数类型定为 Input(在存储过程中可缺省)；第二个字段数据为长度为4 的字符类型，参数类型为 Output。

---- 其模块程序的编写与 DataInsert 过程相似，区别主要在于模块的中间部分，对应的主要代码如下：

StoredProc1.Close;
{关闭存储过程}
StoredProc1.Params[1].ParamType := ptInput;
{设置存储过程的参数类型}
StoredProc1.Params[1].AsInteger := 1;
{设置存储过程中参数的数据}
StoredProc1.Params[2].ParamType := ptOutput;
StoredProc1.Params[2].AsString := ’abcd’;
StoredProc1.Open; {打开存储过程}
//0-9-090