create table a
(
date datetime,
shift varchar(20),
qty float
)insert into a values('2011-01-08','中班',50)
insert into a values('2011-01-08','白班',60)
insert into a values('2011-01-08','晚班',30)
insert into a values('2011-01-09','中班',80)
insert into a values('2011-01-09','白班',50)
insert into a values('2011-01-09','晚班',60)依据date,shift排序(白班,中班,晚班)
例如
输入 '2011-01-08','白班' 结果 ‘2011-01-08’ ‘中班’ 50
输入 ‘2011-01-08’ ‘晚班’ 结果 '2011-01-09' '白班' 50
select *
from a
order by [date],(case shift when '白班' then 0 when '中班' then 1 else 2 end)
select @date = '2011-01-08', @shift = '白班'set @date = case @shift when '晚班' then @date + 1 else @date end
set @shift = case @shift when '白班' then '中班' when '中班' then '晚班' else '白班' end
select top 1 * from a where date >= @date and shift = @shift order by date
with t as
(
select ROW_NUMBER() OVER(order by date,case shift when '白班' then 1 when '中班' then 2 when '晚班' then 3 end) [id],*from a
)
--select * from t
select * from t s where exists(select 1 from t b where s.date=b.date and s.id=(b.id+1)and b.date='2011-01-08' and b.shift='白班')
(
date datetime,
shift varchar(20),
qty float
)insert into a values('2011-01-08','中班',50)
insert into a values('2011-01-08','白班',60)
insert into a values('2011-01-08','晚班',30)
insert into a values('2011-01-09','中班',80)
insert into a values('2011-01-09','晚班',60)
依据date,shift排序(白班,中班,晚班) ID号是乱的,不能依据ID
例如
输入 '2011-01-08','白班' 结果 ‘2011-01-08’ ‘中班’ 50
输入 ‘2011-01-08’ ‘晚班’ 结果 '2011-01-09' '中班' 80
create table a
(
date datetime,
shift varchar(20),
qty float
)insert into a values('2011-01-08','中班',50)
insert into a values('2011-01-08','白班',60)
insert into a values('2011-01-08','晚班',30)
insert into a values('2011-01-09','中班',80)
insert into a values('2011-01-09','晚班',60)
godeclare @time varchar(10)
declare @fl_a varchar(10)
declare @fl_b varchar(10)
set @time = '2011-01-08'
set @fl_a = '白班'
set @fl_b = '晚班';with cte as
(
select *,row_number() over (order by date,(case shift when '白班' then 0 when '中班' then 1 else 2 end))rn
from a
)/*
select date,shift,qty
from cte t
where rn = (select rn + 1 from cte where convert(varchar(10),date,120) = @time and shift = @fl_a)
*/select date,shift,qty
from cte t
where rn = (select rn + 1 from cte where convert(varchar(10),date,120) = @time and shift = @fl_b)drop table a
date shift qty
----------------------- -------------------- ----------------------
2011-01-08 00:00:00.000 中班 50(1 行受影响)date shift qty
----------------------- -------------------- ----------------------
2011-01-09 00:00:00.000 中班 80(1 行受影响)
可能是记录多了就出现如下状况:
消息 512,级别 16,状态 1,第 1 行
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
你确定你的表结构是你给的那样么?是不是还有人员什么的,如果有都要加到where子句里边。
create table a
(
people varchar(10),
date datetime,
shift varchar(20),
qty float
)insert into a values('a','2011-01-08','中班',50)
insert into a values('a','2011-01-08','白班',60)
insert into a values('a','2011-01-08','晚班',30)
insert into a values('a','2011-01-09','中班',80)
insert into a values('a','2011-01-09','晚班',60)
insert into a values('b','2011-01-08','中班',50)
insert into a values('b','2011-01-08','白班',60)
insert into a values('b','2011-01-08','晚班',30)
insert into a values('b','2011-01-09','中班',80)
insert into a values('b','2011-01-09','晚班',60)
godeclare @time varchar(10)
declare @fl_a varchar(10)
declare @fl_b varchar(10)
set @time = '2011-01-08'
set @fl_a = '白班'
set @fl_b = '晚班';with cte as
(
select *,row_number() over (partition by people order by date,(case shift when '白班' then 0 when '中班' then 1 else 2 end))rn
from a
)select date,shift,qty
from cte t
where rn = (select rn + 1 from cte where convert(varchar(10),date,120) = @time and shift = @fl_a and people = 'b')
and people = 'b'/*
select date,shift,qty
from cte t
where rn = (select rn + 1 from cte where convert(varchar(10),date,120) = @time and shift = @fl_b and people = 'a')
and people = 'a'
*/drop table a
people date shift qty
---------- ----------------------- -------------------- ----------------------
b 2011-01-08 00:00:00.000 中班 50(1 行受影响)
people date shift qty
---------- ----------------------- -------------------- ----------------------
a 2011-01-09 00:00:00.000 中班 80(1 行受影响)
from cte t
where rn = (select max(rn) + 1 from cte where convert(varchar(10),date,120) = @time and shift = @fl_a )
最后用的这个,谢谢。
(
select ROW_NUMBER() OVER(order by date,case shift when '白班' then 1 when '中班' then 2 when '晚班' then 3 end) [id],*from a
)
select * from t s where exists(select 1 from t b where s.id=(b.id+1)and b.date='2011-01-08' and b.shift='白班')
--select * from t s where exists(select 1 from t b where s.id=(b.id+1)and b.date='2011-01-08' and b.shift='中班')
--select * from t s where exists(select 1 from t b where s.id=(b.id+1)and b.date='2011-01-08' and b.shift='晚班')
id date shift qty
-------------------- ----------------------- -------------------- ----------------------
2 2011-01-08 00:00:00.000 中班 50(1 行受影响)id date shift qty
-------------------- ----------------------- -------------------- ----------------------
3 2011-01-08 00:00:00.000 晚班 30(1 行受影响)id date shift qty
-------------------- ----------------------- -------------------- ----------------------
4 2011-01-09 00:00:00.000 白班 50(1 行受影响)