hql分组查询的问题？望路过的朋友帮解决！
我现在有一张用户表，有email字段,内容如下：email:
aaa@163.com
aaa@sina.com
aaa@uintec.com
bbb@163.com
bbb@163.com
bbb@sina.com
现在要用@符号和.符号之间的字符串进行分组和排序，请问大哥们，这样的hql语句怎么写？能写出sql语句也一并写出，小弟铭记于心，谢谢！ --------------------编程问答-------------------- 这个可以数据不是很大，可以将数据查找出来在程序中处理 --------------------编程问答-------------------- select a.id,a.mark from
(
select table.id,'sina' as mark from table where table.email like '%@sina.%'
union
select table.id,'163' as mark from table where table.email like '%@163.%'
union
select table.id,'126' as mark from table where table.email like '%@126.%'
union
select table.id,'qq' as mark from table where table.email like '%@qq.%'
) as a group by mark desc --------------------编程问答--------------------
随便来个 一二十条就挂了 --------------------编程问答-------------------- 全部查出来再 分割吧 --------------------编程问答-------------------- 为什么非要用sql来排序呢？这种情况下在程序中处理就好了撒。自己写个比较器就搞定的事 --------------------编程问答--------------------
SELECT *
FROM tab
order by substring(email, charindex('@', email), charindex('.', email))
这是排序的，分组的照着写就是了，希望对你有用吧 --------------------编程问答-------------------- 基本SQL SERVER：
select * from 用户表 order by substring(email, charindex('@', email) + 1, (charindex('.', email) - charindex('@', email) - 1)) desc; --------------------编程问答-------------------- 如果是oracle貌似要换成INSTR函数 --------------------编程问答-------------------- 谢谢各位的顶力相助！！请问一下HQL语句支持substring吗？还有那个union不太实际，邮箱的后缀太多了，这样union下去没完没了。用户表的实体类为FIsUser，这是我写的hql语句
from FisUser where email not like '%<%' and email not like '%(%' and email not like '%=%' and email not like '%\"%' group by substring(email, charindex('@', email), charindex('.', email))
可惜报错了！
[ERROR] 2011-12-20 18:02:43,578 (JDBCExceptionReporter.java:78) - FUNCTION fis_app.charindex does not exist
2011-12-20 18:02:43 org.apache.catalina.core.StandardWrapperValve invoke
严重: Servlet.service() for servlet jsp threw exception
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: FUNCTION fis_app.charindex does not exist
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
at java.lang.reflect.Constructor.newInstance(Constructor.java:513)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:406)
at com.mysql.jdbc.Util.getInstance(Util.java:381)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1030)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:956)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3491)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3423)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1936)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2060)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2542)
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1734)
at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1885)
at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeQuery(NewProxyPreparedStatement.java:76)
at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:186)
at org.hibernate.loader.Loader.getResultSet(Loader.java:1787)
at org.hibernate.loader.Loader.doQuery(Loader.java:674)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:236)
at org.hibernate.loader.Loader.doList(Loader.java:2220)
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2104)
at org.hibernate.loader.Loader.list(Loader.java:2099)
at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:378)
at org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:338)
at org.hibernate.engine.query.HQLQueryPlan.performList(HQLQueryPlan.java:172)
at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1121)
at org.hibernate.impl.QueryImpl.list(QueryImpl.java:79)
at org.uintec.dao.impl.BaseDAO.executeFind(BaseDAO.java:279)
at org.uintec.dao.impl.BaseDAO.executeFind(BaseDAO.java:144)
at com.web.user.bo.impl.UserBO.getAllUser(UserBO.java:117)
at com.fis.report.util.SendExecution.mainEx(SendExecution.java:275)
at org.apache.jsp.test_jsp._jspService(test_jsp.java:97)
at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:70)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
at org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:377)
at org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:313)
at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:260)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at org.apache.struts2.dispatcher.FilterDispatcher.doFilter(FilterDispatcher.java:389)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:96)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:76)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:298)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:852)
at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:588)
at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:489)
at java.lang.Thread.run(Thread.java:619) --------------------编程问答-------------------- 还有忘记说了，我用的是Mysql数据库 --------------------编程问答-------------------- 先取出来，再自己折腾
补充：Java , Web 开发
