有几个表,zl_class ,zl_org zl_courses,zxx_coursesclasscategory通过下面的语句读出所有的数据select
c.classid,x.coursestitle,o.orgid,o.orgshorttitle,x.coursescharge,x.coursesagio,x.issitevouch,c.classsettime
from
zl_class c,zl_org o,zl_courses x,zxx_coursesclasscategory m
where
c.orgid=o.orgid
and
m.categoryid in(19)
and
c.coursesid=x.coursesid
and
c.classsettime>getdate()
and
x.issitevouch=1
and
c.coursesclassid=m.coursesclassid
order by c.classsettime asc---------------------------
数据显示效果如下classid coursestitle orgid orgshorttitle coursescharge coursesagio issitevouch classsettime
2112 A小够 19 某一 10000 2000 1 2009-04-092332 B小够 19 某一 10000 2000 1 2009-04-092565 A小够 19 某一 10000 2000 1 2009-04-092198 C小够 19 某一 10000 2000 1 2009-04-112962 C小够 19 某一 10000 2000 1 2009-04-112677 A小够 19 某一 10000 2000 1 2009-04-122953 M小够 20 某无 10000 2000 1 2009-04-122760 W小够 37 某四 10000 2000 1 2009-04-122984 M小够 20 某无 10000 2000 1 2009-04-122843 W小够 37 某四 10000 2000 1 2009-04-122473 C小够 19 某一 10000 2000 1 2009-04-15----------------------------现在想读出的效果是,相同orgid的只显示一条,按这个需求,那么只能显示三条,某一,某四,某无各一条;其中同一个orgid的显示classid最小的的一条,这样得到的结果是classid coursestitle orgid orgshorttitle coursescharge coursesagio issitevouch classsettime
2112 A小够 19 某一 10000 2000 1 2009-04-09
2953 M小够 20 某无 10000 2000 1 2009-04-12
2760 W小够 37 某四 10000 2000 1 2009-04-12请问在我的SQL语句上,该怎么修改呢?谢谢
c.classid,x.coursestitle,o.orgid,o.orgshorttitle,x.coursescharge,x.coursesagio,x.issitevouch,c.classsettime
from
zl_class c,zl_org o,zl_courses x,zxx_coursesclasscategory m
where
c.orgid=o.orgid
and
m.categoryid in(19)
and
c.coursesid=x.coursesid
and
c.classsettime>getdate()
and
x.issitevouch=1
and
c.coursesclassid=m.coursesclassid
order by c.classsettime asc---------------------------
数据显示效果如下classid coursestitle orgid orgshorttitle coursescharge coursesagio issitevouch classsettime
2112 A小够 19 某一 10000 2000 1 2009-04-092332 B小够 19 某一 10000 2000 1 2009-04-092565 A小够 19 某一 10000 2000 1 2009-04-092198 C小够 19 某一 10000 2000 1 2009-04-112962 C小够 19 某一 10000 2000 1 2009-04-112677 A小够 19 某一 10000 2000 1 2009-04-122953 M小够 20 某无 10000 2000 1 2009-04-122760 W小够 37 某四 10000 2000 1 2009-04-122984 M小够 20 某无 10000 2000 1 2009-04-122843 W小够 37 某四 10000 2000 1 2009-04-122473 C小够 19 某一 10000 2000 1 2009-04-15----------------------------现在想读出的效果是,相同orgid的只显示一条,按这个需求,那么只能显示三条,某一,某四,某无各一条;其中同一个orgid的显示classid最小的的一条,这样得到的结果是classid coursestitle orgid orgshorttitle coursescharge coursesagio issitevouch classsettime
2112 A小够 19 某一 10000 2000 1 2009-04-09
2953 M小够 20 某无 10000 2000 1 2009-04-12
2760 W小够 37 某四 10000 2000 1 2009-04-12请问在我的SQL语句上,该怎么修改呢?谢谢
select
c.classid,x.coursestitle,o.orgid,o.orgshorttitle,x.coursescharge,x.coursesagio,x.issitevouch,c.classsettime
from
zl_class c,zl_org o,zl_courses x,zxx_coursesclasscategory m
where
c.orgid=o.orgid
and
m.categoryid in(19)
and
c.coursesid=x.coursesid
and
c.classsettime>getdate()
and
x.issitevouch=1
and
c.coursesclassid=m.coursesclassid
and not exists(select 1 from zl_class m,zl_org n where m.orgid=n.orgid and m.classsettime>getdate() and orgid=o.orgid and classid<c.classid)
order by c.classsettime asc
c.classid,x.coursestitle,o.orgid,o.orgshorttitle,x.coursescharge,x.coursesagio,x.issitevouch,c.classsettime
into #t
from
zl_class c,zl_org o,zl_courses x,zxx_coursesclasscategory m
where
c.orgid=o.orgid
and
m.categoryid in(19)
and
c.coursesid=x.coursesid
and
c.classsettime>getdate()
and
x.issitevouch=1
and
c.coursesclassid=m.coursesclassid
order by c.classsettime asc select
*
from
#t t
where
not exists(select 1 from #t where orgid=t.orgid and classid<t.classid)
/**
classid coursestitle orgid orgshorttitle coursescharge coursesagio issitevouch classsettime
----------- ------------ ----------- ------------- ------------- ----------- ----------- ------------------------------------------------------
2112 A小够 19 某一 10000 2000 1 2009-04-09 00:00:00.000
2953 M小够 20 某无 10000 2000 1 2009-04-12 00:00:00.000
2760 W小够 37 某四 10000 2000 1 2009-04-12 00:00:00.000(所影响的行数为 3 行)
**/
select c.classid,x.coursestitle,o.orgid,o.orgshorttitle,x.coursescharge,x.coursesagio,x.issitevouch,c.classsettime
from zl_class c,zl_org o,zl_courses x,zxx_coursesclasscategory m
,(select min(classid) from zl_class group by coursesid) d
where c.classid=d.classid
and c.orgid=o.orgid
and m.categoryid in(19)
and c.coursesid=x.coursesid
and c.classsettime>getdate()
and x.issitevouch=1
and c.coursesclassid=m.coursesclassid
order by c.classsettime asc
select
c.classid,x.coursestitle,o.orgid,o.orgshorttitle,x.coursescharge,x.coursesagio,x.issitevouch,c.classsettime
from
zl_class c,zl_org o,zl_courses x,zxx_coursesclasscategory m
where
c.orgid=o.orgid
and
m.categoryid in(19)
and
c.coursesid=x.coursesid
and
c.classsettime>getdate()
and
x.issitevouch=1
and
c.coursesclassid=m.coursesclassid
and not exists(select 1 from zl_class t1,zl_org t2 where t1.orgid=t2.orgid and t1.classsettime>getdate() and t1.orgid=o.orgid and t1.classid<c.classid)
order by c.classsettime asc