SQL Server 2012 Stores temporary tables for group statistics results in reverse order to new tables

Program Pre-Description:

The database accessed by this article is based on Localyago Modified library of Open source database with RDF ternary group, there is only one table, there are five attributes in the table: Subject subject, predicate predict, Object object, subject's number SubID, object's number ObjID. Each record is made up of (Subject,predict,object,subid,objid). Where the object is a character type rather than an entity (such as "2011"), the number defaults to 0. The following data tables are available:

　　

Program Requirements:

1. Count the number of related predicate attributes per subject (each predicate can have multiple objects), that is, how many records with that entity as the subject
2. Because the results are huge, you need to store the results in a new table in the database and store them in reverse order

Specific process:

1. Count the number of predicates per subject, output (subject, subject ID and number of subject attributes), output in reverse order of the number of attributes per subject:

 select  subject, SubID, count  (subject ) as   Subprenum  from  [ localyago   ]. [ dbo  ]  . [ yago  ]  group  by   Subject,subid  order  by  subprenum desc ; 

Group by implements grouping statistics, which are categorized by subject, because we select subject and SubID in the Select, and the fields in select are either contained in the group BY statement or are included in the aggregate function, so our group here By also write to SubID, otherwise it will error

Order BY is sort, default from small to large output, plus desc becomes reverse, from large to small output

The following results are obtained:

2. Subprenum The temporary table of query results into a new table in reverse order for easy storage and querying

The most basic way:

If the new table does not exist

Select *  into  from old table

If the new table does not exist

Insert  into Select *  from old table

Thus there are:

 select  subject, SubID, count  (subject ) as   Subprenum  into   Localyago.dbo.subpre  from  [ localyago   ]. [ dbo  ]  . [ yago  ]  group  by   subject,subid;  order  by  subprenum; 

We then found that although the result of our previous query was orderly, the new table generated after the run was not sorted by Subprenum and the order was chaotic.

It was discovered that this was due to the limitations of SQL Server itself and that if there was a special need to require the data in the staging table to be ordered, the problem could be solved by "creating a clustered Index". For details, please refer to the blog: https://www.cnblogs.com/kerrycode/p/5172333.html

The code is thus improved as follows:

SelectSubject, SubID,Count(subject) asSubprenum intoLocalyago.dbo.subpre from [Localyago].[dbo].[Yago]where 1=0Group  bySubject,subid;Create Clustered IndexInx_subpre onLocalyago.dbo.subpre (SubprenumDESC);--Create a clustered index, sorted in reverse order by SubprenumInsert  intoLocalyago.dbo.subpreSelectSubject, SubID,Count(subject) asSubprenum from [Localyago].[dbo].[Yago]    whereSubID!=0    Group  bySubject,subidOrder  bySubprenum;

The records in the new table Subpre after this run are sorted in reverse order of subprenum.

As follows:

SQL Server 2012 Stores temporary tables for group statistics results in reverse order to new tables