22,209
社区成员
发帖
与我相关
我的任务
分享
select catvalue as [Cat(大/中/小类)],COUNT(1) as CountQty from
(
select * from C
unpivot
(catvalue for cattype in(cat1,cat2,cat3)) as upt
) t group by catvalue
--drop table a
create table A(id int, name varchar(10),cat1 int, cat2 int , cat3 int)
insert into A
select 1 ,'A01', 1, 10, 101 union all
select 2 ,'A02', 1, 10, 101 union all
select 3 ,'A03', 1, 11, 111 union all
select 4 ,'A04', 1, 10, 101 union all
select 5 ,'A05', 2, 20, null union all
select 6 ,'A06', 2, 21, null
go
select [Cat(大/中/小类)],COUNT(*) CountQty
from
(
select cat1 [Cat(大/中/小类)] from A
union all
select cat2 from A
union all
select cat3 from A
)t
where [Cat(大/中/小类)] is not null
group by [Cat(大/中/小类)]
order by cast([Cat(大/中/小类)] as varchar)
/*
Cat(大/中/小类) CountQty
1 4
10 3
101 3
11 1
111 1
2 2
20 1
21 1
*/
不过,这个速度,应该快不了
--drop table a
create table A(id int, name varchar(10),cat1 int, cat2 int , cat3 int)
insert into A
select 1 ,'A01', 1, 10, 101 union all
select 2 ,'A02', 1, 10, 101 union all
select 3 ,'A03', 1, 11, 111 union all
select 4 ,'A04', 1, 10, 101 union all
select 5 ,'A05', 2, 20, null union all
select 6 ,'A06', 2, 21, null
go
select [Cat(大/中/小类)],COUNT(*) CountQty
from
(
select cat1 [Cat(大/中/小类)] from A
union all
select cat2 from A
union all
select cat3 from A
)t
where [Cat(大/中/小类)] is not null
group by [Cat(大/中/小类)]
/*
Cat(大/中/小类) CountQty
1 4
2 2
10 3
11 1
20 1
21 1
101 3
111 1
*/