请教一个很折腾人的SQL语句

[jsksmark]

class表

classid orgid coursesid classsettime

courses表

coursesid orgid isvouch price cheaper


org表

orgid orgtitle

现在以class表为主线，调出前十条数据

select top 10 classid,coursestitle,orgid,orgtitle,price,cheaper,isvouch,classsettime from class c,org o,courses x where c.orgid=o.orgid and

c.coursesid=x.coursesid order by classsettime asc

并且这些数据，要满足的条件是
每条数据之间的orgid不一样，classsettime>getdate()，并且isvouch=1,请问这个SQL语句该怎么完善呢？

[claro]

/*
class表

classid    orgid    coursesid    classsettime

courses表

coursesid  orgid  isvouch  price cheaper


org表

orgid orgtitle

现在以class表为主线，调出前十条数据

select top 10  classid,coursestitle,orgid,orgtitle,price,cheaper,isvouch,classsettime
 from class c,org o,courses x
 where c.orgid=o.orgid and c.coursesid=x.coursesid
 order by classsettime asc

并且这些数据，要满足的条件是
每条数据之间的orgid不一样，classsettime>getdate()，并且isvouch=1,请问这个SQL语句该怎么完善呢？
*/
select top 10  classid,coursestitle,orgid,orgtitle,price,cheaper,isvouch,classsettime
 from class c,org o,courses x
 where c.orgid=o.orgid and c.coursesid=x.coursesid
 and c.classsettime>getdate() and x.isvouch=1 --添加部分
 order by classsettime asc

[claro]

建议楼主给出数据。

[子陌红尘]

楼主请说明表之间的关联，以及表之间数据是一对一或一对多的关系，最好给出测试数据。

[ws_hgo]

给点测试数据

[csdyyr]

每条数据之间的orgid不一样
-->是不是连续的两条数据orgid不能相同？最好给出数据。

[子陌红尘]

try:

select
    top 10 *
from
    (select
         c.classid,c.coursestitle,o.orgid,o.orgtitle,x.price,x.cheaper,x.isvouch,x.classsettime
     from 
         class c,org o,courses x
     where
         c.orgid=o.orgid 
         and 
         c.coursesid=x.coursesid
         and
         c.classsettime>getdate() 
         and 
         x.isvouch=1
         and
         not exists(select 1 from class where orgid=c.orgid and classsettime>t.classsettime)) t
order by
    classsettime asc 

[ai_li7758521]

TRY:

select top 10  c.classid,x.coursestitle,c.orgid,o.orgtitle,x.price,x.cheaper,x.isvouch,c.classsettime 
from class c,org o,courses x 
where c.orgid=o.orgid and c.coursesid=x.coursesid 
and c.classsettime>getdate() and x.isvouch=1 --添加部分
order by c.classsettime asc 

[ai_li7758521]

表间的关系不清楚，不好搞..

[Zoezs]

select
    top 10 *
from
    (select
         c.classid,c.coursestitle,o.orgid,o.orgtitle,x.price,x.cheaper,x.isvouch,x.classsettime
     from 
         class c,org o,courses x
     where
         c.orgid=o.orgid 
         and 
         c.coursesid=x.coursesid
         and
         c.classsettime>getdate() 
         and 
         x.isvouch=1
         and
         not exists(select 1 from class where orgid=c.orgid and classsettime>t.classsettime)) t
order by
    classsettime asc 

钻钻的编码风格灰常的好啊。

[Teng_s2000]

钻钻最近很活跃!!

[jsksmark]

class表
classid orgid coursesid classsettime categoryid
1 1 1 2009-05-01 1
2 1 2 2009-06-01 2
3 2 4 2009-07-01 1
4 2 5 2009-08-01 1
5 3 3 2009-09-01 1
6 4 6 2009-10-01 3
7 1 2 2009-01-01 2
8 1 2 2009-06-02 2

courses表
coursesid orgid isvouch price cheaper coursestitle
1 1 0 1000 900 英语
2 1 1 1000 900 体育
3 3 0 1000 900 音乐
4 2 1 1000 900 德治
5 2 1 1000 900 劳动
6 4 1 1000 900 化学


org表
orgid orgtitle
1 大学
2 小学
3 中学
4 预科
5 中专

现在以class表为主线，调出前十条数据

条件1.大于当前时间的 classsettime>getdate(),则按今天是 2009-04-01算，则得到如下数据

classid orgid coursesid classsettime categoryid
1 1 1 2009-05-01 1
2 1 2 2009-06-01 2
3 2 4 2009-07-01 1
4 2 5 2009-08-01 1
5 3 3 2009-09-01 1
6 4 6 2009-10-01 3
8 1 2 2009-06-02 2

条件2.categoryid=1或者 categoryid=2
classid orgid coursesid classsettime categoryid
1 1 1 2009-05-01 1
2 1 2 2009-06-01 2
3 2 4 2009-07-01 1
4 2 5 2009-08-01 1
5 3 3 2009-09-01 1
8 1 2 2009-06-02 2
条件3.isvouch=1，则
classid orgid coursesid classsettime categoryid
2 1 2 2009-06-01 2
3 2 4 2009-07-01 1
4 2 5 2009-08-01 1
8 1 2 2009-06-02 2
条件4.相同orgid的只显示一条，并且如果数据相同，则选择classsettime离当前时间最近的，则
classid orgid coursesid classsettime categoryid
2 1 2 2009-06-01 2
3 2 4 2009-07-01 1

一共列出10条，请问这个该怎么做呢？

[SmileShark]

select top 10 classid,coursestitle,distinct orgid

加个关键字 distinct 不重复的就可以了

[jsksmark]

这样出错了~

[子陌红尘]

declare @class table(classid int,orgid int,coursesid int,classsettime datetime,categoryid int)
insert into @class select 1,1,1,'2009-05-01',1 
insert into @class select 2,1,2,'2009-06-01',2 
insert into @class select 3,2,4,'2009-07-01',1 
insert into @class select 4,2,5,'2009-08-01',1 
insert into @class select 5,3,3,'2009-09-01',1 
insert into @class select 6,4,6,'2009-10-01',3 
insert into @class select 7,1,2,'2009-01-01',2 
insert into @class select 8,1,2,'2009-06-02',2 

declare @courses table(coursesid int,orgid int,isvouch int,price int,cheaper int,coursestitle varchar(8))
insert into @courses select 1,1,0,1000,900,'英语' 
insert into @courses select 2,1,1,1000,900,'体育' 
insert into @courses select 3,3,0,1000,900,'音乐' 
insert into @courses select 4,2,1,1000,900,'德治' 
insert into @courses select 5,2,1,1000,900,'劳动' 
insert into @courses select 6,4,1,1000,900,'化学' 


declare @org table(orgid int,orgtitle varchar(8))
insert into @org select 1,'大学'
insert into @org select 2,'小学'
insert into @org select 3,'中学'
insert into @org select 4,'预科'
insert into @org select 5,'中专'

select
    top 10 *
from
    (select
         c.classid,x.coursestitle,o.orgid,o.orgtitle,x.price,x.cheaper,x.isvouch,c.classsettime
     from 
         @class c,@org o,@courses x
     where
         c.orgid=o.orgid 
         and
         c.categoryid in(1,2)
         and 
         c.coursesid=x.coursesid
         and
         c.classsettime>getdate() 
         and 
         x.isvouch=1
         and
         not exists(select 1 from @class where orgid=c.orgid and classsettime>c.classsettime)) t
order by
    classsettime asc 

/*
classid     coursestitle orgid       orgtitle price       cheaper     isvouch     classsettime                                           
----------- ------------ ----------- -------- ----------- ----------- ----------- ------------------------------------------------------ 
8           体育           1           大学       1000        900         1           2009-06-02 00:00:00.000
4           劳动           2           小学       1000        900         1           2009-08-01 00:00:00.000
*/

[jsksmark]

怎么还是读不出数据出来？

[肥龙上天]

declare @class table(classid int,orgid int,coursesid int,classsettime datetime,categoryid int)
insert into @class select 1,1,1,'2009-05-01',1 
insert into @class select 2,1,2,'2009-06-01',2 
insert into @class select 3,2,4,'2009-07-01',1 
insert into @class select 4,2,5,'2009-08-01',1 
insert into @class select 5,3,3,'2009-09-01',1 
insert into @class select 6,4,6,'2009-10-01',3 
insert into @class select 7,1,2,'2009-01-01',2 
insert into @class select 8,1,2,'2009-06-02',2 

declare @courses table(coursesid int,orgid int,isvouch int,price int,cheaper int,coursestitle varchar(8))
insert into @courses select 1,1,0,1000,900,'英语' 
insert into @courses select 2,1,1,1000,900,'体育' 
insert into @courses select 3,3,0,1000,900,'音乐' 
insert into @courses select 4,2,1,1000,900,'德治' 
insert into @courses select 5,2,1,1000,900,'劳动' 
insert into @courses select 6,4,1,1000,900,'化学' 


declare @org table(orgid int,orgtitle varchar(8))
insert into @org select 1,'大学'
insert into @org select 2,'小学'
insert into @org select 3,'中学'
insert into @org select 4,'预科'
insert into @org select 5,'中专'


select classid ,c.orgid,a.coursesid,classsettime,categoryid  from @class a join @courses b on a.coursesid = b.coursesid join @org c on b.orgid = c.orgid
where classsettime>getdate() and categoryid in (1,2) and b.isvouch=1 
and not exists(select 1 from @class where orgid = c.orgid and classsettime >a.classsettime )

/*
classid     orgid       coursesid   classsettime            categoryid
----------- ----------- ----------- ----------------------- -----------
8           1           2           2009-06-02 00:00:00.000 2
4           2           5           2009-08-01 00:00:00.000 1

(2 row(s) affected)
*/