数据库合并问题(50分)

Z

zhj218

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-04-30

• #1

如：
字段 No.1 No.2 No.3 No.4
数据：
1 2 3 a
1 2 3 b
1 2 3 c
2 3 4 a
2 3 4 c
3 4 5 a
3 4 5 b
3 4 5 c
3 4 5 d
如何编程实现以下结果：
字段 No.1 No.2 No.3 No.4　a b c d
数据：
1 2 3 a b c
2 3 4 a c
3 4 5 a b c d

 

Z

zhj218

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-05-09

• #2

没人回答，各位高手请多指教

 

U

ugvanxk

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-05-09

• #3

select no1,no2,no3,no_4='',
max(case no4 when a then a else '' end) as a
max(case no4 when b then b else '' end) as b

from tablename
group by no1,no2,no3,no_4
不知行不行

 

D

dgbsnake

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-05-14

• #4

with temp1 as
(select n1,n2,n3 from tablename group by n1,n2,n3),
temp2 as
(select n1,n2,n3,n4 from tablename where n4='a'),
temp3 as
(select n1,n2,n3,n4 from tablename where n4='b'),
temp4 as
(select n1,n2,n3,n4 from tablename where n4='c'),
temp5 as
(select n1,n2,n3,n4 from tablename where n4='d')
, result as
((((select temp1.n1,temp1.n2,temp1.n3 left join temp2.n4 on
temp1.n1=temp2.n1 and temp1.n2=temp2.n2 and temp1.n3=temp3.n3)
left join temp3.n4 on temp1.n1=temp3.n1 and temp1.n2=temp3.n2 and temp1.n3=temp3.n3))
left join temp4.n4 on temp1.n1=temp4.n1 and temp1.n2=temp4.n2 and temp1.n3=temp4.n3))
left join temp5.n4 on temp1.n1=temp5.n1 and temp1.n2=temp5.n2 and temp1.n3=temp5.n3)
select * from result 即可！

 

福

福利彩票

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-05-14

• #5

请问：no.4 的项目随意的吗？如果那样的话，我看想如此合并就并非你想象中的简单了！

 

S

SHEN_HONG

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-05-20

• #6

比较简单的方法是先把NO.1,NO.2,NO.3取过来不要重复,
再按记录处理或用SQL语句按字段处理,此方法缺点是效率较差

 

Z

zhj218

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-05-27

• #7

no.4 的项目随意的

 

H

huangHe

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-05-29

• #8

同意SHEN_HONG的看法，简单的些好！

 

Z

zhj218

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-05-29

• #9

请问这样的sql语句如何实现

 

福

福利彩票

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-05-29

• #10

你的意思是随着no.4情况的增多，查询结果中的项也增多，你太天真了！好可爱！

 

春

春天的故事

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-06-06

• #11

1. Use a Stored Procedure, 'cause it has been compiled, optimized, and it's
fast.
2. Set up a cursor : SELECT DISTINCT N1,N2,N3 FROM table
3. You can probably use only ONE field (N4) to store all the details, rather
than creating many fields.

Example shown as follows:
N1 N2 N3 N4
1 2 3 abc

because:
1. you don't know how many possible values you will have in N4.
2. by doing this, you can easily get the individual value from N4
if you want.

Hope it's clear. Good Luck!

 

W

wangnen

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-06-06

• #12

好象没什么实际意义?

 

Z

zhj218

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-12-05

• #13

接受答案了.