with temp as( select 'abc' username,'A' flag1,'A' flag2,'A' flag3 from dual union all select 'abc1' username,'B' flag1,'B' flag2,'B' flag3 from dual union all select 'abc2' username,'B' flag1,'B' flag2,'B' flag3 from dual union all select 'abc' username,'C' flag1,'C' flag2,'C' flag3 from dual union all select 'abc' username,'A' flag1,'A' flag2,'A' flag3 from dual ) select username,flag1,count(username) from temp group by username,flag1,flag2,flag3
select 用户名称,count(case when 标志1='A' then 1 end) A, count(case when 标志1='B' then 1 end) B, count(case when 标志1='C' then 1 end) C from 表 where 标志1 in ('A','B','C') and 标志1=标志2 and 标志1=标志3 group by 用户名称
高手~但是我把条件说错了一个,应该是 每个用户标志1,或者标志2,或者标志3为A数据有几条 每个用户标志1,或者标志2,或者标志3为B数据有几条 每个用户标志1,或者标志2,或者标志3为C数据有几条所以应该不能用and 标志1=标志2 and 标志1=标志3这个了吧
这个应该可以 SQL> SELECT * FROM TEST1;
A B C ---------- ---------- ---------- A B C B B C B B B
SQL> SQL> WITH T1 AS(SELECT 'A' COL FROM DUAL UNION ALL 2 SELECT 'B' COL FROM DUAL UNION ALL 3 SELECT 'C' COL FROM DUAL ) 4 SELECT T1.COL,SUM(DECODE(INSTR(T2.A||T2.B||T2.C,T1.COL),0,0,1)) FROM T1,TEST1 T2 5 GROUP BY T1.COL ;
COL SUM(DECODE(INSTR(T2.A||T2.B||T --- ------------------------------ A 1 B 3 C 2
SQL>
select a.col1,count(*) from table a where a.col1 =a.col2 and a.col1=a.col3 group by a.col1 貌似这条语句应该可以吧
select 'abc' username,'A' flag1,'A' flag2,'A' flag3 from dual
union all
select 'abc1' username,'B' flag1,'B' flag2,'B' flag3 from dual
union all
select 'abc2' username,'B' flag1,'B' flag2,'B' flag3 from dual
union all
select 'abc' username,'C' flag1,'C' flag2,'C' flag3 from dual
union all
select 'abc' username,'A' flag1,'A' flag2,'A' flag3 from dual
)
select username,flag1,count(username) from temp group by username,flag1,flag2,flag3
count(case when 标志1='B' then 1 end) B,
count(case when 标志1='C' then 1 end) C
from 表
where 标志1 in ('A','B','C')
and 标志1=标志2
and 标志1=标志3
group by 用户名称
每个用户标志1,或者标志2,或者标志3为A数据有几条
每个用户标志1,或者标志2,或者标志3为B数据有几条
每个用户标志1,或者标志2,或者标志3为C数据有几条所以应该不能用and 标志1=标志2
and 标志1=标志3这个了吧
SQL> SELECT * FROM TEST1;
A B C
---------- ---------- ----------
A B C
B B C
B B B
SQL>
SQL> WITH T1 AS(SELECT 'A' COL FROM DUAL UNION ALL
2 SELECT 'B' COL FROM DUAL UNION ALL
3 SELECT 'C' COL FROM DUAL )
4 SELECT T1.COL,SUM(DECODE(INSTR(T2.A||T2.B||T2.C,T1.COL),0,0,1)) FROM T1,TEST1 T2
5 GROUP BY T1.COL ;
COL SUM(DECODE(INSTR(T2.A||T2.B||T
--- ------------------------------
A 1
B 3
C 2
SQL>
貌似这条语句应该可以吧