SQL365-报表技术

1.

2017.2.14

ZWPZK

账务凭证库

ZWPZFL

账务凭证分录

1.按单位、年度、期间、凭证编码提取数据
SELECT * FROM ZWPZFL2016 INNER JOIN (SELECT * FROM ZWPZK2016 WHERE zwpzk_dwbh = 'XX076' and zwpzk_pzbh = '记0008' and zwpzk_kjqj = '10')T ON   T.ZWPZK_PZNM = ZWPZFL2016.ZWPZFL_PZNM AND T.ZWPZK_DWBH = ZWPZFL2016.ZWPZFL_DWBH

 

2.

 2017.2.15

ZWKMYE

账务科目余额表

2.提取某些科目的月末余额

SELECT sum(zwkmye_wbncye) FROM ZWKMYE2016 where (ZWKMYE_KMBH LIKE '1002' or ZWKMYE_KMBH LIKE '1001' or ZWKMYE_KMBH LIKE '1015'  ) AND ZWKMYE_DWBh = 'XX000' AND ZWKMYE_KJQJ = '01'

 

3.

2017.2.16

指定单位年度月份凭证号提取凭证数据

ZWPZK@YR@	账务凭证库
ZWPZFL@YR@	账务凭证分录
ZWFZYS@YR@	账务辅助原始

1.到凭证库去找凭证编号

Select * from zwpzk2016

Zwpzk_dwbh  zwpzk_pznm  zwpzk_kjqj zwpzk_pzbh

Xx006 280    02  记0008

2.到凭证分录表寻找借贷方数额

 

select * from ZWPZfl2016 where zwpzfl_dwbh= ' Xx001' and zwpzfl_pznm = '27' 

 获取到分录内码

3.通过pznm flnm获取辅助数据

Zwpzfl_flnm

select *from ZWfzys2016where zwfzys_dwbh ='xx006'and zwfzys_pznm ='27'   and zwfzys_flnm ='66'

 

 

4.

2017.2.17

资产负债表指定项目数据处理（c#）

使用Z2016表

 

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.OracleClient;

public partial class _Default : System.Web.UI.Page
{

    protected string aaa = "1234";
   protected  decimal hbzj1,hbzj0;
   protected decimal yszk1,yszk0;

    protected void Page_Load(object sender, EventArgs e)
    {
        string connectionString;
        string queryString;

        connectionString = "Data Source=1****;User ID=*****8;PassWord=8*888";

          queryString = "select  ZWSSKMYE_KMBH, zwSSKMYE_wbdqye, zwSSKMYE_wbncye from ZWSSKMYE2016 where zwSSKMYE_dwbh = '4' and  ZWssKMYE_KJQJ = '01' ";
        OracleConnection myConnection = new OracleConnection(connectionString);

        OracleCommand myORACCommand = myConnection.CreateCommand();

        myORACCommand.CommandText = queryString;

        myConnection.Open();

        OracleDataReader myDataReader = myORACCommand.ExecuteReader();

       
        while (myDataReader.Read())
        {
          

            string stu = myDataReader.GetValue(0).ToString();
            string stu1 = myDataReader.GetValue(1).ToString();
            string stu0 = myDataReader.GetValue(2).ToString();
            decimal xx1 = Convert.ToDecimal(stu1);//
            decimal xx0 = Convert.ToDecimal(stu0);//
            if (stu == "1002" || stu == "1001" || stu == "1015")
            {
                hbzj1 = hbzj1 + xx1;
                hbzj0 = hbzj0 + xx0;
            }

            if (stu == "1122" )
            {
                yszk1 = yszk1 + xx1;
                yszk0 = yszk0 + xx0;
            }

    

               ;

        }
      

        myDataReader.Close();

        myConnection.Close();
    }
}

 

5.

1017.2.22

创建试图

例句 1 CREATE VIEW V_Customer
AS SELECT First_Name, Last_Name, Country
FROM Customer

例句 2

创建：
create view 视图名
as
select.....(SQL查询语句)

修改：
alter view 视图名
其他同创建

删除：
Drop view 视图名

 

6.建表

create table product
(
proid int primary key identity(1,1),
proname varchar(64) ,
proprice varchar(64) ,
proaddress varchar(64) ,
proimage varchar(64) ,
)

 

插入数据

INSERT INTO product VALUES(  'name02','price02','address02','age02')