问一道SQL的题 (转载) - 未名空间MITBBS历史存档

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

Redian新闻

>未名空间

>Database - 数据库

问一道SQL的题 (转载)

问一道SQL的题 (转载)# Database - 数据库

p*c2009-02-23 08:02

1 楼

【以下文字转载自 JobHunting 讨论区】
发信人: pic (喜欢画画么?), 信区: JobHunting
标题: 问一道SQL的题
发信站: BBS 未名空间站 (Mon Feb 23 14:52:14 2009)
怎么在一个TABLE的一个格子里插入MULTIPLE VALUE?
举个例子有个TABLE1
employee_id, employee_name, employee_code
1 Mary a, b, c
2 Nancy a
3 John c, m
4 Leo b, a
employee_code是从另外一个table2读进来的
比如Mary在table2里
employee_id, employee_code
1 a
1 b
1 c
怎么写sql的语句能update tabl1, 把a,b,c全都写到field

c*t2009-02-23 08:02

2 楼

Try either nested database or data array.

【在 p*c 的大作中提到】

: 【以下文字转载自 JobHunting 讨论区】
: 发信人: pic (喜欢画画么?), 信区: JobHunting
: 标题: 问一道SQL的题
: 发信站: BBS 未名空间站 (Mon Feb 23 14:52:14 2009)
: 怎么在一个TABLE的一个格子里插入MULTIPLE VALUE?
: 举个例子有个TABLE1
: employee_id, employee_name, employee_code
: 1 Mary a, b, c
: 2 Nancy a
: 3 John c, m

B*g2009-02-23 08:02

3 楼

kick.
啥DB啥version都不说？
oracle 9i + 有solution，oracle 10g + 有function。
要是oracle你会这篇，明天贴答案。
（其实考古前面有答案）

【在 p*c 的大作中提到】

p*c2009-02-23 08:02

4 楼

by default SQL refers to MS SQL Server?
thanks anyway.

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

: kick.
: 啥DB啥version都不说？
: oracle 9i + 有solution，oracle 10g + 有function。
: 要是oracle你会这篇，明天贴答案。
: （其实考古前面有答案）

B*g2009-02-23 08:02

5 楼

default is oracle.

【在 p*c 的大作中提到】

: by default SQL refers to MS SQL Server?
: thanks anyway.

B*g2009-02-23 08:02

6 楼

BTW, no sql solution for sql server till 2008

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

: default is oracle.

c*t2009-02-23 08:02

7 楼

不会吧。最多写个 aggregate 就是了。

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

: BTW, no sql solution for sql server till 2008

B*g2009-02-23 08:02

8 楼

no user-defined sp allowed

【在 c*****t 的大作中提到】

: 不会吧。最多写个 aggregate 就是了。

w*r2009-02-23 08:02

9 楼

default is teradata ..

e

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

: default is oracle.

i*i2009-02-23 08:02

10 楼

select employee_id,max(sys_connect_by_path(employee_code,',')) result from
(
select employee_id,employee_code,(row_number() over(order by employee
_id,employee_code desc) + rank() over(order by employee_id)) rn
from table2
)
connect by rn-1 = prior rn
group by employee_id;
oracle 9i或以上可实现，结果是对table2聚合
出现
employee_id employ_code
1 a,b,c
接下来就是再写个outer join了就不说了

B*g2009-02-23 08:02

11 楼

actually default is access

【在 w*r 的大作中提到】

: default is teradata ..
:
: e

c*t2009-02-23 08:02

12 楼

那无非是 Oracle 提供了个 sp 。。。多此一举嘛。写个 aggregate 也没多难。

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

: no user-defined sp allowed

B*g2009-02-23 08:02

13 楼

用sql比用sp县得nb, hehe.
要我写sp，我这个肯定用java

【在 c*****t 的大作中提到】

: 那无非是 Oracle 提供了个 sp 。。。多此一举嘛。写个 aggregate 也没多难。

p*c2009-02-23 08:02

14 楼

declare @emp_code varchar(1000)
select @emp_code = COALESCE(@emp_code + ' ', '') + table2.emp_code
from table1 table1
LEFT JOIN table2 table2 ON table1.emp_id = table2.emp_id
where emp_id = 1
select @emp_code
this does return for Mary who is emp_id=1:
a b c
however, if i supply more emp_id in the where clause, for example:
declare @emp_code varchar(1000)
select @emp_code = COALESCE(@emp_code + ' ', '') + table2.emp_code
from table1 table1
LEFT JOIN table2 table2 ON table1.emp_id = table2.emp_id

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

: 用sql比用sp县得nb, hehe.
: 要我写sp，我这个肯定用java

p*c2009-02-23 08:02

15 楼

i am using sql server 2008.
what's the solution?

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

: BTW, no sql solution for sql server till 2008

t*n2009-02-23 08:02

16 楼

This is easy in SQL Server
update t1
set t1.employee_code = comb
from TABLE1 t1 join
(
SELECT employee_id,
STUFF((SELECT ',' + employee_code AS [text()]
FROM TABLE2 AS G2
WHERE G2.employee_id = G1.employee_id
ORDER BY employee_code
FOR XML PATH('')), 1, 1, '') AS comb
FROM TABLE2 AS G1
GROUP BY employee_id
) t2
on t1.employee_id = t2.employee_id

【在 p*c 的大作中提到】

: i am using sql server 2008.
: what's the solution?

B*g2009-02-23 08:02

17 楼

赞一个，这个2005有还是一定要2008

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

: This is easy in SQL Server
: update t1
: set t1.employee_code = comb
: from TABLE1 t1 join
: (
: SELECT employee_id,
: STUFF((SELECT ',' + employee_code AS [text()]
: FROM TABLE2 AS G2
: WHERE G2.employee_id = G1.employee_id
: ORDER BY employee_code

t*n2009-02-23 08:02

18 楼

it works on 2005 & 2008

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

: 赞一个，这个2005有还是一定要2008

p*c2009-02-23 08:02

19 楼

很棒! 包子给你和Beijing. 谢谢.

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

B*g2009-02-23 08:02

20 楼

can you compare the performance between this one and the store procedure one?

【在 p*c 的大作中提到】

: 很棒! 包子给你和Beijing. 谢谢.

c*t2009-02-23 08:02

21 楼

那个 STUFF function 就是个 aggregate 吧。这样的话，应该没啥
太大的区别。

one?

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

: can you compare the performance between this one and the store procedure one?

p*c2009-02-23 08:02

22 楼

oh boy, i happened to have time to play with it today. you know what, this
STUFF is much faster than an aggregated function. again, my company is on
sql2008.
http://www.kodyaz.com/articles/concatenate-using-xml-path.aspx

one?

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

: can you compare the performance between this one and the store procedure one?

p*c2009-02-23 08:02

23 楼

here's more idea about the comparison...
i was testing on a table with 100K records, updating which by merging
multiple values from multiple rows in another table into one field for one
column per record. STUFF was completed in 14 seconds (I was stunned), vesus,
my cursor aggregation fn got everything returned in 02:43 minutes...
thought i'd brush up my XML feature from now on...

【在 p*c 的大作中提到】

: oh boy, i happened to have time to play with it today. you know what, this
: STUFF is much faster than an aggregated function. again, my company is on
: sql2008.
: http://www.kodyaz.com/articles/concatenate-using-xml-path.aspx
:
: one?