一个关于having max的问题
sql1:
SELECT a.* FROM td_send a,
(SELECT ID FROM td_send GROUP BY ID HAVING MAX(STEPNOW)=2) b
WHERE a.ID=b.id;
sql2:
SELECT a.* FROM td_send a where STEPNOW=2;
以上两个SQL有什么不同吗？
[解决办法]
第一个是以ID为筛选列，第二个是以STEPNOW为筛选列，能一样么
WITH t AS
(select 1 ID ,0 step from dual UNION
select 1 ID ,1 step from dual UNION
select 1 ID ,2 step from dual UNION
select 2 ID ,0 step from dual UNION
select 2 ID ,1 step from dual )
/*SELECT ID FROM t GROUP BY ID HAVING MAX(STEP)=2*/
/*SELECT a.* FROM t a where step=2;*/
SELECT a.* FROM t a,
(SELECT ID FROM t GROUP BY ID HAVING MAX(STEP)=2) b
WHERE a.ID=b.id;
