One more out join syntax (+) observed.
Setup
drop table t1 purge;
drop table t2 purge;
drop table t3 purge;
create table t1 (id number);
create table t2 (id number);
create table t3 (id number);
insert into t1(id) select rownum from dual connect by level <= 3;
insert into t2(id) select rownum from dual connect by level <= 2;
insert into t3(id) select rownum from dual connect by level <= 1;
commit;
select t1.id t1_id, t2.id t2_id
from t2,t1
where t1.id = t2.id(+)
and (t2.id= 1 or t2.id is null)
--and t2.id(+) = 1
order by t1.id;
T1_ID T2_ID
------- ----------
1 1
3
Here is a new syntax I learned last week.
The output is strange.
select t1.id t1_id, t2.id t2_id
from t2,t1
where t1.id = t2.id(+)
and t2.id(+) = 1
order by t1.id;
T1_ID T2_ID
------- ----------
1 1
2
3
Monday, November 30, 2015
Exercise: Maps 第一段 Go 语言测试通过了!
打算转换职业方向,做Golang程序设计师。
第一段代码测试通过了,好激动!
My code:
package main
import (
"golang.org/x/tour/wc"
"strings"
)
func WordCount(s string) map[string]int {
var la_s = strings.Split(s, " ")
//lm_s := map[string]int{} // simpler
var lm_s map[string]int
lm_s = make(map[string]int)
for _, word := range la_s {
lm_s[word] = lm_s[word] + 1
}
return lm_s
//return map[string]int{"x": 1}
}
func main() {
wc.Test(WordCount)
}
Output:
第一段代码测试通过了,好激动!
My code:
package main
import (
"golang.org/x/tour/wc"
"strings"
)
func WordCount(s string) map[string]int {
var la_s = strings.Split(s, " ")
//lm_s := map[string]int{} // simpler
var lm_s map[string]int
lm_s = make(map[string]int)
for _, word := range la_s {
lm_s[word] = lm_s[word] + 1
}
return lm_s
//return map[string]int{"x": 1}
}
func main() {
wc.Test(WordCount)
}
Output:
PASS
f("I am learning Go!") =
map[string]int{"I":1, "am":1, "learning":1, "Go!":1}
PASS
f("The quick brown fox jumped over the lazy dog.") =
map[string]int{"over":1, "the":1, "The":1, "quick":1, "brown":1, "jumped":1, "fox":1, "lazy":1, "dog.":1}
PASS
f("I ate a donut. Then I ate another donut.") =
map[string]int{"ate":2, "a":1, "donut.":2, "Then":1, "another":1, "I":2}
PASS
f("A man a plan a canal panama.") =
map[string]int{"panama.":1, "A":1, "man":1, "a":2, "plan":1, "canal":1}
Program exited.
Reference:
Tuesday, November 03, 2015
2 level left outer join
Here is Oracle outer join behavior observation.
We get same results between Oracle notation (+), and ANSI join syntax (LEFT OUTER JOIN)
Oracle 12.1.0.2.0
drop table t1 purge;
drop table t2 purge;
drop table t3 purge;
create table t1 (id number);
create table t2 (id number);
create table t3 (id number);
insert into t1(id) select rownum from dual connect by level <= 3;
insert into t2(id) select rownum from dual connect by level <= 2;
insert into t3(id) select rownum from dual connect by level <= 1;
commit;
select t1.id t1_id, t2.id t2_id, t3.id t3_id
from t3,t2,t1
where t1.id = t2.id(+)
and t2.id = t3.id(+)
order by t1.id;
T1_ID T2_ID T3_ID
------- ------- --------
1 1 1
2 2
3
drop table t1 purge;
drop table t2 purge;
drop table t3 purge;
create table t1 (id number);
create table t2 (id number, t1_id number);
create table t3 (id number, t2_id number);
insert into t1(id) select rownum from dual connect by level <= 3;
insert into t2(id, t1_id) select rownum, rownum from dual connect by level <= 2;
insert into t3(id, t2_id) select rownum, rownum from dual connect by level <= 1;
commit;
select t1.id, t2.id t2_id, t2.t1_id, t3.id t3_id, t3.t2_id
from t3,t2,t1
where t1.id = t2.t1_id(+)
and t2.id = t3.t2_id(+)
order by t1.id;
ID T2_ID T1_ID T3_ID T2_ID
---- ------ ------ ------ ------
1 1 1 1 1
2 2 2
3
Your homework is to write ANSI join syntax (LEFT OUTER JOIN) SQL, to get same result. ^_^
We get same results between Oracle notation (+), and ANSI join syntax (LEFT OUTER JOIN)
Oracle 12.1.0.2.0
drop table t1 purge;
drop table t2 purge;
drop table t3 purge;
create table t1 (id number);
create table t2 (id number);
create table t3 (id number);
insert into t1(id) select rownum from dual connect by level <= 3;
insert into t2(id) select rownum from dual connect by level <= 2;
insert into t3(id) select rownum from dual connect by level <= 1;
commit;
select t1.id t1_id, t2.id t2_id, t3.id t3_id
from t3,t2,t1
where t1.id = t2.id(+)
and t2.id = t3.id(+)
order by t1.id;
T1_ID T2_ID T3_ID
------- ------- --------
1 1 1
2 2
3
drop table t1 purge;
drop table t2 purge;
drop table t3 purge;
create table t1 (id number);
create table t2 (id number, t1_id number);
create table t3 (id number, t2_id number);
insert into t1(id) select rownum from dual connect by level <= 3;
insert into t2(id, t1_id) select rownum, rownum from dual connect by level <= 2;
insert into t3(id, t2_id) select rownum, rownum from dual connect by level <= 1;
commit;
select t1.id, t2.id t2_id, t2.t1_id, t3.id t3_id, t3.t2_id
from t3,t2,t1
where t1.id = t2.t1_id(+)
and t2.id = t3.t2_id(+)
order by t1.id;
ID T2_ID T1_ID T3_ID T2_ID
---- ------ ------ ------ ------
1 1 1 1 1
2 2 2
3
Your homework is to write ANSI join syntax (LEFT OUTER JOIN) SQL, to get same result. ^_^
Subscribe to:
Posts (Atom)