sql question - 未名空间MITBBS历史存档

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

Redian新闻

>未名空间

>Database - 数据库

sql question

sql question# Database - 数据库

n*g2004-05-13 07:05

1 楼

there are records in Database such as
1 A
2 A
2 B
3 C
4 B
I want to return a recordset that combine the value of
the second column if the value of the first column is the same,
like below
1 A
2 AB
3 C
4 B
how to implement it using sql statement?

aw2004-05-13 07:05

2 楼

use a self-join.
SELECT T1.lastname + isnull(T2.lastname,'')
FROM MYTABLE T1 LEFT JOIN MYTABLE T2
ON T1.firstname = T2.firstname AND T1.id < T2.id

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

: there are records in Database such as
: 1 A
: 2 A
: 2 B
: 3 C
: 4 B
: I want to return a recordset that combine the value of
: the second column if the value of the first column is the same,
: like below
: 1 A

y*i2004-05-13 07:05

3 楼

I think this way the solution is not scalable. What if there are records like
You will get
Instead of

n*g2004-05-13 07:05

4 楼

doesn't work. the sql script returns:
1 A
2 AB
2 B
3 C
4 B
I don't want the third record....
btw: both columns are char

【在 aw 的大作中提到】

: use a self-join.
: SELECT T1.lastname + isnull(T2.lastname,'')
: FROM MYTABLE T1 LEFT JOIN MYTABLE T2
: ON T1.firstname = T2.firstname AND T1.id < T2.id

n*f2004-05-13 07:05

5 楼

建议您用程序去实现.
在ORACLE 您可以用PL/SQL 去LOOP 整个表.
您的问题主要是同表中行数据和行数据的关系处理.
总之,我认为写一段小程序比较能简单解决您的问题.

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

: doesn't work. the sql script returns:
: 1 A
: 2 AB
: 2 B
: 3 C
: 4 B
: I don't want the third record....
: btw: both columns are char

n*g2004-05-13 07:05

6 楼

yeah, I agree it is easy to implement in program.
but it is not my decision, sigh.

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

: 建议您用程序去实现.
: 在ORACLE 您可以用PL/SQL 去LOOP 整个表.
: 您的问题主要是同表中行数据和行数据的关系处理.
: 总之,我认为写一段小程序比较能简单解决您的问题.

n*f2004-05-13 07:05

7 楼

OK, Please tell us more about your Table:
1. What DB you are using? Access, SQL or Oracle?
2. What is possible value for ID field? 1,2,3..... What is the MAX value?
3. What is possible value for DATA field? Just A,B and C or have more ?
4. How many record do you have?
5. Why you have to use One SQL statement to do that?
Is it possible let me CALL you?

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

: yeah, I agree it is easy to implement in program.
: but it is not my decision, sigh.

aw2004-05-13 07:05

8 楼

还有个前面有人提到的问题，可不可能有：
2 A
2 B
2 C
。。。的情况？就说有未知个相同ID(不止2个)。

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

: OK, Please tell us more about your Table:
: 1. What DB you are using? Access, SQL or Oracle?
: 2. What is possible value for ID field? 1,2,3..... What is the MAX value?
: 3. What is possible value for DATA field? Just A,B and C or have more ?
: 4. How many record do you have?
: 5. Why you have to use One SQL statement to do that?
: Is it possible let me CALL you?