有趣的Join问题，源于SQL和SAS比较。 - 未名空间MITBBS历史存档

国际科技财经博客移民网络热点娱乐民生时事公众号

Redian新闻

>未名空间

>Database - 数据库

有趣的Join问题，源于SQL和SAS比较。

有趣的Join问题，源于SQL和SAS比较。# Database - 数据库

n*62009-03-15 07:03

1 楼

tableA
id name salary06 ...
1 mike 1000
2 john 2000
4 tom 3000
tableB
id name income06 income07 ...
1 mike 1000 ...
2 john 3000
3 smith 3000
5 Leo 5000
Objective:
- Combine all the records from two tables into tableC(id, name, income)
- For a specific id, if B.income exists, use that for C.income.
- For a specific id, If B.income does not exist, use A.salary for C.income
Expected outcome tableC
id name income06 income07 ...
1 mike 1000 ...
2 john 3000
3 smith

B*g2009-03-15 07:03

2 楼

I don't believe SAS invent merge.

【在 n********6 的大作中提到】

: tableA
: id name salary06 ...
: 1 mike 1000
: 2 john 2000
: 4 tom 3000
: tableB
: id name income06 income07 ...
: 1 mike 1000 ...
: 2 john 3000
: 3 smith 3000

j*n2009-03-15 07:03

3 楼

Here is the core part in T-SQL, you finish the rest...
SELECT C.id, C.name, A.salary, B.income
FROM (
SELECT id, name
FROM tableA
UNION
SELECT id, name
FROM tableB
) C
LEFT JOIN tableA ON C.id = A.id
LEFT JOIN tableB ON C.id = B.id

【在 n********6 的大作中提到】

: tableA
: id name salary06 ...
: 1 mike 1000
: 2 john 2000
: 4 tom 3000
: tableB
: id name income06 income07 ...
: 1 mike 1000 ...
: 2 john 3000
: 3 smith 3000

B*g2009-03-15 07:03

4 楼

你需要升级了

【在 j*****n 的大作中提到】

: Here is the core part in T-SQL, you finish the rest...
: SELECT C.id, C.name, A.salary, B.income
: FROM (
: SELECT id, name
: FROM tableA
: UNION
: SELECT id, name
: FROM tableB
: ) C
: LEFT JOIN tableA ON C.id = A.id

j*n2009-03-15 07:03

5 楼

CTE SAS 用不了吧？

【在 B*****g 的大作中提到】

: 你需要升级了

B*g2009-03-15 07:03

6 楼

google "sql server 2008 merge"

【在 j*****n 的大作中提到】

: CTE SAS 用不了吧？

j*n2009-03-15 07:03

7 楼

Damn, i just need to retire....

【在 B*****g 的大作中提到】

: google "sql server 2008 merge"

o*52009-03-15 07:03

8 楼

Here is the Oracle code:
create table c
as
select nvl(a.id,b.id) id,nvl(a.name,b.name) name,nvl(b.income06,a.salary06)
income06
from a full outer join b
on a.id=b.id
order by 1;
I do not know whether you can use PROC SQL to achieve this same thing or not
.

B*g2009-03-15 07:03

9 楼

oracle merge

)
not

【在 o******5 的大作中提到】

: Here is the Oracle code:
: create table c
: as
: select nvl(a.id,b.id) id,nvl(a.name,b.name) name,nvl(b.income06,a.salary06)
: income06
: from a full outer join b
: on a.id=b.id
: order by 1;
: I do not know whether you can use PROC SQL to achieve this same thing or not
: .

o*52009-03-15 07:03

10 楼

I gave it a little more thought on this. In fact, you can use PROC SQL to do
this.
proc sql;
create table c
as
select coalese(a.id,b.id) as id,coalese(a.name,b.name) as name,coalese(b.
income06,a.salary06) as income06
from a full outer join b
on a.id=b.id
order by 1;
quit;