27,579
社区成员
发帖
与我相关
我的任务
分享
select *,
得分=数量*(case
when 数量 between 1 and 5 then 1
when 数量 between 6 and 8 then 3
when 数量 between 9 and 12 then 5
when 数量>=13 then 7
end
)
from
(
select Inspect_name,sum(记录数) as 数量
from
(
select
Inspect_name,
convert(varchar(10),intime,120) as intime,
1 as 记录数
from
tb
group by
Inspect_name,
convert(varchar(10),intime,120)
where
intime between '2009-01-01' and '2009-01-06'
having
sum(vtime)>120
) t
group by Inspect_name
) tt
create table tb(Inspect_name varchar(10), intime datetime, vtime int)
insert into tb values('王' , '2009-1-4 0:33:52' , 60)
insert into tb values('王' , '2009-1-4 0:33:52' , 65)
insert into tb values('王' , '2009-1-5 12:32:50', 100)
insert into tb values('王' , '2009-1-5 12:32:50', 120)
go
select Inspect_name , sum(记录数) 记录数 from
(
select Inspect_name ,记录数 = 1
from tb
where intime between '2009-01-01' and '2009-01-06'
group by Inspect_name ,convert(varchar(10),intime,120)
having sum(vtime) > 120
) t
group by Inspect_name
drop table tb
/*
Inspect_name 记录数
------------ -----------
王 2
(所影响的行数为 1 行)
*/
select Inspect_name,sum(记录数) as 记录数
from
(
select
Inspect_name,
convert(varchar(10),intime,120) as intime,
1 as 记录数
from
tb
group by
Inspect_name,
convert(varchar(10),intime,120)
where
intime between '2009-01-01' and '2009-01-06'
having
sum(vtime)>120
) t
group by Inspect_name
select Inspect_name,sum(记录数) as 记录数
from
(
select
Inspect_name,
convert(varchar(10),intime,120) as intime,
1 as 记录数
from
tb
group by
Inspect_name,
convert(varchar(10),intime,120)
having
sum(vtime)>120
) t
group by Inspect_name
create table tb(Inspect_name varchar(10), intime datetime, vtime int)
insert into tb values('王' , '2009-1-4 0:33:52' , 60)
insert into tb values('王' , '2009-1-4 0:33:52' , 65)
insert into tb values('王' , '2009-1-5 12:32:50', 100)
go
select Inspect_name ,记录数 = 1
from tb
where intime between '2009-01-01' and '2009-01-06'
group by Inspect_name ,convert(varchar(10),intime,120)
having sum(vtime) > 120
drop table tb
/*
Inspect_name 记录数
------------ -----------
王 1
(所影响的行数为 1 行)
*/
---测试数据---
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([Inspect_name] varchar(2),[intime] datetime,[vtime] int,[jcnr] varchar(4))
insert [tb]
select '王','2009-1-4 00:33:52',60,'内容' union all
select '王','2009-1-4 000:33:52',65,'内容' union all
select '王','2009-1-5 12:32:50',100,'内容'
---查询---
select
Inspect_name,
convert(varchar(10),intime,120) as intime,
1 as 记录数
from
tb
group by
Inspect_name,
convert(varchar(10),intime,120)
having
sum(vtime)>=120
---结果---
Inspect_name intime 记录数
------------ ---------- -----------
王 2009-01-04 1
(所影响的行数为 1 行)
create table tb(Inspect_name varchar(10), intime datetime, vtime int)
insert into tb values('王' , '2009-1-4 0:33:52' , 60)
insert into tb values('王' , '2009-1-4 0:33:52' , 65)
insert into tb values('王' , '2009-1-5 12:32:50', 100)
go
select Inspect_name ,convert(varchar(10),intime,120) intime , 记录数 = 1
from tb
where intime between '2009-01-01' and '2009-01-06'
group by Inspect_name ,convert(varchar(10),intime,120)
having sum(vtime) > 120
drop table tb
/*
Inspect_name intime 记录数
------------ ---------- -----------
王 2009-01-04 1
(所影响的行数为 1 行)
*/
select Inspect_name ,convert(varchar(10),intime,120) intime , 记录数 = 1
from tb
where intime between '2009-01-01' and '2009-01-06'
group by Inspect_name ,convert(varchar(10),intime,120)
having sum(vtime) > 120