22,300
社区成员




CREATE TABLE PRE0(
NUMBER VARCHAR(100),
IP VARCHAR(20),
[TIME] DATETIME,
Style VARCHAR(10)
)
INSERT INTO PRE0
SELECT '20071632','127.0.0.1','2009-7-12 21:53:44','簽到' UNION ALL
SELECT '20071632','127.0.0.1','2009-7-12 21:53:44','簽到' UNION ALL
SELECT '20071632','127.0.0.1','2009-7-14 15:01:17','簽到' UNION ALL
SELECT '2076011','127.0.0.1','2009-7-12 19:51:57','簽到' UNION ALL
SELECT '2076011','127.0.0.1','2009-7-12 19:52:23','簽到' UNION ALL
SELECT '2076011','127.0.0.1','2009-7-12 19:52:57','簽到' UNION ALL
SELECT '2076011','127.0.0.1','2009-7-12 19:53:22','簽到' UNION ALL
SELECT '20051936','127.0.0.1','2009-7-12 19:54:44','簽到'
CREATE TABLE PRE1(
NUMBER VARCHAR(100),
IP VARCHAR(20),
[TIME] DATETIME,
Style VARCHAR(10)
)
INSERT INTO PRE1
SELECT '20071632','127.0.0.1','2009-7-12 22:53:44','簽退' UNION ALL
SELECT '20071632','127.0.0.1','2009-7-12 22:53:44','簽退' UNION ALL
SELECT '20071632','127.0.0.1','2009-7-14 18:01:17','簽退' UNION ALL
SELECT '2076011','127.0.0.1','2009-7-12 22:51:57','簽退' UNION ALL
SELECT '2076011','127.0.0.1','2009-7-12 23:52:23','簽退' UNION ALL
SELECT '2076011','127.0.0.1','2009-7-12 20:52:57','簽退' UNION ALL
SELECT '2076011','127.0.0.1','2009-7-12 21:53:22','簽退' UNION ALL
SELECT '20051936','127.0.0.1','2009-7-12 20:54:44','簽退'
CREATE TABLE Member(
NUMBER VARCHAR(100),
[NAME] VARCHAR(100)
)
INSERT INTO Member
SELECT '20071632','A' UNION ALL
SELECT '2076011','B' UNION ALL
SELECT '20051936','C'
select Pre0.Number,Member.Name,Pre0.IP,Pre0.Time,min(Pre1.Time)
from Pre0 left join Pre1 on Pre0.Number=Pre1.Number and Pre1.Time>=Pre0.Time and Pre1.Style='簽退' inner join Member
ON Pre0.Number=Member.Number
where Pre0.Style='簽到' group by Pre0.Number,Member.Name,Pre0.IP,Pre0.Time
Number Name IP Time
---------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------- -------------------- ----------------------- -----------------------
20051936 C 127.0.0.1 2009-07-12 19:54:44.000 2009-07-12 20:54:44.000
20071632 A 127.0.0.1 2009-07-12 21:53:44.000 2009-07-12 22:53:44.000
20071632 A 127.0.0.1 2009-07-14 15:01:17.000 2009-07-14 18:01:17.000
2076011 B 127.0.0.1 2009-07-12 19:51:57.000 2009-07-12 20:52:57.000
2076011 B 127.0.0.1 2009-07-12 19:52:23.000 2009-07-12 20:52:57.000
2076011 B 127.0.0.1 2009-07-12 19:52:57.000 2009-07-12 20:52:57.000
2076011 B 127.0.0.1 2009-07-12 19:53:22.000 2009-07-12 20:52:57.000
(7 行受影响)
select Pre0.Number,Member.Name,Pre0.IP,Pre0.Time,min(Pre1.Time)
from Pre0 left join Pre1 on Pre0.Number=Pre1.Number and Pre1.Time>=Pre0.Time inner join Member ON Pre0.Number=Member.Number
group by Pre0.Number,Member.Name,Pre0.IP,Pre0.Time
select Pre0.Number,Member.Name,Pre0.IP,Pre0.Time,min(Pre1.Time)
from Pre0 left join Pre1 on Pre0.Number=Pre1.Number and Pre1.Time>=Pre0.Time inner join Member ON Pre0.Number=Member.Number
where Pre0.Style='签到' and Pre1.Style='签退'
group by Pre0.Number,Member.Name,Pre0.IP,Pre0.Time
试试看 你的Member表哪来的select Pre0.Number,Member.Name,Pre0.IP,Pre0.Time,min(Pre1.Time)
from Pre0 left join Pre1 on Pre0.Number=Pre1.Number and Pre1.Time>=Pre0.Time inner join Member ON Pre0.Number=Member.Number
where Pre0.Style='签到' and Pre1.Style='签退'
group by Pre0.Number,Member.Name,Pre0.IP,Pre0.Time
这样行不