现在有这样的三个表,
甲表
名称 数量
A 1
B 2
C 3
乙表
名称 数量
B 1
C 2
D 3丙表
名称 数量
C 1
D 2
E 3查询结果需要这样的:名称 数量1 数量2 数量3
A 1 0 0
B 2 1 0
C 3 2 1
D 0 3 2
E 0 0 3就是以名称为关联关系,只要在3个表的其中一个表有对应数据,在结果表中就需要查询出来,但是需要考虑其中某个表没有数据的情况。有没有什么比较好的方法?
甲表
名称 数量
A 1
B 2
C 3
乙表
名称 数量
B 1
C 2
D 3丙表
名称 数量
C 1
D 2
E 3查询结果需要这样的:名称 数量1 数量2 数量3
A 1 0 0
B 2 1 0
C 3 2 1
D 0 3 2
E 0 0 3就是以名称为关联关系,只要在3个表的其中一个表有对应数据,在结果表中就需要查询出来,但是需要考虑其中某个表没有数据的情况。有没有什么比较好的方法?
select 名称, 数量, 数量1,0 数量2, 0 数量3 from 甲表
union
select 名称, 0 数量1,数量 数量2, 0 数量3 from 乙表
union
select 名称, 0 数量1,0 数量2, 数量 数量3 from 乙表
) a
group by 名称, 数量1, 数量2, 数量3
select 名称, sum(数量1), sum(数量2), sum(数量3) from (
select 名称, 数量 数量1,0 数量2, 0 数量3 from 甲表
union
select 名称, 0 数量1,数量 数量2, 0 数量3 from 乙表
union
select 名称, 0 数量1,0 数量2, 数量 数量3 from 乙表
) a
group by 名称
select isnull(isnull(t1.名称,t2.名称),t3.名称) 名称,
isnull(t1.数量,0) 数量1,
isnull(t2.数量,0) 数量2,
isnull(t3.数量,0) 数量3
from 甲表 t1
full join 乙表 t2 on t1.名称=t2.名称
full join 丙表 t3 on t1.名称=t3.名称
select 名称, 数量 数量1 , 0 as 数量2 , 0 as 数量3 from 甲表
union
select 名称, 0 as 数量1 , 数量 as 数量2 , 0 as 数量3 from 乙表
union
select 名称, 0 as 数量1 , 0 as 数量2 , 数量 as 数量3 from 丙表
create table tbjia (name varchar(20),num int)
create table tbyi (name varchar(20),num int)
create table tbbing (name varchar(20),num int)
insert into tbjia values('A',1)
insert into tbjia values('B',2)
insert into tbjia values('C',3)
insert into tbyi values('B',1)
insert into tbyi values('C',2)
insert into tbyi values('D',3)
insert into tbbing values('C',1)
insert into tbbing values('D',2)
insert into tbbing values('E',3)select a.name ,isnull(tbjia.num,0) as num1 ,isnull(tbyi.num,0) as num2 ,isnull(tbbing.num,0) as num3 from
(select name from tbjia
union
select name from tbyi
union
select name from tbbing ) a left join tbjia on a.name=tbjia.name
left join tbyi on a.name=tbyi.name
left join tbbing on a.name=tbbing.name
create table 甲表(名称CHAR(1), 数量int)
insert into 甲表
select 'A', 1 union
select 'B', 2 union
select 'C', 3
create table 乙表(名称CHAR(1), 数量int)
insert into 乙表
select 'B', 1 union
select 'C', 2 union
select 'D', 3
create table 丙表(名称CHAR(1), 数量int)
insert into 丙表
select 'C', 1 union
select 'D', 2 union
select 'E', 3--1
select 名称, sum(数量) 数量, sum(数量) 数量, sum(数量)数量from (
select 名称, 数量 数量,0 数量, 0 数量from 甲表
union
select 名称, 0 数量,数量数量, 0 数量from 乙表
union
select 名称, 0 数量,0 数量, 数量数量from 丙表
) a
group by 名称--2
select 名称,SUM(数量)数量,sum(数量) 数量, sum(数量)数量from (
select isnull(isnull(t1.名称,t2.名称),t3.名称) 名称,
isnull(t1.数量,0) 数量,
isnull(t2.数量,0) 数量,
isnull(t3.数量,0) 数量
from 甲表t1
full join 乙表t2 on t1.名称=t2.名称
full join 丙表t3 on t1.名称=t3.名称)a
group by 名称/*
名称 数量 数量 数量
A 1 0 0
B 2 1 0
C 3 2 1
D 0 3 2
E 0 0 3
*/DROP TABLE 甲表
DROP TABLE 乙表
DROP TABLE 丙表
这是full join 不是left join OK?
#19 都给你测好了
select 名称, 数量 数量1,0 数量2, 0 数量3 from 甲表
union
select 名称, 0 数量1,数量 数量2, 0 数量3 from 乙表
union
select 名称, 0 数量1,0 数量2, 数量 数量3 from 乙表
) a
group by 名称那个a是干什么的?为什么不能少?
a代表子查询的结果,可以随便写aaaaaaa,vdsda,asdfsdqsdafa,……就是不能没有
这贴早就该结了,下次看到大家都跑远远的
[名称] varchar,
[数量] int
)
CREATE TABLE #甲表(
[名称] varchar,
[数量] int
)
CREATE TABLE #乙表(
[名称] varchar,
[数量] int
)
CREATE TABLE #丙表(
[名称] varchar,
[数量] int
)
insert into #甲表 select 'A',1
insert into #甲表 select 'B',2
insert into #甲表 select 'C',3INSERT INTO 乙表 SELECT 'B',1
INSERT INTO 乙表 SELECT 'C',2
INSERT INTO 乙表 SELECT 'D',3INSERT INTO 丙表 SELECT 'C',1
INSERT INTO 丙表 SELECT 'D',2
INSERT INTO 丙表 SELECT 'E',3
SELECT
[名称],
CASE [T] WHEN '甲' then [数量] ELSE 0 END 数量1,
CASE [T] WHEN '乙' then [数量] ELSE 0 END 数量2,
CASE [T] WHEN '丙' then [数量] ELSE 0 END 数量3
FROM
(SELECT '甲' [t],[名称],[数量] FROM #甲表
union all SELECT '乙' [t],[名称],[数量] FROM #乙表
union all SELECT '丙' [t],[名称],[数量] FROM #丙表
) Tb
create table aa(name varchar2(2), num integer);
create table bb(name varchar2(2), num integer);
create table cc(name varchar2(2), num integer);insert into aa values('A',1);
insert into aa values('B',2);
insert into aa values('C',3);insert into bb values('B',1);
insert into bb values('C',2);
insert into bb values('D',3);insert into cc values('C',1);
insert into cc values('D',2);
insert into cc values('E',3);select a.name as name,
NVL(aa.num,0) as num, NVL(bb.num,0) as num, NVL(cc.num,0) as num
from
((select name from aa union select name from bb where name not in (select name from aa)
union select name from cc where name not in (select name from bb)))a
left join aa on a.name = aa.name
left join bb on a.name = bb.name left join cc on a.name = cc.name
order by namedrop table aa;
drop table bb;
drop table cc;
select A,B,C,'0' D ,'0' E from tb_t1
UNION
select '0' A,B,C,D,'0' E from tb_t2
UNION
select '0' A,'0' B,C,D,E from tb_t3 ) order by rownum desc
的名称重复了,跟需求不符哦
from
(
select isnull(A1.名称,A2.名称) 名称,isnull(A1.数量,0) 数量1,isnull(A2.数量) 数量2
from
(
(select t1.名称,sum(t1.数量) from 甲表 t1 group by t1.名称) as A1 full join
(select t2.名称,sum(t2.数量) from 乙表 t2 group by t2.名称) as A2 on A1.名称=A2.名称
) as A3
full join (select t3.名称,sum(t3.数量) from 丙表 t3 group by t3.名称) as A4 on A3.名称=A4.名称
)
gocreate table a1(name1 varchar(50),num int)
insert into a1 (name1,num) select 'a',1 union select 'b',2 union select 'c',3
create table a2(name1 varchar(50),num int)
insert into a2 (name1,num) select 'b',1 union select 'c',2 union select 'd',3
create table a3(name1 varchar(50),num int)
insert into a3 (name1,num) select 'c',1 union select 'd',2 union select 'e',3
select a.name1,
isnull(a1.num,0) as num1,
isnull(a2.num,0) as num2,
isnull(a3.num,0) as num3
from
(select name1 from a1 union select name1 from a2 union select name1 from a3) as a
left join a1 on a1.name1 = a.name1
left join a2 on a2.name1 = a.name1
left join a3 on a3.name1 = a.name1
drop table a1
drop table a2
drop table a3
gocreate table a1(name1 varchar(50),num int)
insert into a1 (name1,num) select 'a',1 union select 'b',2 union select 'c',3
create table a2(name1 varchar(50),num int)
insert into a2 (name1,num) select 'b',1 union select 'c',2 union select 'd',3
create table a3(name1 varchar(50),num int)
insert into a3 (name1,num) select 'c',1 union select 'd',2 union select 'e',3
select a.name1,
isnull(a1.num,0) as num1,
isnull(a2.num,0) as num2,
isnull(a3.num,0) as num3
from
(select name1 from a1 union select name1 from a2 union select name1 from a3) as a
left join a1 on a1.name1 = a.name1
left join a2 on a2.name1 = a.name1
left join a3 on a3.name1 = a.name1
drop table a1
drop table a2
drop table a3
select 名称, 数量 数量1,0 数量2, 0 数量3 from 甲表
union
select 名称, 0 数量1,数量 数量2, 0 数量3 from 乙表
union
select 名称, 0 数量1,0 数量2, 数量 数量3 from 乙表
) a
group by 名称
但是如果不是固定的话, 好像有难度 等待高手给出一个通用的答案。
这种帖子早该结了,无语,看到这种帖子还回了干嘛
isnull((select num from c where a.name=c.name),0) as num3 from a
union
select name,isnull((select num from a where c.name=a.name),0) as num1,isnull((select num from b where c.name=b.name),0) as num2,
num as num3
from c where c.name <>'C'