select test,count(*) as num from ( select test1 test from 表 union all select test2 from 表 ) a group by test
create table #tb(id int,test1 varchar(10),test2 varchar(10)) insert into #tb select 1,'a','b' union all select 2,'b','c' union all select 3,'a','d' union all select 4,'b','c'select test1,COUNT(*) as count from ( select test1 from #tb union all select test2 from #tb )t group by test1/* test1 count a 2 b 3 c 2 d 1 */
if OBJECT_ID('tempdb..#temp', 'u') is not null drop table #temp; go create table #temp( [id] varchar(100), [test1] varchar(100), [test2] varchar(100)); insert #temp select '1','a','b' union all select '2','b','c' union all select '3','a','d' union all select '4','b','c' --SQL: SELECT test, cnt=COUNT(1) FROM ( select id, test=test1 from #temp UNION ALL SELECT id, test2 FROM #temp ) t GROUP BY test ORDER BY MIN(id) /* test cnt a 2 b 3 c 2 d 1 */
select test,count(*) as num from ( select test1 as test from [表名] union all select test2 as test from [表名] ) a group by test order by test
from
(
select test1 test from 表
union all
select test2 from 表
) a
group by test
insert into #tb
select 1,'a','b'
union all select 2,'b','c'
union all select 3,'a','d'
union all select 4,'b','c'select test1,COUNT(*) as count
from (
select test1 from #tb
union all select test2 from #tb
)t
group by test1/*
test1 count
a 2
b 3
c 2
d 1
*/
go
create table #temp( [id] varchar(100), [test1] varchar(100), [test2] varchar(100));
insert #temp
select '1','a','b' union all
select '2','b','c' union all
select '3','a','d' union all
select '4','b','c' --SQL:
SELECT test, cnt=COUNT(1) FROM
(
select id, test=test1 from #temp
UNION ALL
SELECT id, test2 FROM #temp
) t
GROUP BY test
ORDER BY MIN(id)
/*
test cnt
a 2
b 3
c 2
d 1
*/
select test,count(*) as num
from
(
select test1 as test from [表名]
union all
select test2 as test from [表名]
) a
group by test
order by test