表A
Region Area Province City City_Tier Atlas_number issure
SE ANH ANH 芜湖 Conversion 10213650 1
SE JSS JSS 昆山 Conversion 32105084 0
SE JSN JSN 南京 Developing 10228132 0 想得到这样的数据
分类组别 数量A（issure=0） 数量B（issure=1）
Region 1 (根据Region来的相同的算1) 1 （同左）
Area 2 (根据Area来的相同的算1) 1 （同左）
City 2 (根据City 来的相同的算1) 1 （同左）
City_Tier 2 (根据City_Tier 来的相同的1) 1 （同左）
Region Area Province City City_Tier Atlas_number issure
SE ANH ANH 芜湖 Conversion 10213650 1
SE JSS JSS 昆山 Conversion 32105084 0
SE JSN JSN 南京 Developing 10228132 0
排版乱了。 祈求高手的到来。。！
if OBJECT_ID('tempdb..#') is not null
drop table #;
go
create table #
(Region char(2),Area char(3),City nvarchar(20),City_Tier varchar(20),Atlas_number int,issure bit);
go
insert into #
select 'SE','ANH',N'芜湖','Conversion',10213650,1 union all
select 'SE','JSS',N'昆山','Conversion',32105084,0 union all
select 'SE','JSN',N'南京','Developing',10228132,0;
go;with p1 as(
select issure,
COUNT(distinct Region) Region,
COUNT(distinct Area) Area,
COUNT(distinct City) City,
COUNT(distinct City_Tier) City_Tier
from # group by issure
),
p2 as(
select * from p1
unpivot (num for Class in ([Region],[Area],[City],[City_Tier])) upvt
)
select class,
(select num from p2 where p.class=class and issure=0) num0,
(select num from p2 where p.class=class and issure=1) num1
from p2 p group by class;
/*
Area 2 1
City 2 1
City_Tier 2 1
Region 1 1
*/
select Type, sum(case when issure=0 then cnt else 0 end) as QtyA,
sum(case when issure=1 then cnt else 0 end) as QtyB
from
(
select 'Region' as [type], issure , count(distinct region) as cnt from # group by issure
union all
select 'Area' as [type], issure , count(distinct Area) as cnt from # group by issure
union all
select 'City' as [type], issure , count(distinct City) as cnt from # group by issure
union all
select 'City_Tier' as [type], issure , count(distinct City_Tier) as cnt from # group by issure
) X
group by Type
select Type, sum(case when issure=0 then cnt else 0 end) as QtyA,
sum(case when issure=1 then cnt else 0 end) as QtyB
from
(
select 'Region' as [type], issure , count(distinct region) as cnt from 表A
group by issure
union all
select 'Area' as [type], issure , count(distinct Area) as cnt from 表A
group by issure
union all
select 'City' as [type], issure , count(distinct City) as cnt from 表A
group by issure
union all
select 'City_Tier' as [type], issure , count(distinct City_Tier) as cnt from 表A
group by issure
) X
group by Type
go
if exists (select 1 from sysobjects where name='tb')
drop table tb
create table tb(Region varchar(10),Area varchar(10), Province varchar(10), City varchar(10),City_Tier varchar(50), Atlas_number varchar(20),issure int)
insert into tb
select 'SE', 'ANH', 'ANH', '芜湖', 'Conversion', '10213650', 1 union all
select 'SE', 'JSS', 'JSS', '昆山', 'Conversion', '32105084', 0 union all
select 'SE', 'JSN', 'JSN', '南京', 'Developing', '10228132', 0;with t
as
(
select 分类组别='Region',名字=region,issure from tb
union all
select 分类组别='Area',名字=Area,issure from tb
union all
select 分类组别='City',名字=City,issure from tb
union all
select 分类组别='City_Tier',名字=City_Tier,issure from tb
)
select 分类组别,(select COUNT(名字)
from t
where a.分类组别=分类组别 and issure=0) as [数量A（issure=0）],
(select COUNT(名字)
from t
where a.分类组别=分类组别 and issure=1) as [数量B（issure=1）]
from t a
group by 分类组别
分类组别 数量A（issure=0） 数量B（issure=1）
Area 2 1
City 2 1
City_Tier 2 1
Region 2 1
as
(
select 分类组别='Region',名字=region,issure from tb
union all
select 分类组别='Area',名字=Area,issure from tb
union all
select 分类组别='City',名字=City,issure from tb
union all
select 分类组别='City_Tier',名字=City_Tier,issure from tb
),t1
as
(
select distinct *
from t
)
select 分类组别,(select COUNT(名字)
from t1
where a.分类组别=分类组别 and issure=0) as [数量A（issure=0）],
(select COUNT(名字)
from t1
where a.分类组别=分类组别 and issure=1) as [数量B（issure=1）]
from t1 a
group by 分类组别
order by 分类组别 desc分类组别 数量A（issure=0） 数量B（issure=1）
Region 1 1
City_Tier 2 1
City 2 1
Area 2 1
if OBJECT_ID('tab') is not null
drop table tab;
go
create table tab
(Region char(2),Area char(3),City nvarchar(20),City_Tier varchar(20),Atlas_number int,issure bit);
go
insert into tab
select 'SE','ANH',N'芜湖','Conversion',10213650,1 union all
select 'SE','JSS',N'昆山','Conversion',32105084,0 union all
select 'SE','JSN',N'南京','Developing',10228132,0;
go-- SQL 2005
if OBJECT_ID('vw') is not null
drop table vw;
go
create view vw
as
with p1 as(
select issure,
COUNT(distinct Region) Region,
COUNT(distinct Area) Area,
COUNT(distinct City) City,
COUNT(distinct City_Tier) City_Tier
from tab group by issure
),
p2 as(
select * from p1
unpivot (num for Class in ([Region],[Area],[City],[City_Tier])) upvt
)
select class,
(select num from p2 where p.class=class and issure=0) num0,
(select num from p2 where p.class=class and issure=1) num1
from p2 p group by class
goselect * from vw;
IF OBJECT_ID('[tb]') IS NOT NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb] ([Region] [nvarchar](10),[Area] [nvarchar](10),[Province] [nvarchar](10),[City] [nvarchar](10),[City_Tier] [nvarchar](10),[Atlas_number] [int],[issure] [int])
INSERT INTO [tb]
SELECT 'SE','ANH','ANH','芜湖','Conversion','10213650','1' UNION ALL
SELECT 'SE','JSS','JSS','昆山','Conversion','32105084','0' UNION ALL
SELECT 'SE','JSN','JSN','南京','Developing','10228132','0'--SELECT * FROM [tb]-->SQL查询如下:
SELECT col 分组类别,[0] 数量A,[1] 数量B
FROM (
SELECT DISTINCT *
FROM (
SELECT Region, Area, City, City_Tier, issure
FROM tb
) a
UNPIVOT(VALUE FOR col IN (Region, Area, City, City_Tier)) b
) a
PIVOT(COUNT(VALUE) FOR issure IN([0],[1])) b
ORDER BY 2,3
/*
分组类别 数量A 数量B
---------- ----------- -----------
Region 1 1
Area 2 1
City 2 1
City_Tier 2 1(4 行受影响)
*/