select t1.id,t1.name,t1.keywords ,sum(t2.rating),count(t3.id) from t1,t2,t3 where t1.id=t2.pixid(+) and t1.id=t3.pixid(+) group by t1.id,t1.name,t1.keywords ;
还是一样的结果老兄,你的t1.id=t2.pixid(+) and t1.id=t3.pixid(+)中的加号是什么意思 ,没有见过这样的用法咯
一条SQL语句搞不定的,要么使用临时表,要么把数据取出用脚本处理
CREATE TABLE test ( id integer, name character varying(20) );CREATE TABLE info ( mid integer, value character varying(20) );CREATE TABLE per ( pid integer, per integer ); id | name ----+------ 1 | a 2 | b (2 rows) mid | value -----+------- 1 | aaaa 1 | bbbb 1 | cccc 1 | dddd (4 rows) pid | per -----+----- 1 | 5 1 | 4 (2 rows) select t.id , t.count , sum(s.per) from (select a.id , count(b.*) from test a left join info b on a.id = b.mid group by a.id) t left join per s on t.id = s.pid group by t.id ,t.count ; id | count | sum ----+-------+----- 1 | 4 | 9 2 | 0 | (2 rows)
I want to know why t1.id=t2.pixid(+) and t1.id=t3.pixid(+)中的加号是什么意思
id integer,
name character varying(20)
);CREATE TABLE info (
mid integer,
value character varying(20)
);CREATE TABLE per (
pid integer,
per integer
); id | name
----+------
1 | a
2 | b
(2 rows) mid | value
-----+-------
1 | aaaa
1 | bbbb
1 | cccc
1 | dddd
(4 rows) pid | per
-----+-----
1 | 5
1 | 4
(2 rows)
select t.id , t.count , sum(s.per) from (select a.id , count(b.*) from test a left join info b on a.id = b.mid group by a.id) t left join per s on t.id = s.pid group by t.id ,t.count ; id | count | sum
----+-------+-----
1 | 4 | 9
2 | 0 |
(2 rows)