急，SQL问题

来源：百度知道 编辑：UC知道 时间：2024/06/15 07:51:08
表YWLOG
ID ACCNO OPTYPE
1 1111 4
2 1111 10
3 1111 11
4 2222 4
5 2222 10
6 3333 4
7 3333 10

一查询：在ACCNO相同里面查询OPTYPE=10并且ID在同一个ACCNO里面是最大的ACCNO

比如上面这个表
我希望查出来的结果是 accno=2222和accno=3333

--测试数据
declare @YWLOG table(ID int,ACCNO int, OPTYPE int)
insert into @YWLOG select 1, 1111, 4
insert into @YWLOG select 2, 1111, 10
insert into @YWLOG select 3, 1111, 11
insert into @YWLOG select 4, 2222, 4
insert into @YWLOG select 5, 2222, 10
insert into @YWLOG select 6, 3333, 4
insert into @YWLOG select 7, 3333, 10

--查询
--方法一
SELECT *
FROM @YWLOG AS U
WHERE ID=(
SELECT MAX(ID) FROM @YWLOG WHERE ACCNO=U.ACCNO
)
AND OPTYPE=10

--方法二
SELECT * FROM @YWLOG a
WHERE (SELECT COUNT(*) FROM @YWLOG b WHERE b.ACCNO = a.ACCNO AND b.id > a.id) < 1
AND OPTYPE=10

--方法三
SELECT * FROM @YWLOG x
where ID IN
(SELECT TOP 1 ID FROM @YWLOG y WHERE y.ACCNO = x.ACCNO ORDER BY ID desc)
AND OPTYPE=10

--方法四
SELECT ID=MAX(ID),ACCNO,OPTYPE=MAX(OPTYPE)
FROM @YWLOG AS U
GROUP BY ACCNO
HAVING MAX(OPTYPE)=10

-