select id,createdate,yid from table1 where exists(select * from table2 where type<>5 and yid=table1.yid) order by id desc
union
select id,createdate,yid from table1 where exists(select * from table2 where type=5 and yid=table1.yid) order by id desc
union
select id,createdate,yid from table1 where exists(select * from table2 where type=5 and yid=table1.yid) order by id desc
但是这样所有日期的记录只要是type不等于5的都会排在前面了,我的要求是同一天中,这样排列。如果不是同一天,按日期排列
where not exists(select 1 from table2 where yid=a.yid and type=5)
union all
select * from
(select * from table1 a
where not exists(select 1 from table2 where yid=a.yid and type=5)
order by id desc) a
(
select id,createdate,yid,case (select type from table2 where yid=table1.yid) when 5 then 1 else 0 end as type from table1
) a
order by createdate,type,id
order by (select case type when 5 then 1 else 0 end from table2 where yid=a.yid),id
@prodId int
as
select prodName,prodId,bomNum
from prod
where prodId = @prodId
drop table table1
if exists(select Name form sysobjects where name='Table2')
drop table table2create table table1(
[id] varchar(10),
Createdate datetime,
Yid varchar(10)
)
Create Table table2(
Yid varchar(10),
type int)insert into table1
select '494833','2005-1-7 0:46:00','10'
union all
select '494825','2005-1-7 0:28:00','20'
union all
select '494822','2005-1-7 0:23:00','10'
union all
Select '494811','2005-1-7 0:10:00','20'
union all
Select '494809','2005-1-7 0:05:00','20'
union all
select '494791','2005-1-6 23:34:00','30'
union all
select '494018','2005-1-6 15:32:00','10'
union all
select '494016','2005-1-6 15:32:00','30'
union all
select '494015','2005-1-6 15:31:00','10'insert into table2
select '10',5
union all
select '20',25
union all
select '30',5select C.id,C.createdate,C.yid from
(select a.id,a.createdate,dateadd(dd,datediff(dd,0,a.createdate),0) Orderdate,
a.Yid,case b.type when 5 then 2 else 1 end OrderID
from table1 a,table2 b where a.yid=b.yid) c
order by c.orderdate desc ,c.orderid
drop table table1
if exists(select Name from sysobjects where name='Table2')
drop table table2
createcreate table table1(
[id] varchar(10),
Createdate datetime,
Yid varchar(10)
)
Create Table table2(
Yid varchar(10),
type int)insert into table1
select '494833','2005-1-7 0:46:00','10'
union all
select '494825','2005-1-7 0:28:00','20'
union all
select '494822','2005-1-7 0:23:00','10'
union all
Select '494811','2005-1-7 0:10:00','20'
union all
Select '494809','2005-1-7 0:05:00','20'
union all
select '494791','2005-1-6 23:34:00','30'
union all
select '494018','2005-1-6 15:32:00','10'
union all
select '494016','2005-1-6 15:32:00','30'
union all
select '494015','2005-1-6 15:31:00','10'insert into table2
select '10',5
union all
select '20',25
union all
select '30',5
select a.* from table1 a left join table2 b on a.Yid=b.Yid
order by b.type desc--------------------------------------------
494811 2005-01-07 00:10:00.000 20
494809 2005-01-07 00:05:00.000 20
494825 2005-01-07 00:28:00.000 20
494822 2005-01-07 00:23:00.000 10
494833 2005-01-07 00:46:00.000 10
494791 2005-01-06 23:34:00.000 30
494018 2005-01-06 15:32:00.000 10
494016 2005-01-06 15:32:00.000 30
494015 2005-01-06 15:31:00.000 10(所影响的行数为 9 行)
我想case …… when …… then …… else…… 这个应该可以用,就是不知道用起来效率如何。我的table1中有超过30万条数据,会不会很慢呢?
~~~~~~~~~~~~~~~~~~~也就是说,我要先按照日期排列,找出每一天的id,然后将这些在同一天内的ID,按照类型是否等于5排列。
我刚才看得不够仔细,我想和尚最后列出的sql语句可能是按照这个要求写的,谢谢。不过我要周一才能证实是否正确,敬请等待。谢谢。