表格CAR_CD
编号 车号 考试状态 正常 考试车型
ID KSCH KSStstus Status KSCX
1 1 False True A1
2 2 False True A1
3 3 False True A1
4 4 False True B2
5 5 False True B2
6 6 False True C1
7 7 False True C1
8 8 False True C2
9 9 False True C2表格STUDENT_KM2
编号 身份证 姓名 车号 考试车型 考试状态 签到时间
ID SFZMHM XM KSCH KSCX YK FCTIME
1 1 张三 1 A1 False 2011-9-20 1:00:00
2 2 王二 1 A1 False 2011-9-20 3:00:00
3 3 李四 1 A1 False 2011-9-20 2:00:00
4 4 萨科大将 3 A1 False 2011-9-20 1:01:00
5 5 上课 2 A1 False 2011-9-20 4:00:01
6 6 来看看 2 A1 False 2011-9-20 4:01:00
7 7 护符 4 B2 False 2011-9-20 5:00:00
8 8 散打王 3 B2 False 2011-9-20 7:00:00
9 9 撒旦 5 B2 False 2011-9-20 0:14:00
10 10 纳尼 5 B2 False 2011-9-20 21:00:00
11 11 苏打水 4 B2 False 2011-9-20 14:00:00
12 12 矮人 7 C1 False 2011-9-20 4:00:00
13 13 大 6 C1 False 2011-9-20 22:00:00
14 14 是哇 6 C1 False 2011-9-20 9:00:00
15 15 速度 6 C1 False 2011-9-20 7:00:00
16 16 甘氨酸 8 C2 False 2011-9-20 5:00:00
17 17 哥啊 9 C2 False 2011-9-20 3:23:00
18 18 DSA 9 C2 False 2011-9-20 4:11:00
19 19 FAFS 9 C2 False 2011-9-20 2:12:00如上两个表格,在STUDENT_KM2表格中
查找出前几条记录(CAR_CD表格中存在的考试车型(现在已知是A1,B2,C1,C3)本来是要查出来) 按照时间排序,并且 YK是False
找出来的结果目前是4条 只要 SFZMHM KSCH KSCX YK 也可以
编号 身份证 姓名 车号 考试车型 考试状态 签到时间
ID SFZMHM XM KSCH KSCX YK FCTIME
1 1 张三 1 A1 False 2011-9-20 1:00:00
9 9 撒旦 5 B2 False 2011-9-20 0:14:00
12 12 矮人 7 C1 False 2011-9-20 4:00:00
19 19 FAFS 9 C2 False 2011-9-20 2:12:00
各位大哥,仁者见仁智者见智,请教了~~~~拜谢!!!
编号 车号 考试状态 正常 考试车型
ID KSCH KSStstus Status KSCX
1 1 False True A1
2 2 False True A1
3 3 False True A1
4 4 False True B2
5 5 False True B2
6 6 False True C1
7 7 False True C1
8 8 False True C2
9 9 False True C2表格STUDENT_KM2
编号 身份证 姓名 车号 考试车型 考试状态 签到时间
ID SFZMHM XM KSCH KSCX YK FCTIME
1 1 张三 1 A1 False 2011-9-20 1:00:00
2 2 王二 1 A1 False 2011-9-20 3:00:00
3 3 李四 1 A1 False 2011-9-20 2:00:00
4 4 萨科大将 3 A1 False 2011-9-20 1:01:00
5 5 上课 2 A1 False 2011-9-20 4:00:01
6 6 来看看 2 A1 False 2011-9-20 4:01:00
7 7 护符 4 B2 False 2011-9-20 5:00:00
8 8 散打王 3 B2 False 2011-9-20 7:00:00
9 9 撒旦 5 B2 False 2011-9-20 0:14:00
10 10 纳尼 5 B2 False 2011-9-20 21:00:00
11 11 苏打水 4 B2 False 2011-9-20 14:00:00
12 12 矮人 7 C1 False 2011-9-20 4:00:00
13 13 大 6 C1 False 2011-9-20 22:00:00
14 14 是哇 6 C1 False 2011-9-20 9:00:00
15 15 速度 6 C1 False 2011-9-20 7:00:00
16 16 甘氨酸 8 C2 False 2011-9-20 5:00:00
17 17 哥啊 9 C2 False 2011-9-20 3:23:00
18 18 DSA 9 C2 False 2011-9-20 4:11:00
19 19 FAFS 9 C2 False 2011-9-20 2:12:00如上两个表格,在STUDENT_KM2表格中
查找出前几条记录(CAR_CD表格中存在的考试车型(现在已知是A1,B2,C1,C3)本来是要查出来) 按照时间排序,并且 YK是False
找出来的结果目前是4条 只要 SFZMHM KSCH KSCX YK 也可以
编号 身份证 姓名 车号 考试车型 考试状态 签到时间
ID SFZMHM XM KSCH KSCX YK FCTIME
1 1 张三 1 A1 False 2011-9-20 1:00:00
9 9 撒旦 5 B2 False 2011-9-20 0:14:00
12 12 矮人 7 C1 False 2011-9-20 4:00:00
19 19 FAFS 9 C2 False 2011-9-20 2:12:00
各位大哥,仁者见仁智者见智,请教了~~~~拜谢!!!
-- Author :fredrickhu(小F,向高手学习)
-- Date :2011-09-15 16:13:45
-- Verstion:
-- Microsoft SQL Server 2008 R2 (RTM) - 10.50.1617.0 (Intel X86)
-- Apr 22 2011 11:57:00
-- Copyright (c) Microsoft Corporation
-- Enterprise Evaluation Edition on Windows NT 6.1 <X64> (Build 7600: ) (WOW64)
--
----------------------------------------------------------------
--> 测试数据:[CAR_CD]
if object_id('[CAR_CD]') is not null drop table [CAR_CD]
go
create table [CAR_CD]([ID] int,[KSCH] int,[KSStstus] varchar(5),[Status] varchar(4),[KSCX] varchar(2))
insert [CAR_CD]
select 1,1,'False','True','A1' union all
select 2,2,'False','True','A1' union all
select 3,3,'False','True','A1' union all
select 4,4,'False','True','B2' union all
select 5,5,'False','True','B2' union all
select 6,6,'False','True','C1' union all
select 7,7,'False','True','C1' union all
select 8,8,'False','True','C2' union all
select 9,9,'False','True','C2'
--> 测试数据:[STUDENT_KM2]
if object_id('[STUDENT_KM2]') is not null drop table [STUDENT_KM2]
go
create table [STUDENT_KM2]([ID] int,[SFZMHM] int,[XM] varchar(8),[KSCH] int,[KSCX] varchar(2),[YK] varchar(5),[FCTIME] datetime)
insert [STUDENT_KM2]
select 1,1,'张三',1,'A1','False','2011-9-20 1:00:00' union all
select 2,2,'王二',1,'A1','False','2011-9-20 3:00:00' union all
select 3,3,'李四',1,'A1','False','2011-9-20 2:00:00' union all
select 4,4,'萨科大将',3,'A1','False','2011-9-20 1:01:00' union all
select 5,5,'上课',2,'A1','False','2011-9-20 4:00:01' union all
select 6,6,'来看看',2,'A1','False','2011-9-20 4:01:00' union all
select 7,7,'护符',4,'B2','False','2011-9-20 5:00:00' union all
select 8,8,'散打王',3,'B2','False','2011-9-20 7:00:00' union all
select 9,9,'撒旦',5,'B2','False','2011-9-20 0:14:00' union all
select 10,10,'纳尼',5,'B2','False','2011-9-20 21:00:00' union all
select 11,11,'苏打水',4,'B2','False','2011-9-20 14:00:00' union all
select 12,12,'矮人',7,'C1','False','2011-9-20 4:00:00' union all
select 13,13,'大',6,'C1','False','2011-9-20 22:00:00' union all
select 14,14,'是哇',6,'C1','False','2011-9-20 9:00:00' union all
select 15,15,'速度',6,'C1','False','2011-9-20 7:00:00' union all
select 16,16,'甘氨酸',8,'C2','False','2011-9-20 5:00:00' union all
select 17,17,'哥啊',9,'C2','False','2011-9-20 3:23:00' union all
select 18,18,'DSA',9,'C2','False','2011-9-20 4:11:00' union all
select 19,19,'FAFS',9,'C2','False','2011-9-20 2:12:00'
--------------开始查询--------------------------
select
top 4 a.ID ,b.SFZMHM ,b.XM,b. KSCH ,b.KSCX ,b.YK ,b.FCTIME
from
[CAR_CD] a,[STUDENT_KM2] b
where
a.KSCH=b.KSCH
and
b.YK='False'
and
exists(select 1 from [CAR_CD] where KSCX=a.KSCX)
order by
b. FCTIME
----------------结果----------------------------
/*ID SFZMHM XM KSCH KSCX YK FCTIME
----------- ----------- -------- ----------- ---- ----- -----------------------
5 9 撒旦 5 B2 False 2011-09-20 00:14:00.000
1 1 张三 1 A1 False 2011-09-20 01:00:00.000
3 4 萨科大将 3 A1 False 2011-09-20 01:01:00.000
1 3 李四 1 A1 False 2011-09-20 02:00:00.000(4 行受影响)
*/
--> 测试数据: [CAR_CD]
if object_id('[CAR_CD]') is not null drop table [CAR_CD]
create table [CAR_CD] (ID int,KSCH int,KSStstus varchar(5),Status varchar(4),KSCX varchar(2))
insert into [CAR_CD]
select 1,1,'False','True','A1' union all
select 2,2,'False','True','A1' union all
select 3,3,'False','True','A1' union all
select 4,4,'False','True','B2' union all
select 5,5,'False','True','B2' union all
select 6,6,'False','True','C1' union all
select 7,7,'False','True','C1' union all
select 8,8,'False','True','C2' union all
select 9,9,'False','True','C2'
--> 测试数据: [STUDENT_KM2]
if object_id('[STUDENT_KM2]') is not null drop table [STUDENT_KM2]
create table [STUDENT_KM2] (ID int,SFZMHM int,XM varchar(8),KSCH int,KSCX varchar(2),YK varchar(5),FCTIME datetime)
insert into [STUDENT_KM2]
select 1,1,'张三',1,'A1','False','2011-9-20 1:00:00' union all
select 2,2,'王二',1,'A1','False','2011-9-20 3:00:00' union all
select 3,3,'李四',1,'A1','False','2011-9-20 2:00:00' union all
select 4,4,'萨科大将',3,'A1','False','2011-9-20 1:01:00' union all
select 5,5,'上课',2,'A1','False','2011-9-20 4:00:01' union all
select 6,6,'来看看',2,'A1','False','2011-9-20 4:01:00' union all
select 7,7,'护符',4,'B2','False','2011-9-20 5:00:00' union all
select 8,8,'散打王',3,'B2','False','2011-9-20 7:00:00' union all
select 9,9,'撒旦',5,'B2','False','2011-9-20 0:14:00' union all
select 10,10,'纳尼',5,'B2','False','2011-9-20 21:00:00' union all
select 11,11,'苏打水',4,'B2','False','2011-9-20 14:00:00' union all
select 12,12,'矮人',7,'C1','False','2011-9-20 4:00:00' union all
select 13,13,'大',6,'C1','False','2011-9-20 22:00:00' union all
select 14,14,'是哇',6,'C1','False','2011-9-20 9:00:00' union all
select 15,15,'速度',6,'C1','False','2011-9-20 7:00:00' union all
select 16,16,'甘氨酸',8,'C2','False','2011-9-20 5:00:00' union all
select 17,17,'哥啊',9,'C2','False','2011-9-20 3:23:00' union all
select 18,18,'DSA',9,'C2','False','2011-9-20 4:11:00' union all
select 19,19,'FAFS',9,'C2','False','2011-9-20 2:12:00'--开始查询
select * from STUDENT_KM2 a where exists(select 1 from CAR_CD where KSCX=a.KSCX)
and not exists(select 1 from STUDENT_KM2 where KSCX=a.KSCX and FCTIME<a.FCTIME and YK='False')--结束查询
drop table [CAR_CD],[STUDENT_KM2]/*
ID SFZMHM XM KSCH KSCX YK FCTIME
----------- ----------- -------- ----------- ---- ----- -----------------------
1 1 张三 1 A1 False 2011-09-20 01:00:00.000
9 9 撒旦 5 B2 False 2011-09-20 00:14:00.000
12 12 矮人 7 C1 False 2011-09-20 04:00:00.000
19 19 FAFS 9 C2 False 2011-09-20 02:12:00.000(4 行受影响)
到Sudent_KM2里面找这对应的四条记录。。按照时间排序,各找出一条,才组成了四条记录