额,以这个为准SELECT 订单Id FROM 订单表, (SELECT 自编号,MIN(时间)时间 FROM 订单表 GROUP BY 自编号) A WHERE 订单表.自编号 = A.自编号 AND 订单表.时间 = A.时间
WITH cte AS ( SELECT *,rn=ROW_NUMBER() OVER(PARTITION BY 自编号 ORDER BY 时间),* FROM 订单表 ) SELECT * FROM cte WHERE rn=1
--多了个*,修正一下 WITH cte AS ( SELECT *,rn=ROW_NUMBER() OVER(PARTITION BY 自编号 ORDER BY 时间) FROM 订单表 ) SELECT * FROM cte WHERE rn=1
楼主sql server 什么版本的啊,如果是sql 2005以上可以用CTE的 楼主可以查一下MSDN
sql2005的 肿么办办 啊 大哥help
sql2005的 肿么办办 啊 大哥help可以这样:SELECT 客户id FROM 订单表 t1 INNER JOIN (SELECT 自编号,MIN(时间) 时间 FROM 订单表 GROUP BY 自编号) t2 ON t1.自编号=t2.自编号 AND t1.时间=t2.时间
SELECT 客户Id FROM 订单表, (SELECT 自编号,MIN(时间)时间 FROM 订单表 GROUP BY 自编号) A WHERE 订单表.自编号 = A.自编号 AND 订单表.时间 = A.时间
sql2005的 肿么办办 啊 大哥help可以这样:SELECT 客户id FROM 订单表 t1 INNER JOIN (SELECT 自编号,MIN(时间) 时间 FROM 订单表 GROUP BY 自编号) t2 ON t1.自编号=t2.自编号 AND t1.时间=t2.时间
我用的是hibernate hql语句 然后报了这样的错误,求好哥哥再指点一二是不是hql语句哪里要改一下 2014-08-16 19:23:01,264 ERROR (org.hibernate.hql.internal.ast.ErrorCounter:54) - line 1:73: unexpected token: ( 2014-08-16 19:23:01,279 ERROR (org.hibernate.hql.internal.ast.ErrorCounter:50) - line 1:73: unexpected token: ( line 1:73: unexpected token: ( at org.hibernate.hql.internal.antlr.HqlBaseParser.fromJoin(HqlBaseParser.java:1630) at org.hibernate.hql.internal.antlr.HqlBaseParser.fromClause(HqlBaseParser.java:1349) at org.hibernate.hql.internal.antlr.HqlBaseParser.selectFrom(HqlBaseParser.java:1055) at org.hibernate.hql.internal.antlr.HqlBaseParser.queryRule(HqlBaseParser.java:701) at org.hibernate.hql.internal.antlr.HqlBaseParser.selectStatement(HqlBaseParser.java:294) at org.hibernate.hql.internal.antlr.HqlBaseParser.statement(HqlBaseParser.java:157) at org.hibernate.hql.internal.ast.QueryTranslatorImpl.parse(QueryTranslatorImpl.java:266)
FROM 订单表,
(SELECT 自编号,MIN(时间)时间 FROM 订单表 GROUP BY 自编号) A
WHERE 订单表.自编号 = A.自编号 AND 订单表.时间 = A.时间
WITH cte AS (
SELECT *,rn=ROW_NUMBER() OVER(PARTITION BY 自编号 ORDER BY 时间),* FROM 订单表
)
SELECT * FROM cte WHERE rn=1
--多了个*,修正一下
WITH cte AS (
SELECT *,rn=ROW_NUMBER() OVER(PARTITION BY 自编号 ORDER BY 时间) FROM 订单表
)
SELECT * FROM cte WHERE rn=1
楼主可以查一下MSDN
INNER JOIN (SELECT 自编号,MIN(时间) 时间
FROM 订单表
GROUP BY 自编号) t2 ON t1.自编号=t2.自编号 AND t1.时间=t2.时间
FROM 订单表,
(SELECT 自编号,MIN(时间)时间 FROM 订单表 GROUP BY 自编号) A
WHERE 订单表.自编号 = A.自编号 AND 订单表.时间 = A.时间
INNER JOIN (SELECT 自编号,MIN(时间) 时间
FROM 订单表
GROUP BY 自编号) t2 ON t1.自编号=t2.自编号 AND t1.时间=t2.时间
我用的是hibernate hql语句
然后报了这样的错误,求好哥哥再指点一二是不是hql语句哪里要改一下
2014-08-16 19:23:01,264 ERROR (org.hibernate.hql.internal.ast.ErrorCounter:54) - line 1:73: unexpected token: (
2014-08-16 19:23:01,279 ERROR (org.hibernate.hql.internal.ast.ErrorCounter:50) - line 1:73: unexpected token: (
line 1:73: unexpected token: (
at org.hibernate.hql.internal.antlr.HqlBaseParser.fromJoin(HqlBaseParser.java:1630)
at org.hibernate.hql.internal.antlr.HqlBaseParser.fromClause(HqlBaseParser.java:1349)
at org.hibernate.hql.internal.antlr.HqlBaseParser.selectFrom(HqlBaseParser.java:1055)
at org.hibernate.hql.internal.antlr.HqlBaseParser.queryRule(HqlBaseParser.java:701)
at org.hibernate.hql.internal.antlr.HqlBaseParser.selectStatement(HqlBaseParser.java:294)
at org.hibernate.hql.internal.antlr.HqlBaseParser.statement(HqlBaseParser.java:157)
at org.hibernate.hql.internal.ast.QueryTranslatorImpl.parse(QueryTranslatorImpl.java:266)