how to write this query - 未名空间MITBBS历史存档

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

Redian新闻

>未名空间

>Database - 数据库

how to write this query

how to write this query# Database - 数据库

m*h2008-08-28 07:08

1 楼

I have 2 tables, classes and classSchedules
Classes table has the following columns: ClassID, Title, Description, Length
, Price.
ClassSchedule table has ScheduleID, ClassID, StartDate.
I need a query to return the result shown below
Title Description StartDate Length Price
Class 1 desc. For Class 1 9/1/2008 5 days $1,000

c*d2008-08-28 07:08

2 楼

没看懂

Length

【在 m*****h 的大作中提到】

: I have 2 tables, classes and classSchedules
: Classes table has the following columns: ClassID, Title, Description, Length
: , Price.
: ClassSchedule table has ScheduleID, ClassID, StartDate.
: I need a query to return the result shown below
: Title Description StartDate Length Price
: Class 1 desc. For Class 1 9/1/2008 5 days $1,000

m*h2008-08-28 07:08

3 楼

I want to return those columns Title, description, StartDate, Length, Price.
Since each class may have serveral different startDate. I don't want the
Title and description repeated in the result.

B*g2008-08-28 07:08

4 楼

SELECT CASE c.ID
WHEN 1
THEN c.titel
ELSE NULL
END title,
CASE c.ID
WHEN 1
THEN c.description
ELSE NULL
END description,
c.startdate,
c.LENGTH,
c.price
FROM (SELECT a.titel,
a.description,
b.startdate,
a.LENGTH,
a.price,
ROW_NUMBER () OVER (PARTITION BY a.classid ORDER BY b.
scheduleid) ID
FROM classes a, classs

【在 m*****h 的大作中提到】

: I want to return those columns Title, description, StartDate, Length, Price.
: Since each class may have serveral different startDate. I don't want the
: Title and description repeated in the result.