udacity新课程Introduction to programming in Java - 未名空间MITBBS历史存档

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

Redian新闻

>未名空间

>Java - 爪哇娇娃

udacity新课程Introduction to programming in Java

udacity新课程Introduction to programming in Java# Java - 爪哇娇娃

n*u2013-06-10 07:06

1 楼

Sorry to bother High Hands here.
Two tables, a, b
a has uid(userid), gid(groupid), date, active
b has uid, gid, date(it's different from a.date)
a and b have some common uid and gid.
If I want to combine these two tables:
SELECT a.gid as gid, a.uid as uid
FROM a
UNION
SELECT b.gid as gid, b.uid as uid
FROM b
Right now, I have this table which has all uid and gid (distinct)
Then I want to get a table with columns uid, gid, date(if b has date, use b.
date), active
How can I do?
Thanks

J*n2013-06-10 07:06

2 楼

Introduction to programming in Java
https://www.udacity.com/course/cs046

B*g2013-06-10 07:06

3 楼

Suppose (uid,gid) is unique on a
Suppose (uid,gid) is unique on b
Suppose you want table c
INSERT INTO c(uid, gid, date, active)
SELECT uid, gid, date, active
FROM a;
MERGE INTO c
USING b
ON (c.uid = b.uid AND c.gid = b.gid)
WHEN MATCHED THEN
UPDATE SET c.date = (CASE WHEN b.date IS NOT NULL THEN b.date
ELSE c.date
END)
WHEN NOT MATCHED THEN
INSERT (c.uid, c.gid, c.date, c.active)
VALUES (b.uid, b.gid, b.date, NULL);

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

: Sorry to bother High Hands here.
: Two tables, a, b
: a has uid(userid), gid(groupid), date, active
: b has uid, gid, date(it's different from a.date)
: a and b have some common uid and gid.
: If I want to combine these two tables:
: SELECT a.gid as gid, a.uid as uid
: FROM a
: UNION
: SELECT b.gid as gid, b.uid as uid

W*o2013-06-10 07:06

4 楼

谢谢

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

: Introduction to programming in Java
: https://www.udacity.com/course/cs046

n*u2013-06-10 07:06

5 楼

Thanks Beijing!
I am using mysql. it seems mysql does not support MERGE INTO. Instead it has
replace.
I am reading it.
Thanks a lot!

s*22013-06-10 07:06

6 楼

谢谢

n*62013-06-10 07:06

7 楼

a.uid, a.gid, a.date, a.active
b.uid, b.gid, b.date
SELECT a.uid, a.gid, ISNULL(b.date, a.date), a.active
INTO ##TEMP
FROM a, b
where a.uid = b.uid
没有测试过，不知道行不行。
beijing，用merge有什么好处啊？我没用过merge

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

J*n2013-06-10 07:06

8 楼

Introduction to programming in Java
https://www.udacity.com/course/cs046

B*g2013-06-10 07:06

9 楼

你这个miss了a有b没有，b有a没有的。
merge是个好东西。

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

: a.uid, a.gid, a.date, a.active
: b.uid, b.gid, b.date
: SELECT a.uid, a.gid, ISNULL(b.date, a.date), a.active
: INTO ##TEMP
: FROM a, b
: where a.uid = b.uid
: 没有测试过，不知道行不行。
: beijing，用merge有什么好处啊？我没用过merge

W*o2013-06-10 07:06

10 楼

谢谢

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

: Introduction to programming in Java
: https://www.udacity.com/course/cs046

n*62013-06-10 07:06

11 楼

漏看了一句话：
“a and b have some common uid and gid.”
merge确实好啊。等有机会用用看。
既然some common uid and gid，那么用outer join，两次staging。(未经测试）
SELECT a.uid, a.gid, ISNULL(b.date, a.date) as date, /*ISNULL as可能不行，可定义table*/
a.active, b.uid as buid, b.gid as bgid, b.date as bdate
INTO ##c1
FROM a
OUTER JOIN b
ON a.uid = b.uid
SELECT c.uid, c.gid, c.date, c.active
INTO ##c2
FROM ##c1
WHERE c.uid <> c.buid
AND c.uid IS NOT NULL
AND c.buid IS NOT NULL

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

: 你这个miss了a有b没有，b有a没有的。
: merge是个好东西。

s*22013-06-10 07:06

12 楼

谢谢

j*n2013-06-10 07:06

13 楼

没必要的，人只要 b 有的就行。
neil222966 的有点错，改了下
SELECT a.uid, a.gid, ISNULL(b.date, a.date), a.active
INTO ##TEMP
FROM a, b
where a.uid = b.uid
and a.gid = b.gid

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

: 你这个miss了a有b没有，b有a没有的。
: merge是个好东西。

g*w2013-06-10 07:06

14 楼

大家觉得这个讲得怎么样？
正准备找个JAVA教程学学

B*g2013-06-10 07:06

15 楼

人家题目没说只要用b fix 一下 a

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

: 没必要的，人只要 b 有的就行。
: neil222966 的有点错，改了下
: SELECT a.uid, a.gid, ISNULL(b.date, a.date), a.active
: INTO ##TEMP
: FROM a, b
: where a.uid = b.uid
: and a.gid = b.gid

H*72013-06-10 07:06

16 楼

199块。疯了

d*82013-06-10 07:06

17 楼

也不是很好，只支持insert,update & delete,不支持select

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

: 你这个miss了a有b没有，b有a没有的。
: merge是个好东西。

s*12013-06-10 07:06

18 楼

我上过免费时候的，如果没有变化的话，这门课不行，啥也干不了。

n*62013-06-10 07:06

19 楼

I looked up the merge command in sql 2005 help file but did not find the
usage of merge table.
I googled and found merge table is a new feature in 2008.
I get a conclusion that 2005 does not support merge table.

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

: 你这个miss了a有b没有，b有a没有的。
: merge是个好东西。

j*n2013-06-10 07:06

20 楼

here...
date(if b has date, use b.date)

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

: 人家题目没说只要用b fix 一下 a

B*g2013-06-10 07:06

21 楼

那是说inner join的部分，outer 的没说

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

: here...
: date(if b has date, use b.date)