Select * from A﹐ ( Select columnB,Max(columnBT) As columnBT from B Group By columnB )D A.keyA=C.columnB
Select * from A﹐ ( Select columnB,Max(columnBT) As columnBT from B Group By columnB )D A.keyA=D.columnB
select b.columnb,max(isnull(b.columnbt),'') from 表b b left join 表a a on a.keya=b.columnb
表结构如下: create table task_log ( log_id serial NOT NULL UNIQUE PRIMARY KEY, prj_cd varchar(20), mod_cd varchar(20), dsc varchar(1024), log_ts timestamp NOT NULL, log_by varchar(20) NOT NULL, tp varchar(20), priority int, version_no numeric(20) DEFAULT 0, last_modifiedon Date );create table task_log_rpl ( log_rpl_id serial NOT NULL UNIQUE PRIMARY KEY, log_id numeric(20) NOT NULL, rpl_ts timestamp NOT NULL, rpl_by varchar(50) NOT NULL, sts varchar(20), msg varchar(1024), version_no numeric(20) DEFAULT 0, last_modifiedon Date );查询语句: SELECT * FROM task_log INNER JOIN (SELECT log_rpl_id,MAX(rpl_ts) as rpt_ts,rpl_by,sts,msg FROM task_log_rpl GROUP BY log_id) R ON task_log.log_id=C.log_id;错误: ERROR: Attribute task_log_rpl.log_rpl_id must be GROUPed or used in an aggregate function
SELECT * FROM task_log A INNER JOIN (Select log_rpl_id,rpl_ts,rpl_by,sts,msg from task_log_rpl T Where Not Exists(Select * from task_log_rpl Where log_rpl_id=T.log_rpl_id And rpl_ts>T.rpl_ts)) B ON A.log_id=B.log_id;
(
Select columnB,Max(columnBT) As columnBT
from B Group By columnB
)D
A.keyA=C.columnB
(
Select columnB,Max(columnBT) As columnBT
from B Group By columnB
)D
A.keyA=D.columnB
from 表b b
left join 表a a
on a.keya=b.columnb
create table task_log (
log_id serial NOT NULL UNIQUE PRIMARY KEY,
prj_cd varchar(20),
mod_cd varchar(20),
dsc varchar(1024),
log_ts timestamp NOT NULL,
log_by varchar(20) NOT NULL,
tp varchar(20),
priority int,
version_no numeric(20) DEFAULT 0,
last_modifiedon Date
);create table task_log_rpl (
log_rpl_id serial NOT NULL UNIQUE PRIMARY KEY,
log_id numeric(20) NOT NULL,
rpl_ts timestamp NOT NULL,
rpl_by varchar(50) NOT NULL,
sts varchar(20),
msg varchar(1024),
version_no numeric(20) DEFAULT 0,
last_modifiedon Date
);查询语句:
SELECT * FROM task_log INNER JOIN (SELECT log_rpl_id,MAX(rpl_ts) as rpt_ts,rpl_by,sts,msg FROM task_log_rpl GROUP BY log_id) R ON task_log.log_id=C.log_id;错误:
ERROR: Attribute task_log_rpl.log_rpl_id must be GROUPed or used in an aggregate function
SELECT * FROM task_log A
INNER JOIN
(Select log_rpl_id,rpl_ts,rpl_by,sts,msg from task_log_rpl T Where Not Exists(Select * from task_log_rpl Where log_rpl_id=T.log_rpl_id And rpl_ts>T.rpl_ts)) B
ON A.log_id=B.log_id;