再次感谢前辈们的指教，我继续问一个查询问题。
字段A字段B字段C字段D
A结束3a
A开始2b
A开始1c
B结束7a
B开始6b
B开始5c
B开始4d
B开始3e
B开始2f
B开始1g
C开始2a
C开始1b
D开始3a
D开始2b
D开始1c


字段B是字段A的状态，需要把不含结束的记录集提取出来，并且取得最大的那个字段C的值和最小的哪一个对应的字段D的值，所以，最终结果就是：
C 开始 2 b
D 开始 3 c
这样一个查询怎么写呢？
再一次谢谢CSDN的朋友们，我学到很多东西。。


[解决办法]

SQL code

select [字段A],[字段B],MAX([字段C]) as [字段C],max([字段D]) as [字段D]from testwhere [字段A] not in(select [字段A] from test where [字段B]='结束')group by [字段A],[字段B]
[解决办法]
SQL code
select  a.字段A ,a.字段B,a.字段C,b.字段Dfrom tab a,tab bwhere a.字段A = b.字段Aand not exists (  select 1  from tab  where 字段A = a.字段A  and 字段B = '结束'  )and not exists (  select 1  from tab  where 字段A = a.字段A  and 字段c > a.字段c  )and not exists (  select 1  from tab  where 字段A = b.字段A  and 字段c < b.字段c  )
[解决办法]
SQL code
IF EXISTS (SELECT 1 FROM SYSOBJECTS WHERE name = 'tba')BEGIN    DROP TABLE tbaENDGOCREATE TABLE tba(    a VARCHAR(10),    b VARCHAR(10),    c INT,    d VARCHAR(10))GOINSERT INTO tbaSELECT 'A','结束',3,'a' UNIONSELECT 'A','开始',2,'b' UNIONSELECT 'A','开始',1,'c' UNIONSELECT 'B','结束',7,'a' UNIONSELECT 'B','开始',6,'b' UNIONSELECT 'B','开始',5,'c' UNIONSELECT 'B','开始',4,'d' UNIONSELECT 'B','开始',3,'e' UNIONSELECT 'B','开始',2,'f' UNIONSELECT 'B','开始',1,'g' UNIONSELECT 'C','开始',2,'a' UNIONSELECT 'C','开始',1,'b' UNIONSELECT 'D','开始',3,'a' UNIONSELECT 'D','开始',2,'b' UNIONSELECT 'D','开始',1,'c'SELECT A,b,MAX(c) AS C,MAX(d) AS DFROM tbaWHERE a NOT IN (SELECT a from tba WHERE b = '结束')GROUP BY a,bA    b    C    DC    开始    2    bD    开始    3    c