select id,processid,convert(varchar(10),createdate,120) as createdate,count(1) as cou from tablename group by id,processid,convert(varchar(10),createdate,120)
select count(*) from tablename group by id,processid,convert(varchar(10),createdate,120)
SELECT [id], processid, convert(varchar(10),createdate,112) as createdate, count(*) as count FROM tablenameGROUP BY id,processid,convert(varchar(10),createdate,112)
weasea(尘一笑) ( ) 信誉:97 Blog 2007-01-19 15:50:16 得分: 0
--tryselect id, processid, createdate=convert(char(10), createdate, 120), count(*) from tbName group by id, processid, convert(char(10), createdate, 120)
select id, 天数=convert(varchar(10),createdate,120),记录=count(*) from 表 group by id,convert(varchar(10),createdate,120)
select convert(datetime,floor(convert(float, createdate))) as createdate, count(*) from tablename group by id, processid, convert(datetime,floor(convert(float, createdate)))主要问题是将日期时间型数据转换为日期,方式很多,上面只是一种
create table table1(id varchar(10), processid varchar(10), createdate datetime) insert table1 select '1', '1' , '2007-1-16 9:15:27' union all select '1', '1' , '2007-1-16 9:15:22' union all select '2', '2' , '2007-2-17 9:15:27' union all select '3', '3' , '2007-2-17 9:15:27' union all select '3', '3' , '2007-2-16 9:15:27' union all select '1', '1' , '2007-1-16 9:15:22' union all select '1', '1' , '2007-1-16 9:15:22' union all select '2', '2' , '2007-2-17 9:15:27' union all select '3', '3' , '2007-2-17 9:15:27' union all select '3', '3' , '2007-2-16 9:15:27' SELECT [id], processid,createdate, convert(varchar(10),createdate,112) as createdate2, count(*) as count FROM table1 GROUP BY id,processid,createdate,convert(varchar(10),createdate,112)------------------ id processid createdate createdate2 count 1 1 2007-01-16 09:15:22.000 20070116 3 1 1 2007-01-16 09:15:27.000 20070116 1 2 2 2007-02-17 09:15:27.000 20070217 2 3 3 2007-02-16 09:15:27.000 20070216 2 3 3 2007-02-17 09:15:27.000 20070217 2
from tablename
group by id,processid,convert(varchar(10),createdate,120)
processid,
convert(varchar(10),createdate,112) as createdate,
count(*) as count FROM tablenameGROUP BY id,processid,convert(varchar(10),createdate,112)
(DATEDIFF(day, t1.createdate,T2.createdate) =0)我这么比较时间好像不起作用
----------------这句话确实能得知两者在一天
把语句全贴出来看看~
from tbName
group by id, processid, convert(char(10), createdate, 120)
from 表
group by id,convert(varchar(10),createdate,120)
group by id, processid, convert(datetime,floor(convert(float, createdate)))主要问题是将日期时间型数据转换为日期,方式很多,上面只是一种
insert table1
select '1', '1' , '2007-1-16 9:15:27' union all
select '1', '1' , '2007-1-16 9:15:22' union all
select '2', '2' , '2007-2-17 9:15:27' union all
select '3', '3' , '2007-2-17 9:15:27' union all
select '3', '3' , '2007-2-16 9:15:27' union all
select '1', '1' , '2007-1-16 9:15:22' union all
select '1', '1' , '2007-1-16 9:15:22' union all
select '2', '2' , '2007-2-17 9:15:27' union all
select '3', '3' , '2007-2-17 9:15:27' union all
select '3', '3' , '2007-2-16 9:15:27'
SELECT
[id],
processid,createdate,
convert(varchar(10),createdate,112) as createdate2,
count(*) as count
FROM table1
GROUP BY id,processid,createdate,convert(varchar(10),createdate,112)------------------
id processid createdate createdate2 count
1 1 2007-01-16 09:15:22.000 20070116 3
1 1 2007-01-16 09:15:27.000 20070116 1
2 2 2007-02-17 09:15:27.000 20070217 2
3 3 2007-02-16 09:15:27.000 20070216 2
3 3 2007-02-17 09:15:27.000 20070217 2