`

数据库查询

 
阅读更多
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倒叙排列

 

注意:上面基本能把效果做出来,但是效率没有测试,优化查询待续.......

 

 

  • 大小: 95.6 KB
分享到:
评论

相关推荐

Global site tag (gtag.js) - Google Analytics