现有2张表 A B
A表和B表有共同字段LINKA表是统计数量的
B表是统计价格的单独的时候这样显示 例如
A表
LINK NUM
XX 100B表
LINK NUM2
XX 1000
我想通过 通过GROUP BY LINK
把2张表显示成
LINK NUM NUM2
XX 100 1000这个语句咋整啊?
A表和B表有共同字段LINKA表是统计数量的
B表是统计价格的单独的时候这样显示 例如
A表
LINK NUM
XX 100B表
LINK NUM2
XX 1000
我想通过 通过GROUP BY LINK
把2张表显示成
LINK NUM NUM2
XX 100 1000这个语句咋整啊?
解决方案 »
- oracle存储过程 语法
- 每个“*”代表100
- 急!!oracle是否可以选择性插入值,
- 常用数据库 我发现我在两个不同的数据库中的表进行关联是并不会,比如,像一个数据库中的表和另一个数据库中的表进行合并成一个表 这个应该怎么做呢?
- 一个简单的问题!oracle BLOB占多少字节
- 异构平台,高手帮忙
- 大家帮忙看看这条插入语句什么地方错了!!!
- 定义了long型变量来存放动态SQL语句,为何当该变量长度达到32768时就出错,不是说long型变量可存储2GB个字节吗?
- 不是我不愿出分喔,只有10分了,请帮帮忙,多谢!
- 请问在C#使用Oracle中的存储过程时,如何得到DataSet???
- Oracle用户被删除,表空间文件还在,如何查看以前对象
- 插入数据出错 vb.net
where a.link=b.link;为什么要group by?你需要计算?
select a.link, a.num, b.num2 from A a, B b where a.link = b.link
LINK NUM
XX 100
XX 100
XX 100B表
LINK NUM2
XX 1000
XX 1000 把2张表显示成
LINK NUM NUM2
XX 300 2000
group by a.link
select a.link,sum(a.num) as num,sum(b.num2) as num2
from a,b
where a.link=b.link
group by a.link
是不是要这样?
SQL>
SQL> with A as
2 (
3 select 'XX' LINK, 100 num from dual
4 union all
5 select 'XX' LINK, 111 num from dual
6 ),
7 B as
8 (
9 select 'XX' LINK, 1000 num2 from dual
10 union all
11 select 'XX' LINK, 200 num2 from dual
12 )
13 select a.link, a.num, b.num2
14 from (select a.link, sum(a.num) num from a group by a.link) a,
15 (select b.link, sum(b.num2) num2 from b group by b.link) b
16 where a.link = b.link
17 /LINK NUM NUM2
---- ---------- ----------
XX 211 1200SQL>
XX 300 3 2000 2
from (select a.link, sum(a.num) num, count(*) cnt1 from a group by a.link) a,
(select b.link, sum(b.num2) num2, count(*) cnt2
from b
group by b.link) b
where a.link = b.link
group by a.link
SELECT
A.LINK,
SUM(NVL(A.NUM,0)) OVER(PARTITION BY A.LINK),
SUM(NVL(B.NUM2,0)) ) OVER(PARTITION BY B.LINK)
FROM
TABLE_A A,TABLE_B B
WHERE A.LINK = B.LINK
---查出所有的link
SELECT DISTINCT *
FROM (
SELECT
CASE WHEN A.ID IS NOT NULL
THEN A.ID
ELSE B.ID END AS ID,
SUM(NVL(A.NUM,0)) OVER(PARTITION BY A.ID),
SUM(NVL(B.NUM,0)) OVER(PARTITION BY B.ID)
FROM
TEST_A A FULL JOIN TEST_B B
ON A.ID = B.ID
) M
from (select a.ad_channel, sum(1) num, count(*) cnt1 from web_Member group by a.ad_channel) a,
(select b.ad_channel, sum(1) num2, count(*) cnt2 from WEB_ORDER_HEADER group by b.ad_channel) b,
where a.ad_channel = b.ad_channel提示无效SQL语句。
from
(
select a.link,a.num,0 num2 from a
union all
select b.link,0 num, b.num2 from b
)
group by link
create table b(link varchar2(20),num2 int);
insert into a values('XX',100);
insert into a values('XX',100);
insert into a values('XX',100);
insert into b values('XX',1000);
insert into b values('XX',1000);select link,sum(num),sum(decode(num,0,1,0)),sum(num2),sum(decode(num2,0,1,0))
from
(
select a.link,a.num,0 num2 from a
union all
select b.link,0 num, b.num2 from b
)
group by link