有表A与表B,用SQL语句得出表C:
如下:
表A
incount content
4 ABC
2 CDF
3 DDA
表B
phone ismember content
138 1 ABC
133 1 ABC
131 1 CDF
132 0 DDA
139 1 DDA
130 0 DDA
135 0 CDF
136 0 ABC
137 1 ABC表C
incount content 会员数 非会员数
4 ABC 3 1
2 CDF 1 1
3 DDA 1 2
注:B表中的字段当ismember=1 时代表会员
ismember=0时代表非会员
如下:
表A
incount content
4 ABC
2 CDF
3 DDA
表B
phone ismember content
138 1 ABC
133 1 ABC
131 1 CDF
132 0 DDA
139 1 DDA
130 0 DDA
135 0 CDF
136 0 ABC
137 1 ABC表C
incount content 会员数 非会员数
4 ABC 3 1
2 CDF 1 1
3 DDA 1 2
注:B表中的字段当ismember=1 时代表会员
ismember=0时代表非会员
解决方案 »
- oralce二个表关联查询的小问题,在线等
- 诡异的ora-01400: 无法将 NULL 插入 ("SYSTEM"."HC_RLDYRBMXB_TEMP"."DYBH"
- SQLPLUS菜鸟问题,在线等————————————————
- 请问如何expdp/impdp一个分区表的其中某个分区的数据?谢谢!!!
- 如何写个循环SQL语句?
- 请教:ASP访问Oracle
- oracle8 "Enterprise Manager"的一个问题
- Oracle中如何弹出错误?
- 请大家给4位斑竹提出建议和看法,如何把oarcle版办的更好呢?
- 为什么存储过程中打印的中文都变成了问号??求解。。。。
- 请教高手,有没有数据库关闭前的触发器,谢谢! 急!在线等
- 求救一個select連續n天的語句
------- ------- ---------- ----------
4 ABC 3 1
2 CDF 1 1
3 DDA 1 2刚刚学习了一下,解决了这个问题.
继续努力中......
case 原始列 when 特征 then 值 else 0 as 目标列
decode(原始列,特征值,值 ,0) as 目标列
CREATE TABLE MTB7(PHONE NUMBER(10),ISMEMBER NUMBER(2),CONTENT VARCHAR2(10))
select a.incount,a.content
,sum(decode(flag,1,pCount,0)) as 会员数
,sum(decode(flag,0,pCount,0)) as 非会员数
from mtb6 a
,(
select content,ismember as flag
,sum(rn) pCount
from (
select content,ismember
,rank()over(partition by content,ismember order by ismember) rn
from mtb7
) group by content,ismember
) b
where a.content=b.content
group by a.incount,a.content
a.content,
sum(t.member) member,
sum(t.unmember) unmember (select a.incount,
a.content,
1 member,
0 unmember
from a, b
where a.content = b.content
and b.ismember = 1
union all
select a.incount, a.content, 0, 1
from a, b
where a.content = b.content
and b.ismember = 0) t
group by a.incount, a.content
from (select a.incount, a.content, 1 member, 0 unmember
from a, b
where a.content = b.content
and b.ismember = 1
union all
select a.incount, a.content, 0, 1
from a, b
where a.content = b.content
and b.ismember = 0) t
group by a.incount, a.content
From Mtb6 a,
(Select Content,
Sum(Decode(Ismember, 1, 1)),
Sum(Decode(Ismember, 0, 1))
From Mtb7
Group By Content) b
Where a.Content = b.Content
这样统计出来就可能会缺记录。
下面是SQL2000和sybase数据库的写法,外联接和isnull函数不一致。自己可转化一下。select a.incount as incount,a.content as content,isnull(b.会员数,0),isnull(c.非会员数,0)
from tablea a,
(select content,count(*) as 会员数 from tableb where ismember=1 group by content) b,
(select content,count(*) as 非会员数 from tableb where ismember=0 group by content) c
where a.content*=b.content and a.content*=c.content
a.content,
sum(decode(b.ismember,1,1,0) "会员数",
sum(decode(b.ismember,0,1,0) "非会员数"
from a,b
where a.content=b.content
group by a.incount,a.content;
and ismember=1) as 会员数,(select Count(*) from B where B.content=A.content and ismember=0) as 非会员数 from A
(
select * from
(
select 4 as incount,'ABC' as content
union all
select 2 as incount,'CDF' as content
union all
select 3 as incount,'DDA' as content
)a
)aa
inner join
(
select a.content,a.会员数,b.非会员数 from
(
select content,ismember,num_count as '非会员数' from
(
select content,ismember,
case when ismember=1 then '会员数' else '非会员数' end as leixing,
count(*) as num_count
from
(
select 138 as phone, 1 as ismember,'ABC' as content
union all
select 133 as phone, 1 as ismember,'ABC' as content
union all
select 131 as phone, 1 as ismember,'CDF' as content
union all
select 132 as phone, 0 as ismember,'DDA' as content
union all
select 139 as phone, 1 as ismember,'DDA' as content
union all
select 130 as phone, 0 as ismember,'DDA' as content
union all
select 135 as phone, 0 as ismember,'CDF' as content
union all
select 136 as phone, 0 as ismember,'ABC' as content
union all
select 137 as phone, 1 as ismember,'ABC' as content
) b
group by content,ismember
)a
where leixing='非会员数'
)binner join
(
select content,ismember,num_count as '会员数' from
(
select content,ismember,
case when ismember=1 then '会员数' else '非会员数' end as leixing,
count(*) as num_count
from
(
select 138 as phone, 1 as ismember,'ABC' as content
union all
select 133 as phone, 1 as ismember,'ABC' as content
union all
select 131 as phone, 1 as ismember,'CDF' as content
union all
select 132 as phone, 0 as ismember,'DDA' as content
union all
select 139 as phone, 1 as ismember,'DDA' as content
union all
select 130 as phone, 0 as ismember,'DDA' as content
union all
select 135 as phone, 0 as ismember,'CDF' as content
union all
select 136 as phone, 0 as ismember,'ABC' as content
union all
select 137 as phone, 1 as ismember,'ABC' as content
) b
group by content,ismember
)a
where leixing='会员数'
)a
on a.content=b.content)bb
on aa.content=bb.content
select t.incount,t.content,v.membercount,t.incount-v.membercount
A t,
(
select content,sum(ismember) membercount
from B
group by content
) v
where t.content=v.content
select *,
会员数=(select count(phone) from tblUserInfo where tblUserInfo.content = tblUser.content and ismember = '1'),
非会员数=(select count(phone) from tblUserInfo where tblUserInfo.content = tblUser.content and ismember = '0')
from tblUser
From tablea a,
(Select Content,Sum(case Ismember when 1 then 1 end) as hy,Sum(case Ismember when 0 then 1 end) as fhy
From tableb Group By Content) b
Where a.Content *= b.Content