--> 测试数据:[tbl] if object_id('[tbl]') is not null drop table [tbl] create table [tbl]([date] datetime) insert [tbl] select '2012-03-18 14:50' union all select '2012-03-18 14:55' union all select '2012-03-19 14:56'select CONVERT(varchar(10),[date],120) as [date] from tbl group by CONVERT(varchar(10),[date],120)/**date 2012-03-18 2012-03-19 */
if object_id('[tbl]') is not null drop table [tbl] create table [tbl]([date] datetime) insert [tbl] select '2012-03-18 14:50' union all select '2012-03-18 14:55' union all select '2012-03-19 14:56'select count(*) from tbl where CONVERT(varchar(10),[date],120) between '2012-3-12' and '2012-3-20'
提示 每个 GROUP BY 表达式必须至少包含一个不是外部引用的列。请指教。。
--不去重复 select convert(varchar(10),时间字段,120) from tb where convert(varchar(10),时间字段,120) between '2012-03-12' and '2012-03-20'--去重复 select distinct convert(varchar(10),时间字段,120) from tb where convert(varchar(10),时间字段,120) between '2012-03-12' and '2012-03-20'
--> 测试数据:[tbl] if object_id('[tbl]') is not null drop table [tbl] create table [tbl]([date] datetime) insert [tbl] select '2012-03-18 14:50' union all select '2012-03-18 14:55' union all select '2012-03-19 14:56' select count(*) as times from tbl where [date] between '2012-3-18' and '2012-3-20'/* times 3 */
如果要算每天的数量:select convert(varchar(10),时间字段,120) , count(1) from tb where convert(varchar(10),时间字段,120) between '2012-03-12' and '2012-03-20' group by convert(varchar(10),时间字段,120)
select * from tb where charindex('系统操作员name' , 字段) > 0select * from tb where charindex(','+'系统操作员name'+',' , ','+字段+',') > 0
select CONVERT(varchar(10),[date],120) as [date],count(*) as 次数 from tbl where [date] between '2012-3-18' and '2012-3-20' group by CONVERT(varchar(10),[date],120)
--> 测试数据:[tbl]
if object_id('[tbl]') is not null drop table [tbl]
create table [tbl]([date] datetime)
insert [tbl]
select '2012-03-18 14:50' union all
select '2012-03-18 14:55' union all
select '2012-03-19 14:56'select CONVERT(varchar(10),[date],120) as [date] from tbl
group by CONVERT(varchar(10),[date],120)/**date
2012-03-18
2012-03-19
*/
create table [tbl]([date] datetime)
insert [tbl]
select '2012-03-18 14:50' union all
select '2012-03-18 14:55' union all
select '2012-03-19 14:56'select count(*) from tbl
where CONVERT(varchar(10),[date],120) between '2012-3-12'
and
'2012-3-20'
每个 GROUP BY 表达式必须至少包含一个不是外部引用的列。请指教。。
select convert(varchar(10),时间字段,120) from tb where convert(varchar(10),时间字段,120) between '2012-03-12' and '2012-03-20'--去重复
select distinct convert(varchar(10),时间字段,120) from tb where convert(varchar(10),时间字段,120) between '2012-03-12' and '2012-03-20'
--> 测试数据:[tbl]
if object_id('[tbl]') is not null drop table [tbl]
create table [tbl]([date] datetime)
insert [tbl]
select '2012-03-18 14:50' union all
select '2012-03-18 14:55' union all
select '2012-03-19 14:56'
select count(*) as times from tbl
where [date] between '2012-3-18' and '2012-3-20'/*
times
3
*/
from tb
where convert(varchar(10),时间字段,120) between '2012-03-12' and '2012-03-20'
group by convert(varchar(10),时间字段,120)
8楼的很好用。6楼的有点不懂。。SQL一直没学过,就回个增删查改,悲剧。。麻烦再问一下,有没有把法查'系统管理员name,系统操作员name,系统维护员name'
数据是这种形式,都是姓名,如果我要模糊查 '系统操作员name',有没有把法把这3个名字分成类似数组,然后模糊去查系统操作员这个名字段的内容呢
from tbl
where [date] between '2012-3-18' and '2012-3-20'
group by CONVERT(varchar(10),[date],120)
就差这两天条结束了内牛满面