Select into结构使用

数据表中存储着不同种类的内容，用Type1、Type2列的不同值来区分，如果我们想查询各类型的数据有多少条，而且是以行的形式展示时，只用group就有点不够了，刚学的select into结构，分享一下。

数据表说明

地市表：

区县表：

商店信息表：

StoreType商店类型：自营店601、加盟商602
StoreRank商店级别：一级店701、二级店702
以河北为例，Sys_City表中河北省下有11个地市，Sys_County表中每个地市下均有3个区县

查询某地市下各类型各级别的商店数量

select CityCode,CityName into #city from Sys_City where ProvinceCode = 10，表示把从Sys_City表中查询到的内容插入到表名为”#city”的临时表中，注意表名不能重复。
ISNULL(#c1.c,0)表示如果#c1.c值为NULL就返回0，因为要计算总数，所以数量不能为NULL，因此做了一下转换。
某地市下各类型商店的数量，最小分组类别是地市，因此从#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 /*自营一级店*/ group by CityCode;/*#c2中存储所有地区的自营二级店的数量*/select CityCode,COUNT(1) as c into #c2 from dbo.StoreInfo where StoreType=601 and StoreRank=702 /*自营二级店*/ group by CityCode;/*#c3中存储所有地区的加盟一级店的数量*/select CityCode,COUNT(1) as c into #c3 from dbo.StoreInfo where StoreType=602 and StoreRank=701 /*加盟一级店*/ group by CityCode;/*#c4中存储所有地区的加盟二级店的数量*/select CityCode,COUNT(1) as c into #c4 from dbo.StoreInfo where StoreType=602 and StoreRank=702 /*加盟二级店*/ 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 Totalfrom #cityleft join #c1 on #c1.CityCode = #city.CityCodeleft join #c2 on #c2.CityCode = #city.CityCodeleft join #c3 on #c3.CityCode = #city.CityCodeleft join #c4 on #c4.CityCode = #city.CityCodegroup by CityName,#c1.c,#c2.c,#c3.c,#c4.c/*在查询分析页中临时表用完之后要手动删除，否则会报错*//*在存储过程中临时表用完之后会自动删除*/drop table #citydrop table #c1drop table #c2drop table #c3drop table #c4

所有地市下所有区县的各类型各级别的商店数量

所有地市下所有区县的各类型商店的数量，最小分组类别是区县，因此从Sys_County表中查询，然后外连接其他表。

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 /*自营一级店*/ group by CityCode;/*#c2中存储所有地区的自营二级店的数量*/select CityCode,COUNT(1) as c into #c2 from dbo.StoreInfo where StoreType=601 and StoreRank=702 /*自营二级店*/ group by CityCode;/*#c3中存储所有地区的加盟一级店的数量*/select CityCode,COUNT(1) as c into #c3 from dbo.StoreInfo where StoreType=602 and StoreRank=701 /*加盟一级店*/ group by CityCode;/*#c4中存储所有地区的加盟二级店的数量*/select CityCode,COUNT(1) as c into #c4 from dbo.StoreInfo where StoreType=602 and StoreRank=702 /*加盟二级店*/ 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 Totalfrom Sys_Countyleft join #city on #city.CityCode=Sys_County.CityCodeleft join #c1 on #c1.CityCode = #city.CityCodeleft join #c2 on #c2.CityCode = #city.CityCodeleft join #c3 on #c3.CityCode = #city.CityCodeleft join #c4 on #c4.CityCode = #city.CityCodegroup by CityName,CountyName,#c1.c,#c2.c,#c3.c,#c4.c/*在查询分析页中临时表用完之后要手动删除，否则会报错*//*在存储过程中临时表用完之后会自动删除*/drop table #citydrop table #c1drop table #c2drop table #c3drop table #c4

某地市下所有区县的各类型各级别的商店数量

某地市下所有区县的各类型商店的数量，最小分组类别是区县，因此从Sys_County表中查询，在此基础上加上地市查询条件即可。

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 /*自营一级店*/ group by CityCode;/*#c2中存储所有地区的自营二级店的数量*/select CityCode,COUNT(1) as c into #c2 from dbo.StoreInfo where StoreType=601 and StoreRank=702 /*自营二级店*/ group by CityCode;/*#c3中存储所有地区的加盟一级店的数量*/select CityCode,COUNT(1) as c into #c3 from dbo.StoreInfo where StoreType=602 and StoreRank=701 /*加盟一级店*/ group by CityCode;/*#c4中存储所有地区的加盟二级店的数量*/select CityCode,COUNT(1) as c into #c4 from dbo.StoreInfo where StoreType=602 and StoreRank=702 /*加盟二级店*/ 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 Totalfrom Sys_Countyleft join #city on #city.CityCode=Sys_County.CityCodeleft join #c1 on #c1.CityCode = #city.CityCodeleft join #c2 on #c2.CityCode = #city.CityCodeleft join #c3 on #c3.CityCode = #city.CityCodeleft join #c4 on #c4.CityCode = #city.CityCode/*查询哪个地市下的各类型商店数量*/where Sys_County.CityCode=1001group by CityName,CountyName,#c1.c,#c2.c,#c3.c,#c4.c/*在查询分析页中临时表用完之后要手动删除，否则会报错*//*在存储过程中临时表用完之后会自动删除*/drop table #citydrop table #c1drop table #c2drop table #c3drop table #c4

总结

上面三个例子总结起来就是：把需要获取的但不方便直接查询的值查询出来放到临时表中，有几个这样的值就创几个临时表，之后通过外连接的方式使所需值以列的形式返回。