新手求sql写法
现在有个表
col1 col2 col3
a a b
c b a
b b b
经查询出现以下结果
content number
a 3
b 5
c 1
就是统计一下 a有几个 b有几个 c有几个
但不能直接用 case when col1='a' then 1 else 0 end
因为 a b c 不是固定的 有可能有 d e 等别的字母 都是动态的
求高手帮忙!
[解决办法]
select col,count(*)from(select col1 as col from tb union all select col2 from tb unioin all select col3 from tb) as tgroup by col
[解决办法]
if object_id('[table4]') is not null drop table [table3]create table [table4]([col1] VARCHAR(32),[col2] VARCHAR(32),[col3] VARCHAR(32))insert [table4]select 'a ','a ','b' union allselect 'c ','b', 'a' union allselect 'b','b' ,'b'SELECT col,sum(1) value FROM(SELECT col1 AS col FROM table4UNION ALLSELECT col2 AS col FROM table4UNION ALLSELECT col3 AS col FROM table4) tbGROUP BY colcol value---- -----a 3b 5c 1