sql关键字 With

最新推荐文章于 2024-01-04 00:01:15 发布

原创最新推荐文章于 2024-01-04 00:01:15 发布 · 1.2k 阅读

0 ·

CC 4.0 BY-SA版权

oracle和sqlserver里面是有with这个关键字的

sqlserver:

with nodes as (select a.id ,a.name text,a.superorId parentId from BT_CountryRelation a where a.superorId=-1
union all 
select a.id ,a.name text,a.superorId parentId from BT_CountryRelation a inner join nodes b on a.superorId = b.id)
select c.id,c.name text,c.superorId parentId from BT_CountryRelation c 
where c.id in(select id from nodes) and c.superorId <>-1

public Map<?,?> getCityInfoMap(HttpServletRequest request){
		String cityId = req.getValue(request, "cityName");
//		String sql = " select * from BT_Summarize where summarize_address = '"+cityId+"'";
		String sql = " with jj as (select summarize_content jj from BT_Summarize where summarize_address='"+cityId+"' and summarize_type='简介') "
			+",ms as (select summarize_content ms from BT_Summarize where summarize_address='"+cityId+"' and summarize_type='美食') "
			+",gw as (select summarize_content gw from BT_Summarize where summarize_address='"+cityId+"' and summarize_type='购物') "
			+",jt as (select summarize_content jt from BT_Summarize where summarize_address='"+cityId+"' and summarize_type='特价门票') "
			+"select * from jj , ms , gw , jt";
		log.debug("城市简介sql："+sql);
		return db.queryForMap(sql);
	}