有表A,B
A:
ID NAME
1 name1
2 name2
3 name3
B:
ID AID NAME
1 1 bname1
2 1 bname2
3 1 bname3
4 3 bname4查询结果
AID ANAME BID BNAME NUM
1 name1 1 bname1 3
1 name1 2 bname2 3
1 name1 3 bname3 3
2 name2 0或1或空
3 name3 4 bname4 1下面方法我已经知道,还有没有其他方法
select * from A
left join B on A.id=B.aid
left join (select aid,count(1) num from A
left join B on A.id=B.aid group by aid) c
on A.id=C.aid
A:
ID NAME
1 name1
2 name2
3 name3
B:
ID AID NAME
1 1 bname1
2 1 bname2
3 1 bname3
4 3 bname4查询结果
AID ANAME BID BNAME NUM
1 name1 1 bname1 3
1 name1 2 bname2 3
1 name1 3 bname3 3
2 name2 0或1或空
3 name3 4 bname4 1下面方法我已经知道,还有没有其他方法
select * from A
left join B on A.id=B.aid
left join (select aid,count(1) num from A
left join B on A.id=B.aid group by aid) c
on A.id=C.aid
select a.*, b.*, count(1) over (partition by a.id) from a,b where a.id = b.aid(+);
或者
select a.*, b.*, count(aid) over (partition by a.id) from a,b where a.id = b.aid(+);
除了count(),avg()还有哪些呢?
AVG
CORR
COVAR_POP
COVAR_SAMP
COUNT
CUME_DIST
DENSE_RANK
FIRST
FIRST_VALUE
LAG
LAST
LAST_VALUE
LEAD
MAX
MIN
NTILE
PERCENT_RANK
PERCENTILE_CONT
PERCENTILE_DISC
RANK
RATIO_TO_REPORT
REGR_ (Linear Regression) Functions
ROW_NUMBER
STDDEV
STDDEV_POP
STDDEV_SAMP
SUM
VAR_POP
VAR_SAMP
VARIANCE
几乎所有的分析函数和集合函数