数据库查询

 

mysql查询结果添加序列号

 

第一种方法：

select   (@i:=@i+1)   as   i,table_name.*   from   table_name,(select   @i:=0)   as   it 

第二种方法：

 

 

 

set @rownum=0;
select @rownum:=@rownum+1 as rownum, t.username from auth_user t limit 1,5;

 

 

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倒叙排列

 

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

 

参考：http://435727748.iteye.com/blog/2306903