如何实现此存储过程

Question

有TSaleList 销售记录表 SaleListID,SaleCoding,ProductName,SaleNum,FactPrice,writedate,CostPrice,zhekou(折扣),SaleYongjin(销售佣金 比如20，指20%),有BT_PayMode 支付方式表 ID,Info(银行卡，现金，欠款，支票)有TCustomerInfo 客户表  CustomerID,CustomerName,Tel,AccountNo,FromSourceFromSource(字段) 1 代表 自有 2代表 代卖想实现存储过程 (要求价格保留两位小数)并可以根据条件参数 筛选 比如 日期 销售方式等销售编号SaleCoding ，货品名ProductName, 销售收入 shouru,销售方式 salemode,货品来源 fromsource，成本/收购成本 shina, 利润 lirun , 客户CustomerName，客户联系方式Tel记录:zy200712300038        手提包              320.00            银行卡           自有                 120                 200             (null)          (null)                       (自有库存)  
xs200712300038        挎包                500.00            银行卡           代卖                 400                 100             张三            13900000000                  (代卖张三的货)
.
.
.
.如何实现以上存储过程
代卖  shouru   FactPrice*ZheKou/10,         shina=SalePrice*(100-SaleYongjin)/100, lirun=(SaleNum*FactPrice*ZheKou/10-SalePrice*(100-SaleYongjin)/100)
自有  shouru   SaleNum*FactPrice*ZheKou/10, shina=CostPrice,                       lirun=(FactPrice*ZheKou/10-CostPrice)类似： 
SELECT SaleCoding,ProductName,SaleNum*FactPrice*ZheKou/10 as shouru,PreSalePayModeID,FromSource,SalePrice*(100-SaleYongjin)/100 as shina,(SaleNum*FactPrice*ZheKou/10-SalePrice*(100-SaleYongjin)/100) as lirun,JSCode FROM TSaleList where fromsource=2 
union SELECT SaleCoding,ProductName,FactPrice*ZheKou/10,PreSalePayModeID,FromSource,CostPrice,(FactPrice*ZheKou/10-CostPrice) as lirun,JSCode FROM TSaleList where fromsource=1

Answer 1

论坛有bug.
建议楼主用sql code格式发代码，这样会好看很多，否则真不好明白。

Answer 2

销售记录表: TSaleList
字段:     SaleListID,SaleCoding,ProductName,SaleNum,FactPrice,writedate,CostPrice,zhekou,SaleYongjin,PreSalePayModeID,CustomerID,FromSource
(其中FromSource字段   1=自有   2=代卖)支付方式表: BT_PayMode    
字段: 
 ID,Info   
(其中info字段包含内容 银行卡，现金，欠款，支票) 
 
客户表: TCustomerInfo  
字段:  
CustomerID,CustomerName,Tel,AccountNo
想实现存储过程 (价格保留两位小数) 结果如下销售编号(SaleCoding),货品名(ProductName),销售收入(shouru),销售方式(salemode),货品来源(fromsource),成本/收购成本(临时字段shina),利润(临时字段lirun),客户(CustomerName),客户联系方式(Tel) 记录结果: 
略存储过程存在以下条件
fromsource（字段）
如果代卖=2      shouru=FactPrice*ZheKou/10(收入)                 
              shina=SalePrice*(100-SaleYongjin)/100(货主实拿)   
              lirun=(SaleNum*FactPrice*ZheKou/10-SalePrice*(100-SaleYongjin)/100)(本店收入) 如果自有=1      shouru=SaleNum*FactPrice*ZheKou/10(收入)
              shina=CostPrice(本店成本)
              lirun=(FactPrice*ZheKou/10-CostPrice))(本店收入) 

Answer 3

/*有TSaleList   销售记录表   SaleListID,SaleCoding,ProductName,SaleNum,FactPrice,writedate,CostPrice,zhekou(折扣),SaleYongjin(销售佣金   比如20，指20%), 有BT_PayMode   支付方式表   ID,Info(银行卡，现金，欠款，支票) 有TCustomerInfo   客户表     CustomerID,CustomerName,Tel,AccountNo,FromSource FromSource(字段)   1   代表   自有   2代表   代卖 想实现存储过程   (要求价格保留两位小数) 并可以根据条件参数   筛选   比如   日期   销售方式等 销售编号SaleCoding   ，货品名ProductName,   销售收入   shouru,销售方式   salemode,货品来源   fromsource，成本/收购成本   shina,   利润   lirun   ,   客户CustomerName，客户联系方式Tel 记录: zy200712300038                 手提包                             320.00                         银行卡                       自有                                   120                                   200                           (null)                     (null)                                               (自有库存)     
xs200712300038                 挎包                                 500.00                         银行卡                       代卖                                   400                                   100                           张三                         13900000000                                     (代卖张三的货) 
. 
. 
. 
. 如何实现以上存储过程 
*/
--代卖     shouru       FactPrice*ZheKou/10,                   shina=SalePrice*(100-SaleYongjin)/100,   lirun=(SaleNum*FactPrice*ZheKou/10-SalePrice*(100-SaleYongjin)/100) 
--自有     shouru       SaleNum*FactPrice*ZheKou/10,   shina=CostPrice,                                               lirun=(FactPrice*ZheKou/10-CostPrice) --类似：   
SELECT   SaleCoding,ProductName,SaleNum*FactPrice*ZheKou/10   as   shouru,PreSalePayModeID,FromSource,SalePrice*(100-SaleYongjin)/100   as   shina,(SaleNum*FactPrice*ZheKou/10-SalePrice*(100-SaleYongjin)/100)   as   lirun,JSCode   FROM   TSaleList   where   fromsource=2   
union   SELECT   SaleCoding,ProductName,FactPrice*ZheKou/10,PreSalePayModeID,FromSource,CostPrice,(FactPrice*ZheKou/10-CostPrice)   as   lirun,JSCode   FROM   TSaleList   where   fromsource=1

Answer 4

以这个为准销售记录表:   TSaleList 
字段:           SaleListID,SaleCoding,ProductName,SaleNum,FactPrice,writedate,CostPrice,zhekou,SaleYongjin,PreSalePayModeID,CustomerID,FromSource 
(其中FromSource字段       1=自有       2=代卖) 支付方式表:   BT_PayMode         
字段:   
  ID,Info       
(其中info字段包含内容   银行卡，现金，欠款，支票)   
  
客户表:   TCustomerInfo     
字段:     
CustomerID,CustomerName,Tel,AccountNo 
想实现存储过程   (价格保留两位小数)   结果如下 销售编号(SaleCoding),货品名(ProductName),销售收入(shouru),销售方式(salemode),货品来源(fromsource),成本/收购成本(临时字段shina),利润(临时字段lirun),客户(CustomerName),客户联系方式(Tel)   记录结果:   
略 存储过程存在以下条件 
fromsource（字段） 
如果代卖=2             shouru=FactPrice*ZheKou/10(收入)                                   
                            shina=SalePrice*(100-SaleYongjin)/100(货主实拿)       
                            lirun=(SaleNum*FactPrice*ZheKou/10-SalePrice*(100-SaleYongjin)/100)(本店收入)   如果自有=1             shouru=SaleNum*FactPrice*ZheKou/10(收入) 
                            shina=CostPrice(本店成本) 
                            lirun=(FactPrice*ZheKou/10-CostPrice))(本店收入)   

Answer 5

销售记录表:       TSaleList   
字段:                       SaleListID,SaleCoding,ProductName,SaleNum,FactPrice,writedate,CostPrice,zhekou,SaleYongjin,PreSalePayModeID,CustomerID,FromSource   
(其中FromSource字段               1=自有               2=代卖)   支付方式表:       BT_PayMode                   
字段:       
    ID,Info               
(其中info字段包含内容       银行卡，现金，欠款，支票)       
    
客户表:       TCustomerInfo           
字段:           
CustomerID,CustomerName,Tel,AccountNo   销售记录表 中的PreSalePayModeID和 支付方式表中ID对应
销售记录表 中的CustomerID和 客户表中CustomerID对应各位帮忙呵