SQL query 一问 - 未名空间MITBBS历史存档

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

Redian新闻

>未名空间

>Database - 数据库

SQL query 一问

SQL query 一问# Database - 数据库

a*82008-11-07 08:11

1 楼

I have three tables,
Student(sID, name)
Course(cID, title)
Registration(sID, cID)
I'd like to get result-set with 2 columns.
name | Course title list separated by semicolon(;)
How to implement in SQL or stored procedure?

B*g2008-11-07 08:11

2 楼

oracle 里sql可以写，only work for 10g+
SELECT s.name, TRANSLATE(wmsys.wm_concat(c.title), ',', ';') AS "Course Titl
e Lis"
FROM Student s, Course c, Registration r
WHERE s.sid = r.sid
AND r.cid = c.cid
GROUP BY s.sid
Note 1: procedure 做个loop就行了。
Note 2：学生没选课sql需要改动。

【在 a******8 的大作中提到】

: I have three tables,
: Student(sID, name)
: Course(cID, title)
: Registration(sID, cID)
: I'd like to get result-set with 2 columns.
: name | Course title list separated by semicolon(;)
: How to implement in SQL or stored procedure?

M*g2008-11-07 08:11

3 楼

Just a quick thought not test yet, wish can give you some fresh idea. I skip
to directly write cID rather and Title but I think you could change that
later easily.
if objective_id('new_table') is not null drop table new_table
create table new_table(RowID int identity(1,1), sID int, name varchar(20),
course varchar(255))
declare @rc int, @looper int
declare @course varchar(255)
Insert new_table(sID,name)select distinct r.sID, s.name from Registration r,
Student s where r.sID = s.sID
set @rc = @@R

【在 a******8 的大作中提到】

n*62008-11-07 08:11

4 楼

Another logic is to use cursor. By using cursor, we can do not use the
identity column.

skip
r,

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

: Just a quick thought not test yet, wish can give you some fresh idea. I skip
: to directly write cID rather and Title but I think you could change that
: later easily.
: if objective_id('new_table') is not null drop table new_table
: create table new_table(RowID int identity(1,1), sID int, name varchar(20),
: course varchar(255))
: declare @rc int, @looper int
: declare @course varchar(255)
: Insert new_table(sID,name)select distinct r.sID, s.name from Registration r,
: Student s where r.sID = s.sID

a*82008-11-07 08:11

5 楼

which one is efficient for following two options? I guess option 2 is better
bcz it doesn't explicitly define temp tbl.
Option 1: Use embbed cursors
Student(sID, name)
Course(cID, title)
Registration(sID, cID)
declare tmpTbl (name, titleList)
Loop cursor1 AS (select sID, name from Student)
{
set @titleList = ""
inner Loop cusor2 AS (select title from Course c, Registration r
where r.sID = @sID(get from cursor1) and r.cID = c.cID)
{
set @titleList = @titleList + ";" + @title (get from

j*n2008-11-07 08:11

6 楼

temp table is more efficient than cursor.

better

【在 a******8 的大作中提到】

: which one is efficient for following two options? I guess option 2 is better
: bcz it doesn't explicitly define temp tbl.
: Option 1: Use embbed cursors
: Student(sID, name)
: Course(cID, title)
: Registration(sID, cID)
: declare tmpTbl (name, titleList)
: Loop cursor1 AS (select sID, name from Student)
: {
: set @titleList = ""

B*g2008-11-07 08:11

7 楼

ding。
传言oralce10g cursor优化了，呵呵。

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

: temp table is more efficient than cursor.
:
: better

n*62008-11-07 08:11

8 楼

Would you explain a little bit how to use temp table and not use cursor in
this case?
Can you provide any comments if I do in this way? (MSSQL)
IF OBJECT_ID('TEMPDB..TEMPTABLE') > 0
DROP ##TEMPTABLE
CREATE ##TEMPTABLE
CREATE INDEX ... (IF NECESSARY, NONCLUSTERED PREFERRED IN GENERAL)
DECLARE CURSOR
OPEN CURSOR
FETCH CURSOR INTO @COLUMN_1, @COLUMN_2, @COLUMN_3
WHILE (@@FETCH_STATUS = 0)
SELECT FUNC(COLUMN_1, COLUMN_2), COLUMN_3
INTO (COLUMN_A, COLUMN_B)
FROM ORIGINAL_TABLE
FETCH NEXT FROM CURSOR

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

: temp table is more efficient than cursor.
:
: better

a*82008-11-07 08:11

9 楼

Would you double-check it? Option 1 use two cursors & tmp tbl with insert
and select option, option 2 use 1 cursor & 1 select.

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

: temp table is more efficient than cursor.
:
: better

a*82008-11-07 08:11

10 楼

I think one select is ok, no need use cursor.
SELECT FUNC(COLUMN_1, COLUMN_2), COLUMN_3
FROM ORIGINAL_TABLE;

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

: Would you explain a little bit how to use temp table and not use cursor in
: this case?
: Can you provide any comments if I do in this way? (MSSQL)
: IF OBJECT_ID('TEMPDB..TEMPTABLE') > 0
: DROP ##TEMPTABLE
: CREATE ##TEMPTABLE
: CREATE INDEX ... (IF NECESSARY, NONCLUSTERED PREFERRED IN GENERAL)
: DECLARE CURSOR
: OPEN CURSOR
: FETCH CURSOR INTO @COLUMN_1, @COLUMN_2, @COLUMN_3

S*k2008-11-07 08:11

11 楼

If you use ms sql, “for xml” may be helpful.
select
S.Name,
(select C.Title
from Registration R
inner join Course C ON (R.CID = C.CID)
where R.SID = S.SID
for xml raw)
from Student S
then convert xml to varchar and get the desired format using string
functions.

a*82008-11-07 08:11

12 楼

I'm afraid that "for xml raw" is not efficient to process large volume data.
Does sql have similar function like wmsys.wm_concat provided by oracle?

B*g2008-11-07 08:11

13 楼

even for oracle, the function is undocument. Maybe sql server also has undoc
ument functions?

data.

【在 a******8 的大作中提到】

: I'm afraid that "for xml raw" is not efficient to process large volume data.
: Does sql have similar function like wmsys.wm_concat provided by oracle?

S*k2008-11-07 08:11

14 楼

如果考虑performance, 这样的task就不应该完全依赖在database里完成. loop 放到
application里, database的压力会小得多.

T*92008-11-07 08:11

15 楼

select Student.name, Course.title
From Student, Course, Registration

【在 a******8 的大作中提到】

a*82008-11-07 08:11

16 楼

你是说直接返回所有result-set (name, title)给application吗？
select s.name, c.title
from Student s, Course c, Registration r
where (s.sId = r.sId AND c.cID = r.cID)
这样result-set中name会重复多次，加大Application和Database之间的网络开销。

【在 S***k 的大作中提到】

: 如果考虑performance, 这样的task就不应该完全依赖在database里完成. loop 放到
: application里, database的压力会小得多.

S*k2008-11-07 08:11

17 楼

The size of return set is almost double. If the servers have to communicate
through internet, double load would be a big suck. If each name/title has 30
characters, each student enrolled 50 courses, and there are 30000 students
in the database, there would be more 45M byte load over the network.
If the network speed is not an issue and the DB is busy, you may like to
simply return the names and titles back to application. Otherwise, ask DB
return the final results set to save network.