select c.cat_id, c.cat_name,COUNT(i.image_id) AS new_images FROM brand_categories c , brand_images i where i.cat_id = c.cat_id(+) AND i.image_date >= to_date(1047989967,date_format) AND i.image_active=1 GROUP BY c.cat_id ORDER BY c.cat_order, c.cat_name ASC
select c.cat_id, c.cat_name,COUNT(i.image_id) AS new_images FROM brand_categories c , brand_images i where i.cat_id = c.cat_id(+) AND i.image_date >= to_date(1047989967,date_format) AND i.image_active=1 GROUP BY c.cat_id ORDER BY c.cat_order, c.cat_name ASC
id name1
1 hello1
2 hello2
3 hello3
4 hello4
6 hello6
表名:t2
id name1
2 hello2
3 hello3
5 hello5 在Mysql下用下面SQL语句查询:
SELECT t1.*,count(t2.id) as t2idcount FROM t1 LEFT JOIN t2 ON (t1.id=t2.id and t2.id>2) group by t1.id结果如下:
id name1 t2idcount
1 hello1 0
2 hello2 0
3 hello3 1
4 hello4 0
6 hello6 0 我要在Oracle下完成上述的查询结果,怎么用一条SQL语句实现??
要用标准的SQL,oracle不能用left join大虾,出手吧