34,594
社区成员
发帖
与我相关
我的任务
分享
SELECT Top 10 province.proname AS 区域 ,IsNull(SUM(RegionExpand.CfgValue1),0) AS 数量,province.OrganCode
FROM province INNER JOIN MapRegion ON province.OrganCode = LEFT(MapRegion.OrganCode, 2)
INNER JOIN RegionExpand ON RegionExpand.REGID = MapRegion.RegID
WHERE (RegionExpand.CfgName = 'A')
GROUP BY province.proname, province.OrganCode
ORDER BY 数量 DESC
create table province(id int , OrganCode varchar(10) , proname varchar(10))
insert province
select
1 , '32' , '江苏'
union all select
2 , '34' , '安徽'
union all select
3 , '36' , '江西'
union all select
4 , '51' , '四川'
union all select
5 , '46' , '海南'
union all select
6 , '11' , '北京'
create table MapRegion (RegID int , Organcode varchar(10) , OrganName varchar(10))
insert MapRegion
select
1 , '340100' , '合肥'
union all select
2 , '340200' , '芜湖'
union all select
3 , '320100' , '南京'
union all select
4 , '360100' , '南昌'
union all select
5 , '510100' , '成都'
union all select
6 , '110000' , '北京'
union all select
7 , '460100' , '海口'
create table RegionExpand (id int , REGID int , CfgName varchar(10) , CfgValue1 int)
insert RegionExpand
select
1 , 1 , 'A' , 2
union all select
2 , 2 , 'B' , 3
union all select
3 , 3 , 'A' , 8
union all select
4 , 2 , 'A' , 6
union all select
5 , 4 , 'A' , 2
union all select
6 , 5 , 'A' , 3
union all select
7 , 4 , 'C' , 4
union all select
8 , 5 , 'D' , 2
union all select
9 , 6 , 'A' , 8
select MapRegion.OrganCode,RegionExpand.CfgValue1 ,RegionExpand.CfgName from MapRegion left outer join RegionExpand on RegionExpand.REGID = MapRegion.RegID
SELECT Top 10 province.proname AS 区域 ,
IsNull(SUM(bb.CfgValue1),0) AS 数量,
province.OrganCode
FROM province left outer JOIN
(select MapRegion.OrganCode,RegionExpand.CfgValue1 ,isnull(RegionExpand.CfgName,'A') as CfgName from MapRegion left outer join RegionExpand on RegionExpand.REGID = MapRegion.RegID ) bb
ON province.OrganCode = LEFT(bb.OrganCode, 2)
WHERE (bb.CfgName = 'A')
GROUP BY province.proname, province.OrganCode
ORDER BY 数量 DESC
drop table RegionExpand
drop table MapRegion
drop table province
/*
北京 8 11
安徽 8 34
江苏 8 32
四川 3 51
江西 2 36
海南 0 46
*/
/**
区域 数量 OrganCode
---- ----------- -----------
安徽 8 34
江苏 8 32
北京 8 11
四川 3 51
江西 2 36
海南 0 46
(所影响的行数为 6 行)
警告: 聚合或其它 SET 操作消除了空值。
**/
DECLARE @province TABLE([id] INT, [OrganCode] INT, [proname] NVARCHAR(2))
INSERT @province
SELECT 1, 32, N'江苏' UNION ALL
SELECT 2, 34, N'安徽' UNION ALL
SELECT 3, 36, N'江西' UNION ALL
SELECT 4, 51, N'四川' UNION ALL
SELECT 5, 46, N'海南' UNION ALL
SELECT 6, 11, N'北京'
DECLARE @MapRegion TABLE([RegID] INT, [Organcode] INT, [OrganName] NVARCHAR(2))
INSERT @MapRegion
SELECT 1, 340100, N'合肥' UNION ALL
SELECT 2, 340200, N'芜湖' UNION ALL
SELECT 3, 320100, N'南京' UNION ALL
SELECT 4, 360100, N'南昌' UNION ALL
SELECT 5, 510100, N'成都' UNION ALL
SELECT 6, 110000, N'北京' UNION ALL
SELECT 7, 460100, N'海口'
DECLARE @RegionExpand TABLE([id] INT, [REGID] INT, [CfgName] VARCHAR(1), [CfgValue1] INT)
INSERT @RegionExpand
SELECT 1, 1, 'A', 2 UNION ALL
SELECT 2, 2, 'B', 3 UNION ALL
SELECT 3, 3, 'A', 8 UNION ALL
SELECT 4, 2, 'A', 6 UNION ALL
SELECT 5, 4, 'A', 2 UNION ALL
SELECT 6, 5, 'A', 3 UNION ALL
SELECT 7, 4, 'C', 4 UNION ALL
SELECT 8, 5, 'D', 2 UNION ALL
SELECT 9, 6, 'A', 8
SELECT Top 10 province.proname AS 区域 ,IsNull(SUM(RegionExpand.CfgValue1),0) AS 数量,province.OrganCode
FROM @province AS province LEFT JOIN @MapRegion AS MapRegion ON province.OrganCode = LEFT(MapRegion.OrganCode, 2)
LEFT JOIN @RegionExpand AS RegionExpand ON RegionExpand.REGID = MapRegion.RegID
AND (RegionExpand.CfgName = 'A')
GROUP BY province.proname, province.OrganCode
ORDER BY 数量 DESC
/*
区域 数量 OrganCode
---- ----------- -----------
安徽 8 34
江苏 8 32
北京 8 11
四川 3 51
江西 2 36
海南 0 46
*/
SELECT Top 10 province.proname AS 区域 ,IsNull(SUM(RegionExpand.CfgValue1),0) AS 数量,province.OrganCode
FROM province left JOIN MapRegion ON province.OrganCode = LEFT(MapRegion.OrganCode, 2)
left JOIN RegionExpand ON RegionExpand.REGID = MapRegion.RegID and RegionExpand.CfgName = 'A'
GROUP BY province.proname, province.OrganCode
ORDER BY 数量 DESC
SELECT Top 10 province.proname AS 区域 ,IsNull(SUM(RegionExpand.CfgValue1),0) AS 数量,province.OrganCode
FROM province left JOIN MapRegion ON province.OrganCode = LEFT(MapRegion.OrganCode, 2)
left JOIN (select * from RegionExpand WHERE (RegionExpand.CfgName = 'Past24ProblemsCount') ) as RegionExpand ON RegionExpand.REGID = MapRegion.RegID
GROUP BY province.proname, province.OrganCode
ORDER BY 数量 DESC