class表classid orgid coursesid classsettimecourses表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语句该怎么完善呢?
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语句该怎么完善呢?
解决方案 »
- 如何获取当天8点至昨天8点的数据
- sqlserver l两个表 分类id 每个分类取2条记录。
- 为什么只有这两个字显示****?
- 求一条SQL语句,在线等待。请大家帮忙!
- SQL2005为什么安装后在“事件察看-->应用程序”那里会不断产生.NET Runtime Optimization Service的时间的?
- 求助:两个时间:比如9:00到12:30减出来得到3.3小时;
- 请问:在SQLserver中怎么将binaryl类型的数据转换成char型的?
- 数据库sql 65,主键莫名其妙重复出错,请高手指点!!
- 求一SQL语句的写法,.....................................
- 青对RADIUS有了解的大侠看过来!
- |zyciis| 如何知道我的记录在指定分页中的第几页,谢谢
- 前面发错了,再发一贴!这样的情况怎么写sql语句?
class表classid orgid coursesid classsettimecourses表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
-->是不是连续的两条数据orgid不能相同?最好给出数据。
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
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
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 钻钻的编码风格灰常的好啊。
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 2courses表
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条,请问这个该怎么做呢?
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
*/
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)
*/