Database Single Table association implements data count function (table itself association)

Database Single Table association implements data count function (table itself association)

Note: This blog post for the original blogger, please indicate the source of reproduced.

　　　　question : In the database there is such a table, which mainly contains three fields, GOODSID (cargo number), TypeId (type number), State (status). The cargo number is not duplicated, goodsid and typeid belong to N to 1 relationship, State is mainly divided into two kinds of "null" and "1", "null" means that the goods are not sold unsold, "1" means the goods sold. Now has the following business, statistics out the type number under how many goods sold, how many goods unsold.

The table data in the database is as follows:

The result of the last statistic (output) is:

　　　　problem Analysis : The key points to be used in the above questions are the table itself association, the count function of the counting method, and the group by. In this paper, according to the question, we need to use the table connection method for the inner join (inner join). The INNER JOIN keyword returns a row because there is at least one match in the table.

　　　　Problem Solving : According to the problem described, as well as the analysis of the problem, the following SQL was designed.

1 SelectG1. TypeId,Count(*) asSold,g3. Unsold fromGoods G12 Inner Join 3(SelectTypeId,Count(*) asUnsold fromGoods G2WHEREState='NULL'4 Group  byG2. TYPEID) G35  onG1. TypeId=G3. TypeId6 WHEREState='1' Group  byG1. Typeid,g3. Unsold

　　　　Accessories : In order to let everyone better to practice, Bo Master will build the statement and INSERT statements posted. For everyone to learn.

1 --build a Table statement2 CREATE TABLEGoods (3GoodsidVARCHAR(255),4TypeIdVARCHAR(255),5StateVARCHAR(255)6 );7 8 ---INSERT Statement9 Insert  intoGoods (Goodsid,typeid,state)Values('001','1','1');Ten Insert  intoGoods (Goodsid,typeid,state)Values('002','1','NULL') One Insert  intoGoods (Goodsid,typeid,state)Values('003','1','NULL'); A Insert  intoGoods (Goodsid,typeid,state)Values('004','1','NULL'); - Insert  intoGoods (Goodsid,typeid,state)Values('005','1','NULL'); - Insert  intoGoods (Goodsid,typeid,state)Values('006','1','1'); the Insert  intoGoods (Goodsid,typeid,state)Values('001','2','1'); - Insert  intoGoods (Goodsid,typeid,state)Values('002','2','NULL'); - Insert  intoGoods (Goodsid,typeid,state)Values('003','2','NULL'); - Insert  intoGoods (Goodsid,typeid,state)Values('004','2','1'); + Insert  intoGoods (Goodsid,typeid,state)Values('005','2','NULL'); - Insert  intoGoods (Goodsid,typeid,state)Values('006','2','NULL'); + Insert  intoGoods (Goodsid,typeid,state)Values('007','2','1'); A Insert  intoGoods (Goodsid,typeid,state)Values('008','2','NULL'); at Insert  intoGoods (Goodsid,typeid,state)Values('001','3','1'); - Insert  intoGoods (Goodsid,typeid,state)Values('002','3','NULL'); - Insert  intoGoods (Goodsid,typeid,state)Values('003','3','NULL');

Database Single Table association implements data count function (table itself association)