- 论坛徽章:
- 0
|
请教查询问题
上次讲的局限:你要确保,ip和dt组合唯一,即他们是表的组合键
上次我已讲了一下,实际上,同一ip,意味着同一机器,在同一天内,同一台机器维护记录应该只有一条,所有按日期,再按ip排是无意义的。
要么,就按ip排序
建表:
-- EMS PostgreSQL Manager 2.4.0.1
-- ---------------------------------------
-- Host : 192.168.1.6
-- Database : template1
--
-- Structure for table comp (OID = 26703) :
--
SET search_path = public, pg_catalog;
CREATE TABLE comp (
id serial NOT NULL,
ip character varying(20),
dt date,
rm text
);
--
-- Data for blobs (OID = 26703) (LIMIT 0,5)
--
INSERT INTO comp (id, ip, dt, rm) VALUES (5, NULL, '2001-01-01', 'ee');
INSERT INTO comp (id, ip, dt, rm) VALUES (3, '10.0.0.1', '2001-01-02', 'cc');
INSERT INTO comp (id, ip, dt, rm) VALUES (1, '10.0.1.1', '2005-01-01', 'aaa');
INSERT INTO comp (id, ip, dt, rm) VALUES (2, '10.0.1.1', '2005-10-02', 'bb');
INSERT INTO comp (id, ip, dt, rm) VALUES (4, '10.0.3.1', '2004-01-09', 'dd');
COMMIT;
--
-- Data for sequence public.comp_id_seq (OID = 26701)
--
SELECT pg_catalog.setval('comp_id_seq', 7, false);
COMMENT ON SCHEMA public IS 'Standard public schema';
//查询
select * from comp
where (ip,dt) IN
(
select distinct on(ip) ip,dt from comp where ip is not null
)
order by ip
如果ip,日期不是组合键,直接加就行
order by dt,ip或order by ip,dt |
|