select top 1 * from goods where BTypeID =1
select top 1 * from goods where BTypeID =2
select top 1 * from goods where BTypeID =3
select top 1 * from goods where BTypeID =4
在以上的四条sql语句中,如何用一条sql语句实现分组查询,并且每组之查询一条数据,重新构成新的数据
等待中
[b]问题补充:[/b]
数据库是sql 2005 的
[b]问题补充:[/b]
只能查询出一条数据啊,我要的是
同一张表,有四种类别,类别的用BTypeID区分,然后每个类别只查询一条数据,并构成新的数据
[b]问题补充:[/b]
select top 1 * from goods where BTypeID =1
union all
select top 1 * from goods where BTypeID =2
union all
select top 1 * from goods where BTypeID =3
union all
select top 1 * from goods where BTypeID =4
BTypeID是外键,同时是另一张表的主键,以上有没有更简洁的方法啊
[b]问题补充:[/b]
我用struts2 + spring2.5 + hibernate3.2架构的,所以希望各位高手帮我把它能成 HQL 语句的形式,本人学习ssh还不是很熟悉,以后望各位多多指点。谢谢!
[b]问题补充:[/b]
select top 1 * from goods where BTypeID =1
select top 1 * from goods where BTypeID =2
select top 1 * from goods where BTypeID =3
select top 1 * from goods where BTypeID =4
SELECT * FROM goods g WHERE ID in(SELECT TOP 1 ID FROM goods y WHERE g.BTypeID = y.BTypeID)
这几条SQL语句中,上面的四条与下面的一条是等效的,但是现在我要把下面的一条改成 hql 的形式,请各位帮忙一下
另外,用后一条语句查询时用
public List findPopular(){
Session session = this.getHibernateTemplate().getSessionFactory().openSession();
log.debug("finding top Popular Goods instances");
try {
String sql = "SELECT * FROM goods g WHERE ID in(SELECT TOP 1 ID FROM goods WHERE g.BTypeID = BTypeID ORDER BY PutTime DESC) ";
Query query = session.createSQLQuery(sql); //执行的是 sql 语句
List list = query.list();
System.out.println(list.size());
return list;
} catch (RuntimeException re) {
log.error("find all failed;查询精品推荐查询出错", re);
throw re;
}
}
可以查询出查到的记录数,但是在却获取不到 good 物品的对象来,也无法输出信息,请问各位是什么原因啊
这里关键是在sql语句,如果可以改成hql语句,问题应该就简单化了