取最大的一条
Table A
a1 NVRCHAR(10),
a2 NVRCHAR(10),
a3 NVRCHAR(10)
Table B
b1 NVRCHAR(10),
b2 NVRCHAR(10),
b3 NVRCHAR(10)
SELECT *
FROM A
INNER JOIN B
ON A.a1 = B.b1
where A.a2 = '1'
在这个基础上我还想加一个条件就是取A.a3最大的那条，要怎么写呀
方案一
SELECT *
FROM A
INNER JOIN B
ON A.a1 = B.b1
WHERE A.a2 = '1'
AND A.a3 =
(
SELECT MAX(A.a3)
FROM A
INNER JOIN B
ON A.a1 = B.b1
WHERE A.a2 = '1'
)
方案二
DECALRE A3_MAX NVRCHAR(10)
SELECT A3_MAX = MAX(A.a3)
FROM A
INNER JOIN B
ON A.a1 = B.b1
WHERE A.a2 = '1'
SELECT *
FROM A
INNER JOIN B
ON A.a1 = B.b1
WHERE A.a2 = '1'
AND A.a3 = A3_MAX
还有没有更好点的方法呀，执行的结果应该是可能有N条的情况，
所以用ORDERBY，然后top1好像也不行
[解决办法]
SELECT * FROM (SELECT a1,a2,MAX(a3) AS a3 FROM A WHERE a2='1' GROUP BY a1,a2) a INNER JOIN B b ON a.a1=b.b1
