先排序再left jion

Question

有一个表字段为   id，name，state，datetime
state有2个值  0和1，分别表示出和入
现在要做成这样一个表id    name   indatetime    outdatetime
1      a         8:10          9:39
1      a         9:45          10:50
2      b         8:00          10:10
3      c         8:23          9:30
3      c         9:50          12:20
4      d         8:00           未出
5      e         8:30          10:30
SELECT ID,NAME,TABLE0.DATETIME INDATETIME , TABLE1.DATETIME OUTDATETIME FROM 
(SELECT *   FROM   MY_TBALE   WHERE  MY_TBALE.STATE = '0') TABLE0 
LEFT JOIN
(SELECT *   FROM   MY_TBALE   WHERE  MY_TBALE.STATE = '1')  TABLE1
ON
TABLE0.ID = TABLE1.ID
AND 
TABLE0.DATETIME <> TABLE1.DATETIME这里有个问题，因为MY_TBALE 这个表里不是按照datetime排序的，数据比较乱，这样left jion 就会出现问题要在排序之后再left jion ，，但是这样会报sql错，大家帮我看看怎么写才好？

Answer 1

我建议你这么做,(不过,小时是单个的需要补上0)
select m.id , m.name , m.DATETIME indatetime, isnull(n.DATETIME,'未出') outdatetime from
(select t.* , px = (select count(1) from MY_TBALE WHERE MY_TBALE.STATE = '0' and id = t.id and DATETIME < t.DATETIME) + 1 from MY_TBALE t) m
left join
(select t.* , px = (select count(1) from MY_TBALE WHERE MY_TBALE.STATE = '1' and id = t.id and DATETIME < t.DATETIME) + 1 from MY_TBALE t) n
on m.id = n.id 

Answer 2

我建议你这么做,(不过,小时是单个的需要补上0)select m.id , m.name , m.DATETIME indatetime, isnull(n.DATETIME,'未出') outdatetime from
(select t.* , px = (select count(1) from MY_TBALE WHERE MY_TBALE.STATE = '0' and id = t.id and DATETIME < t.DATETIME) + 1 from MY_TBALE t) m
left join
(select t.* , px = (select count(1) from MY_TBALE WHERE MY_TBALE.STATE = '1' and id = t.id and DATETIME < t.DATETIME) + 1 from MY_TBALE t) n
on m.id = n.id and m.px = n.px

Answer 3

没有原始数据猜一个
SELECT TABLE0.ID,TABLE0.NAME,TABLE0.DATETIME INDATETIME , min(TABLE1.DATETIME) OUTDATETIME 
FROM  MY_TBALE TABLE0 LEFT JOIN MY_TBALE TABLE1
ON TABLE0.STATE = '0' and TABLE1.STATE = '1' and TABLE0.ID = TABLE1.ID AND TABLE0.DATETIME < TABLE1.DATETIME
group by TABLE0.ID,TABLE0.NAME,TABLE0.DATETIME

Answer 4

--sql 2000用子查询
create table tb(id int, name varchar(10) ,datetime varchar(5),STATE varchar(10))
insert into tb values(1 , 'a', '08:10','0')
insert into tb values(1 , 'a', '09:39','1')
insert into tb values(1 , 'a', '09:45','0')
insert into tb values(1 , 'a', '10:50','1')
insert into tb values(2 , 'b', '08:00','0')
insert into tb values(2 , 'b', '10:10','1')
insert into tb values(3 , 'c', '08:23','0')
insert into tb values(3 , 'c', '09:30','1')
insert into tb values(3 , 'c', '09:50','0')
insert into tb values(3 , 'c', '12:20','1')
insert into tb values(4 , 'd', '08:00','0')
insert into tb values(5 , 'e', '08:30','0')
insert into tb values(5 , 'e', '10:30','1')
goselect m.id , m.name , m.DATETIME indatetime, isnull(n.DATETIME,'未出') outdatetime from
(select t.* , px = (select count(1) from tb WHERE STATE = '0' and id = t.id and DATETIME < t.DATETIME) + 1 from tb t where STATE = '0') m 
left join
(select t.* , px = (select count(1) from tb WHERE STATE = '1' and id = t.id and DATETIME < t.DATETIME) + 1 from tb t where STATE = '1') n 
on m.id = n.id and m.px = n.px
order by m.id , indatetimedrop table tb/*
id          name       indatetime outdatetime 
----------- ---------- ---------- ----------- 
1           a          08:10      09:39
1           a          09:45      10:50
2           b          08:00      10:10
3           c          08:23      09:30
3           c          09:50      12:20
4           d          08:00      未出
5           e          08:30      10:30（所影响的行数为 7 行）*/--sql 2005用row_numbercreate table tb(id int, name varchar(10) ,datetime nvarchar(5),STATE varchar(10))
insert into tb values(1 , 'a', '08:10','0')
insert into tb values(1 , 'a', '09:39','1')
insert into tb values(1 , 'a', '09:45','0')
insert into tb values(1 , 'a', '10:50','1')
insert into tb values(2 , 'b', '08:00','0')
insert into tb values(2 , 'b', '10:10','1')
insert into tb values(3 , 'c', '08:23','0')
insert into tb values(3 , 'c', '09:30','1')
insert into tb values(3 , 'c', '09:50','0')
insert into tb values(3 , 'c', '12:20','1')
insert into tb values(4 , 'd', '08:00','0')
insert into tb values(5 , 'e', '08:30','0')
insert into tb values(5 , 'e', '10:30','1')
goselect m.id , m.name , m.DATETIME indatetime, isnull(n.DATETIME,N'未出') outdatetime from
(select t.* , px = row_number() over(partition by id order by datetime) from tb t where STATE = '0') m 
left join
(select t.* , px = row_number() over(partition by id order by datetime) from tb t where STATE = '1') n 
on m.id = n.id and m.px = n.px
order by m.id , indatetimedrop table tb/*
id          name       indatetime outdatetime
----------- ---------- ---------- -----------
1           a          08:10      09:39
1           a          09:45      10:50
2           b          08:00      10:10
3           c          08:23      09:30
3           c          09:50      12:20
4           d          08:00      未出
5           e          08:30      10:30(7 行受影响)*/

Answer 5

从字符串向 datetime 转换时失败。 怎么回事啊？
有点看不懂你的代码，我是新手，，，郁闷啊