SELECT
--COUNT(*) AS '数量' ,
row_number() over (order by a.EMPNAME) as '序号',
(SELECT top 1
a.EMPNAME AS '姓名'
FROM PT_PREHIREEMP_DETAILS a
INNER JOIN PT_PREHIREEMP_MAIN b
ON a.ORGCODE = b.ORGCODE
WHERE a.RPTDATE BETWEEN '2016-01-05 00:00:00.000'AND '2016-01-05 18:00:00.000'
AND b.LETTERSEQ = '20150076'
AND a.ORGCODE = '150401' ORDER BY a.EMPNAME) AS '名字',
a.EMPNAME AS '姓名',
a.GENDER AS '性别',
a.UNIT,
a.IDENTITYNO AS '身份证号',
a.INGRUOPMETHOD AS '进入本企业方式',
a.REMARK AS '备注',
b.LETTERSEQ AS '编号',
(SELECT CONVERT(DATE,a.RPTDATE,120)) AS '填报时间'
FROM PT_PREHIREEMP_DETAILS a
INNER JOIN PT_PREHIREEMP_MAIN b
ON a.ORGCODE = b.ORGCODE
WHERE a.RPTDATE BETWEEN '2016-01-05 00:00:00.000'AND '2016-01-05 18:00:00.000'
AND b.LETTERSEQ = '20150076'
AND a.ORGCODE = '150401' GROUP BY a.EMPNAME,a.EMPNAME,a.GENDER,a.UNIT,a.IDENTITYNO,a.INGRUOPMETHOD,a.REMARK,b.LETTERSEQ ,a.RPTDATE;
查询结果如下:
一,详解:
row_number() over (order by a.EMPNAME) as '序号';//给查询的条数做序号
top 1得到第一条记录。以前做的分页查询的效果就是这个top,可以得到m n直接的记录。
二.集合查询总条数
select count(*) from ( select a,b from table where ...) a
注意集合后面必须要有a,这是给集合添加一个别名。
也可以使用下面方法查询总条数:
select
(SELECT TOP 1 (row_number() over (order by a.EMPNAME)) FROM PT_PREHIREEMP_DETAILS a
INNER JOIN PT_PREHIREEMP_MAIN b
ON a.ORGCODE = b.ORGCODE
WHERE b.LETTERSEQ = '20150076' GROUP BY a.EMPNAME ORDER BY row_number() over (order by a.EMPNAME) DESC) AS '总数'
FROM PT_PREHIREEMP_DETAILS a
INNER JOIN PT_PREHIREEMP_MAIN b
ON a.ORGCODE = b.ORGCODE
WHERE b.LETTERSEQ = '20150076' GROUP BY a.EMPNAME
解释:
top 1 得到一个数,ORDER BY row_number() over (order by a.EMPNAME) DESC) 把序号按照EMPNAME倒叙排列
注意:上面基本能把效果做出来,但是效率没有测试,优化查询待续.......