Select into structure uses

Data tables stored in different kinds of content, with Type1, Type2 columns of different values to distinguish, if we want to query the number of types of data, but also in the form of rows, only a little less than group, just learn the select into the structure, share. Data Table Description

To the city table:

Counties table:

Store Information table:

Storetype Store Type: Proprietary shop 601, franchisee 602
Storerank Store Level: Level 701, Level two store 702
In Hebei province, for example, the Sys_city table in Hebei Save 11 of the city, Sys_county table in each of the city under each of the 3 counties and cities to check the number of different types of shops at different levels

Select Citycode,cityname into #city the from sys_city where Provincecode = 10, which means that the content that is queried from the Sys_city table is inserted into a temporary table named #city. Note The table name cannot be duplicated.
ISNULL (#c1. c,0) indicates that if the #c1.c value is null, it returns 0 because the number cannot be null to calculate the total, so the conversion is done.
The number of stores in a certain city, the smallest grouping category is the city, so query from the #city table, and then connect the other tables outside.

Select Citycode,cityname into #city from sys_city where provincecode = 10; /* #c1中存储所有地区的自营一级店的数量 */select Citycode,count (1) as C into #c1 from dbo.
Storeinfo where storetype=601 and storerank=701/* self-level store/group by Citycode; /* #c2中存储所有地区的自营二级店的数量 */select Citycode,count (1) as C into #c2 from dbo.
Storeinfo where storetype=601 and storerank=702/* Self Level Two store/group by Citycode; /* #c3中存储所有地区的加盟一级店的数量 */select Citycode,count (1) as C into #c3 from dbo.
Storeinfo where storetype=602 and storerank=701/* Join the first level shop/group by Citycode; /* #c4中存储所有地区的加盟二级店的数量 */select Citycode,count (1) as C into #c4 from dbo.
Storeinfo where storetype=602 and storerank=702/* joined Level two store/group by Citycode;
       Select CityName, ISNULL (#c1. c,0) as Count1, ISNULL (#c2. c,0) as Count2, ISNULL (#c3. c,0) as Count3, ISNULL (#c4. c,0) as Count4, SUM (ISNULL (#c1. c,0) +isnull (#c2. c,0) +isnull (#c3. c,0) +isnull (#c4. c,0)) as Total from # The city left join is #c1 on #c1. Citycode = #city. Citycode left join #c2 on #c2. Citycode = #City. Citycode left join #c3 on #c3. Citycode = #city. Citycode left join #c4 on #c4. Citycode = #city. Citycode GROUP by CityName, #c1. C, #c2. C, #c3. C, #c4. C/* To manually delete the temporary tables after they have been used in the query Analysis page, or you will be able to make an error/* The automatic deletion of the temporary table in the stored procedure will automatically delete/drop table # The City drop table #c1 drop table #c2 drop table #c3 drop table #c4

number of stores at all levels in all districts and counties of the city

The number of all types of stores in all districts and counties, the smallest grouping category is county, so query from the Sys_county table, and then connect the other tables outside.

Select Citycode,cityname into #city from sys_city where provincecode = 10; /* #c1中存储所有地区的自营一级店的数量 */select Citycode,count (1) as C into #c1 from dbo.
Storeinfo where storetype=601 and storerank=701/* self-level store/group by Citycode; /* #c2中存储所有地区的自营二级店的数量 */select Citycode,count (1) as C into #c2 from dbo.
Storeinfo where storetype=601 and storerank=702/* Self Level Two store/group by Citycode; /* #c3中存储所有地区的加盟一级店的数量 */select Citycode,count (1) as C into #c3 from dbo.
Storeinfo where storetype=602 and storerank=701/* Join the first level shop/group by Citycode; /* #c4中存储所有地区的加盟二级店的数量 */select Citycode,count (1) as C into #c4 from dbo.
Storeinfo where storetype=602 and storerank=702/* joined Level two store/group by Citycode; Select Cityname,countyname, ISNULL (#c1. c,0) as Count1, ISNULL (#c2. c,0) as Count2, ISNULL (#c3. c,0) as Count3, ISNULL (#c4. c,0) as Count4, SUM (ISNULL (#c1. c,0) +isnull (#c2. c,0) +isnull (#c3. c,0) +isnull (#c4. c,0)) as T Otal from Sys_county left join #city on #city. Citycode=sys_county.citycode LEFT Join#c1 on #c1. Citycode = #city. Citycode left join #c2 on #c2. Citycode = #city. Citycode left join #c3 on #c3. Citycode = #city. Citycode left join #c4 on #c4. Citycode = #city.
Citycode GROUP by Cityname,countyname, #c1. C, #c2. C, #c3. C, #c4. C/* To manually delete the temporary tables after they have been used in the query Analysis page, or you will get an error *//////////// DROP table #city drop table #c1 drop table #c2 drop table #c3 drop table #c4

the number of stores at all levels in every county and county in a certain city

The number of all types of shops in the county and county in a certain city, the smallest grouping category is county and county, so the query from the Sys_county table, on the basis of the query conditions can be added to the city.

Select Citycode,cityname into #city from sys_city where provincecode = 10; /* #c1中存储所有地区的自营一级店的数量 */select Citycode,count (1) as C into #c1 from dbo.
Storeinfo where storetype=601 and storerank=701/* self-level store/group by Citycode; /* #c2中存储所有地区的自营二级店的数量 */select Citycode,count (1) as C into #c2 from dbo.
Storeinfo where storetype=601 and storerank=702/* Self Level Two store/group by Citycode; /* #c3中存储所有地区的加盟一级店的数量 */select Citycode,count (1) as C into #c3 from dbo.
Storeinfo where storetype=602 and storerank=701/* Join the first level shop/group by Citycode; /* #c4中存储所有地区的加盟二级店的数量 */select Citycode,count (1) as C into #c4 from dbo.
Storeinfo where storetype=602 and storerank=702/* joined Level two store/group by Citycode; Select Cityname,countyname, ISNULL (#c1. c,0) as Count1, ISNULL (#c2. c,0) as Count2, ISNULL (#c3. c,0) as Count3, ISNULL (#c4. c,0) as Count4, SUM (ISNULL (#c1. c,0) +isnull (#c2. c,0) +isnull (#c3. c,0) +isnull (#c4. c,0)) as T Otal from Sys_county left join #city on #city. Citycode=sys_county.citycode LEFT Join#c1 on #c1. Citycode = #city. Citycode left join #c2 on #c2. Citycode = #city. Citycode left join #c3 on #c3. Citycode = #city. Citycode left join #c4 on #c4. Citycode = #city. Citycode/* Query which city of the various types of shops in the number of stores * * where sys_county.citycode=1001 GROUP by Cityname,countyname, #c1. C, #c2. C, #c3. C, #c4. C/* After the temporary table has been used in the query Analysis page, it will be deleted manually, or the error////drop table will be automatically deleted after the temporary table is used in the Stored Procedure #city drop table #c1 drop table #c2 drop table #c3 drop tabl E #c4

Summary

The above three examples are summed up is: the need to obtain but not convenient to query the value of the query out of the temporary table, a few such values to create a few temporary tables, and then through the external connection to make the desired values in the form of columns returned.