select t1.name, t1.effictive_date, t1.qty from table_name t1 where exists (select 1 from table_name t2 where t2.name=t1.name group by t2.name having min(t2.effictive_date)=t1.effictive_date);
不对我是取最新的记录。怎么是min
--方法1 SELECT * FROM table_name a WHERE NOT EXISTS (SELECT * FROM table_name b WHERE b.name = a.name AND b.effictive_date > a.effictive_date);--方法2 SELECT * FROM table_name a WHERE (a.name, a.effictive_date) IN (SELECT b.name, MAX(b.effictive_date) FROM table_name b);--方法3 SELECT * FROM table_name a WHERE a.effictive_date >= ALL (SELECT b.effictive_date FROM table_name b WHERE b.name = a.name);
--方法4 SELECT name, effictive_date, qty FROM (SELECT t.*, row_number() over(PARTITION BY t.name ORDER BY t.effictive_date DESC) rn FROM table_name t) WHERE rn = 1;
--方法2(上面少写了条件) SELECT * FROM table_name a WHERE (a.name, a.effictive_date) IN (SELECT b.name, MAX(b.effictive_date) FROM table_name b group by b.name);
--方法5(与方法2类似) SELECT * FROM table_name a WHERE (a.name, a.effictive_date) IN (SELECT MAX(b.name), MAX(b.effictive_date) FROM table_name b WHERE b.name = a.name);
select t1.name, t1.effictive_date, t1.qty from table_name t1 where exists (select 1 from table_name t2 where t2.name=t1.name group by t2.name having max(t2.effictive_date)=t1.effictive_date);
from table_name t1
where exists (select 1 from table_name t2
where t2.name=t1.name
group by t2.name
having min(t2.effictive_date)=t1.effictive_date);
SELECT *
FROM table_name a
WHERE NOT EXISTS (SELECT *
FROM table_name b
WHERE b.name = a.name
AND b.effictive_date > a.effictive_date);--方法2
SELECT *
FROM table_name a
WHERE (a.name, a.effictive_date) IN
(SELECT b.name, MAX(b.effictive_date) FROM table_name b);--方法3
SELECT *
FROM table_name a
WHERE a.effictive_date >= ALL
(SELECT b.effictive_date FROM table_name b WHERE b.name = a.name);
--方法4
SELECT name, effictive_date, qty
FROM (SELECT t.*,
row_number() over(PARTITION BY t.name ORDER BY t.effictive_date DESC) rn
FROM table_name t)
WHERE rn = 1;
SELECT *
FROM table_name a
WHERE (a.name, a.effictive_date) IN
(SELECT b.name, MAX(b.effictive_date) FROM table_name b group by b.name);
SELECT *
FROM table_name a
WHERE (a.name, a.effictive_date) IN
(SELECT MAX(b.name), MAX(b.effictive_date)
FROM table_name b
WHERE b.name = a.name);
-- 最新的,就用max()撒!不对,不知道改?
-- 事是死的,人是活的;你一点改动能力也没有,那公司还要你做啥?
from table_name t1
where exists (select 1 from table_name t2
where t2.name=t1.name
group by t2.name
having max(t2.effictive_date)=t1.effictive_date);