select
shopid,
empid,
[A班] = sum(case 班别 when 'A班' then 1 else 0 end),
[B班] = sum(case 班别 when 'B班' then 1 else 0 end)
出勤时间 = (sum(case 班别 when 'A班' then 1 else 0 end)*(select 时间 from 表2 where 班别='A班'))
+ (sum(case 班别 when 'B班' then 1 else 0 end)*(select 时间 from 表2 where 班别='B班'))
from
表1
group by
shopid,empid
shopid,
empid,
[A班] = sum(case 班别 when 'A班' then 1 else 0 end),
[B班] = sum(case 班别 when 'B班' then 1 else 0 end)
出勤时间 = (sum(case 班别 when 'A班' then 1 else 0 end)*(select 时间 from 表2 where 班别='A班'))
+ (sum(case 班别 when 'B班' then 1 else 0 end)*(select 时间 from 表2 where 班别='B班'))
from
表1
group by
shopid,empid
create table test(empid varchar(3),[date] datetime,班别 varchar(10),shopid varchar(10))insert into test(empid,[date],班别,shopid)
select '163','2005-07-01','A班','001' union all
select '163','2005-07-02','A班','001' union all
select '163','2005-07-03','B班','001' union all
select '163','2005-07-08','B班','001' union all
select '163','2005-07-09','B班','001' union all
select '163','2005-07-11','B班','001' union all
select '101','2005-07-01','A班','002' union all
select '101','2005-07-02','A班','002' union all
select '101','2005-07-03','A班','002' union all
select '101','2005-07-04','B班','002' union all
select '101','2005-07-05','B班','002' union all
select '101','2005-07-06','B班','002' union all
select '101','2005-07-07','B班','002'
go
create table test2(班别 varchar(10),时间 int)
insert into test2 select 'A班' ,5 union select 'B班',4
go
--测试
select *from test
select *from test2
select shopid,empid,
[A班] = sum(case 班别 when 'A班' then 1 else 0 end),
[B班] = sum(case 班别 when 'B班' then 1 else 0 end),
[出勤时间] = (sum(case 班别 when 'A班' then 1 else 0 end)*(select 时间 from test2 where 班别='A班'))
+ (sum(case 班别 when 'B班' then 1 else 0 end)*(select 时间 from test2 where 班别='B班'))
from test group by shopid,empid
--结果
/*
empid date 班别 shopid
----- ------------------------------------------------------ ---------- ----------
163 2005-07-01 00:00:00.000 A班 001
163 2005-07-02 00:00:00.000 A班 001
163 2005-07-03 00:00:00.000 B班 001
163 2005-07-08 00:00:00.000 B班 001
163 2005-07-09 00:00:00.000 B班 001
163 2005-07-11 00:00:00.000 B班 001
101 2005-07-01 00:00:00.000 A班 002
101 2005-07-02 00:00:00.000 A班 002
101 2005-07-03 00:00:00.000 A班 002
101 2005-07-04 00:00:00.000 B班 002
101 2005-07-05 00:00:00.000 B班 002
101 2005-07-06 00:00:00.000 B班 002
101 2005-07-07 00:00:00.000 B班 002(所影响的行数为 13 行)班别 时间
---------- -----------
A班 5
B班 4(所影响的行数为 2 行)shopid empid A班 B班 出勤时间
---------- ----- ----------- ----------- -----------
002 101 3 4 31
001 163 2 4 26(所影响的行数为 2 行)
*/
create table test(empid varchar(3),[date] datetime,班别 varchar(10),shopid varchar(10))insert into test(empid,[date],班别,shopid)
select '163','2005-07-01','A班','001' union all
select '163','2005-07-02','A班','001' union all
select '163','2005-07-03','B班','001' union all
select '163','2005-07-08','B班','001' union all
select '163','2005-07-09','B班','001' union all
select '163','2005-07-11','B班','001' union all
select '101','2005-07-01','A班','002' union all
select '101','2005-07-02','A班','002' union all
select '101','2005-07-03','A班','002' union all
select '101','2005-07-04','B班','002' union all
select '101','2005-07-05','B班','002' union all
select '101','2005-07-06','B班','002' union all
select '101','2005-07-07','B班','002'
go
create table test2(班别 varchar(10),时间 int)
insert into test2 select 'A班' ,5 union select 'B班',4
go
--测试
select *from test
select *from test2
select shopid,empid,
[A班] = sum(case 班别 when 'A班' then 1 else 0 end),
[B班] = sum(case 班别 when 'B班' then 1 else 0 end),
[出勤时间] = (sum(case 班别 when 'A班' then 1 else 0 end)*(select 时间 from test2 where 班别='A班'))
+ (sum(case 班别 when 'B班' then 1 else 0 end)*(select 时间 from test2 where 班别='B班'))
from test group by shopid,empid order by shopid
--结果
/*
empid date 班别 shopid
----- ------------------------------------------------------ ---------- ----------
163 2005-07-01 00:00:00.000 A班 001
163 2005-07-02 00:00:00.000 A班 001
163 2005-07-03 00:00:00.000 B班 001
163 2005-07-08 00:00:00.000 B班 001
163 2005-07-09 00:00:00.000 B班 001
163 2005-07-11 00:00:00.000 B班 001
101 2005-07-01 00:00:00.000 A班 002
101 2005-07-02 00:00:00.000 A班 002
101 2005-07-03 00:00:00.000 A班 002
101 2005-07-04 00:00:00.000 B班 002
101 2005-07-05 00:00:00.000 B班 002
101 2005-07-06 00:00:00.000 B班 002
101 2005-07-07 00:00:00.000 B班 002(所影响的行数为 13 行)班别 时间
---------- -----------
A班 5
B班 4(所影响的行数为 2 行)shopid empid A班 B班 出勤时间
---------- ----- ----------- ----------- -----------
001 163 2 4 26
002 101 3 4 31(所影响的行数为 2 行)
*/
--删除测试数据
drop table test,test2
position basetime payhour overpay
-------------------------------------------
A 240 8 12
B 248 7 10.5
C 260 6 9
D 270 5 7.5出勤时间<=basetime 工资为出勤时间×payhour
出勤时间>basetime 工资为basetime×payhour+((出勤时间-basetime)×overpay)
怎么算出工资,再谢谢各位大侠