Question

如何写SQL语句获取下数据？

一个表：num1与num2全部是float
id name1 Num1 name2 Num2
1 张三 1.5 李四 2.3
2 王五 1.2 刘六 2.2
3 李四 2.2 王八 1.3
4 刘六 0.3 null null
如何得到下列数据的统计表：
name num3 num4
张三 1.5 0
李四 2.2 2.3
王五 1.2 0
刘六 0.3 2.3
王八 0 1.3

Answer 1

我提供两种做法：
1.
select A.name , nvl(B.Num1,0) num3 , nvl(C.Num2,0) num4
from ( select name1 name from Ts union select name2 name from Ts ) as A
left join ( select name1 name , Num1 from Ts ) B on A.name = B.name
left join ( select name2 name , Num2 from Ts ) C on A.name = C.name
where A.name is not null
2.
select nvl(A.name1,B.name2) name ,
nvl(A.Num1,0) num3 ,
nvl(B.Num2,0) num4
from Ts A full outer join Ts B
on A.name1 = B.name2
where A.name1 is not null and B.name2 is not null
第一个有点笨，弄三个结果集Left join
第二个比较简单，就俩结果集Full join

追问

'nvl' 不是可以识别的 函数名。

追答

啥类型的数据库 ，你换成对应的函数就可以了

Answer 2

insert into table2
select name1,sum(num1) from table1 group by name1 where name1 is not null group by name1
union all
select name2,sum(num2) from table1 group by name2 where name2 is not null group by name2

如果NAME1和NAME2没有重复的话 用上面

追问

如有有重复的呢

追答

select name ,sum(num)
from (
select name1 as name ,sum(num1) as num from table1 group by name1 where name1 is not null group by name1
union all
select name2 as name ,sum(num2)as num from table1 group by name2 where name2 is not null group by name2
)tmp
group by name

如果不去重的话这样
如果要去重
则把UNION all换成UNION

Answer 3

select c.name,c.num3, isnull(d.Num2,0) as num4
from
(select a.name, isnull(b.Num1,0) as num3
from
(select name1 as name from tablename
union
select name2 from tablename) as a
left join tablename as b on a.name = b.name1)
as c
left join tablename as d on c.name = d.name2

Answer 4

select name1,num3,num4 from 表名

追问

上面是表，下面的是需要得到的结果！