select name, [登次数]=count(1), [最新登记时间]=max(cjdate) from 表 group by name order by [最新登记时间]
select name, [登次数]=count(1), [最新登记时间]=max(cjdate) from 表 group by name order by [最新登记时间] desc
select [登次数]=count(1), [最新登记时间]=max(cjdate), name from 表 group by name order by [最新登记时间] desc
--测试环境 declare @t table (id int,name varchar(10),cjdate datetime) insert @t select 1,'小明','2004-02-21 12:22:21' union all select 2,'小a','2004-02-25 12:22:21' union all select 3,'小c','2004-03-15 12:22:21' union all select 4,'小明','2004-02-23 12:22:21' union all select 5,'小a','2004-04-11 12:22:21 ' union all select 6,'小明','2004-05-21 12:22:21' union all select 7,'小d','2004-06-21 12:22:21' union all select 1,'小c','2005-02-21 12:22:21'--查询 select name, 登次数=(select count(*) from @t where name=a.name), 最新登记时间=(select max(cjdate) from @t where name=a.name) from @t a group by name --结果 name 登次数 最新登记时间 ---------- ----------- ------------------------------------------------------ 小a 2 2004-04-11 12:22:21.000 小c 2 2005-02-21 12:22:21.000 小d 1 2004-06-21 12:22:21.000 小明 3 2004-05-21 12:22:21.000(所影响的行数为 4 行)
--时间排序 select name, 登次数=(select count(*) from @t where name=a.name), 最新登记时间=(select max(cjdate) from @t where name=a.name) from @t a group by name order by 最新登记时间 desc --结果 name 登次数 最新登记时间 ---------- ----------- ------------------------------------------------------ 小c 2 2005-02-21 12:22:21.000 小d 1 2004-06-21 12:22:21.000 小明 3 2004-05-21 12:22:21.000 小a 2 2004-04-11 12:22:21.000(所影响的行数为 4 行) --楼主结果是不是有点问题??
--照下面的复制过去看下,是不是,楼上的基本上都是正确了。declare @t table (id int identity(1,1),name char(20),cjdate datetime)insert @t select '小明','2004-02-21 12:22:21' union all select '小a','2004-02-25 12:22:21' union all select '小c','2004-03-15 12:22:21' union all select '小明','2004-02-23 12:22:21' union all select '小a','2004-04-11 12:22:21 ' union all select '小明','2004-05-21 12:22:21' union all select '小d','2004-06-21 12:22:21' union all select '小c','2005-02-21 12:22:21'select name,max(cjdate) dt from @t group by name order by dt desc
SELECT NAME, COUNT(*) AS 登次数, MAX(cjdate) 最新登记时间 GROUP BY NAME ORDER BY 最新登记时间
楼上的很好啊,呵呵 就差 FROM 表名 SELECT NAME, COUNT(*) AS 登次数, MAX(cjdate) 最新登记时间 FROM 表名 GROUP BY NAME ORDER BY 最新登记时间
[登次数]=count(1),
[最新登记时间]=max(cjdate)
from 表
group by name
order by [最新登记时间]
[登次数]=count(1),
[最新登记时间]=max(cjdate)
from 表
group by name
order by [最新登记时间] desc
[最新登记时间]=max(cjdate),
name
from 表
group by name
order by [最新登记时间] desc
declare @t table (id int,name varchar(10),cjdate datetime)
insert @t select 1,'小明','2004-02-21 12:22:21'
union all select 2,'小a','2004-02-25 12:22:21'
union all select 3,'小c','2004-03-15 12:22:21'
union all select 4,'小明','2004-02-23 12:22:21'
union all select 5,'小a','2004-04-11 12:22:21 '
union all select 6,'小明','2004-05-21 12:22:21'
union all select 7,'小d','2004-06-21 12:22:21'
union all select 1,'小c','2005-02-21 12:22:21'--查询
select name,
登次数=(select count(*) from @t where name=a.name),
最新登记时间=(select max(cjdate) from @t where name=a.name)
from @t a
group by name
--结果
name 登次数 最新登记时间
---------- ----------- ------------------------------------------------------
小a 2 2004-04-11 12:22:21.000
小c 2 2005-02-21 12:22:21.000
小d 1 2004-06-21 12:22:21.000
小明 3 2004-05-21 12:22:21.000(所影响的行数为 4 行)
select name,
登次数=(select count(*) from @t where name=a.name),
最新登记时间=(select max(cjdate) from @t where name=a.name)
from @t a
group by name
order by 最新登记时间 desc
--结果
name 登次数 最新登记时间
---------- ----------- ------------------------------------------------------
小c 2 2005-02-21 12:22:21.000
小d 1 2004-06-21 12:22:21.000
小明 3 2004-05-21 12:22:21.000
小a 2 2004-04-11 12:22:21.000(所影响的行数为 4 行)
--楼主结果是不是有点问题??
select '小明','2004-02-21 12:22:21'
union all select '小a','2004-02-25 12:22:21'
union all select '小c','2004-03-15 12:22:21'
union all select '小明','2004-02-23 12:22:21'
union all select '小a','2004-04-11 12:22:21 '
union all select '小明','2004-05-21 12:22:21'
union all select '小d','2004-06-21 12:22:21'
union all select '小c','2005-02-21 12:22:21'select name,max(cjdate) dt from @t
group by name
order by dt desc
GROUP BY NAME
ORDER BY 最新登记时间
SELECT NAME, COUNT(*) AS 登次数, MAX(cjdate) 最新登记时间 FROM 表名
GROUP BY NAME
ORDER BY 最新登记时间