create table table1
(
id serial primary key,
class_id integer,
book_id integer,
show_begintime date,
show_endtime date
)表裡會有缺省數據如下(class_id為空):insert into table1(book_id,show_begintime,show_endtime)values(1,'2009-01-01','2009-12-31')
insert into table1(book_id,show_begintime,show_endtime)values(2,'2009-01-01','2009-12-31')
insert into table1(book_id,show_begintime,show_endtime)values(3,'2009-01-01','2009-12-31')
insert into table1(book_id,show_begintime,show_endtime)values(4,'2009-01-01','2009-12-31')
insert into table1(book_id,show_begintime,show_endtime)values(5,'2009-01-01','2009-12-31')
用戶可能會對書本進行個別班級重新排列時間,class_id為班級的ID例如對class=1的班級插入了如下數據
insert into table1(class_id,book_id,show_begintime,show_endtime)values(1,1,'2009-09-01','2009-09-20')我想對級班1查詢這10本書的時間,如果數據中有符合條件的class_id,那麼時間就取這個班級某本書的時間,反之就取缺省數據的時間。請問SQL語句如何寫,而且要求效率高。
(
id serial primary key,
class_id integer,
book_id integer,
show_begintime date,
show_endtime date
)表裡會有缺省數據如下(class_id為空):insert into table1(book_id,show_begintime,show_endtime)values(1,'2009-01-01','2009-12-31')
insert into table1(book_id,show_begintime,show_endtime)values(2,'2009-01-01','2009-12-31')
insert into table1(book_id,show_begintime,show_endtime)values(3,'2009-01-01','2009-12-31')
insert into table1(book_id,show_begintime,show_endtime)values(4,'2009-01-01','2009-12-31')
insert into table1(book_id,show_begintime,show_endtime)values(5,'2009-01-01','2009-12-31')
用戶可能會對書本進行個別班級重新排列時間,class_id為班級的ID例如對class=1的班級插入了如下數據
insert into table1(class_id,book_id,show_begintime,show_endtime)values(1,1,'2009-09-01','2009-09-20')我想對級班1查詢這10本書的時間,如果數據中有符合條件的class_id,那麼時間就取這個班級某本書的時間,反之就取缺省數據的時間。請問SQL語句如何寫,而且要求效率高。
from table1 t
where id =(select id from table1 where book_id=t.book_id order by class_id desc limit 1)
select *
from table1 t
where id =(select id from table1 where book_id=t.book_id and (class_id=1 or class_id is null) order by class_id desc limit 1)
and (class_id=1 or class_id is null)
1 2009-09-01 2009-09-20
2 2009-01-01 2009-12-31
3 2009-01-01 2009-12-31
4 2009-01-01 2009-12-31
5 2009-01-01 2009-12-31
from table1 t
where id =(select id from table1 where book_id=t.book_id and (class_id=1 or class_id is null) order by class_id desc limit 1)
and (class_id=1 or class_id is null)/**
id class_id book_id show_begintime show_endtime
------ -------- ------- -------------- ------------
2 (NULL) 2 2009-01-01 2009-12-31
3 (NULL) 3 2009-01-01 2009-12-31
4 (NULL) 4 2009-01-01 2009-12-31
5 (NULL) 5 2009-01-01 2009-12-31
6 1 1 2009-09-01 2009-09-20
**/
这是我这里测试的结果,貌似没有问题
from table1 t
where id =(select id from table1 where book_id=t.book_id and (class_id=1 or class_id is null) order by class_id desc limit 1)
and (class_id=1 or class_id is null)
order by book_id/**
book_id show_begintime show_endtime
------- -------------- ------------
1 2009-09-01 2009-09-20
2 2009-01-01 2009-12-31
3 2009-01-01 2009-12-31
4 2009-01-01 2009-12-31
5 2009-01-01 2009-12-31
**/
直接執行insert into table1(book_id,show_begintime,show_endtime)values(1,'2009-01-01','2009-12-31') 這個class_id裡的值應該就是null吧?
order by class_id desc 改为order by ifnull(class_id,0) desc 试试