请高手给出查询语句

Question

sql

Answer 1

剩下的就是 化行为列 的工作了：
declare @t table (DepartID varchar(50),
EmployeeID varchar(50),
EmployeeName varchar(50),
CheckDate smalldatetime,
CheckTime smalldatetime,
CheckDateTime         datetime,
eatMoney    decimal(10, 2),
Toll_MachineID varchar(50))insert @t
select 'a001002004001', '87207024', '季支付', '2014-04-09 00:00:00.000', '17:23:08', '2014-04-09 17:23:08.000', '5.00', '008'
union all select 'a002010002001', '88109976', '高爱萍', '2014-04-09 00:00:00.000', '17:54:14', '2014-04-09 17:54:14.000', '1.70', '002'
union all select 'a002010002001', '88109976', '高爱萍', '2014-04-09 00:00:00.000', '18:32:14', '2014-04-09 18:32:14.000', '20.70', '002'
union all select 'a003006001001', 'S-PROCU-03', '张维冰', '2014-04-09 00:00:00.000', '10:54:19','2014-04-09 10:54:19.000', '1.70','004'
union all select 'a002015001001', '88100340', '傅晓芬', '2014-04-09 00:00:00.000', '10:54:21', '2014-04-09 10:54:21.000', '5.00', '011'
union all select 'a002010002001', '88109968', '廖宏菊', '2014-04-09 00:00:00.000', '10:54:27', '2014-04-09 10:54:27.000', '2.40', '002'
union all select 'a001002005001', '87207008', '李芸', '2014-04-09 00:00:00.000', '10:54:38', '2014-04-09 10:54:38.000', '1.50', '008'
union all select 'a002010002001', '88109979', '董华勤', '2014-04-09 00:00:00.000', '10:54:38', '2014-04-09 10:54:38.000', '2.70', '002'
union all select 'a002018001001', '88100174', '刘辉', '2014-04-09 00:00:00.000', '10:54:43', '2014-04-09 10:54:43.000', '7.50', '004'
union all select 'a003006001001', 'S-PROCU-03', '张维冰', '2014-04-09 00:00:00.000', '18:54:52', '2014-04-09 18:54:52.000', '3.50','008'
union all select 'a002010002001', '88109976', '高爱萍', '2014-04-10 00:00:00.000', '17:04:24', '2014-04-10 17:04:14.000', '1.70', '002'
select * from @t
select a.EmployeeName,a.CheckDate --,fc3,fc2,fc1
,isnull(fc3,0)+isnull(fc2,0)+case when fc1>0 then 1 else 0 end fc
from (
select distinct EmployeeName,CheckDate
from @t
) a
left join (
select EmployeeName,CheckDate,count(*)*2 fc3
from @t
where eatMoney>=18
group by EmployeeName,CheckDate
) b on a.EmployeeName=b.EmployeeName and a.CheckDate=b.CheckDate
left join (
select EmployeeName,CheckDate,count(*)*1 fc2
from @t
where eatMoney<18 and eatMoney>=6
group by EmployeeName,CheckDate
) c on a.EmployeeName=c.EmployeeName and a.CheckDate=c.CheckDate
left join (
select EmployeeName,CheckDate,count(*) fc1
from @t
where eatMoney<6
group by EmployeeName,CheckDate
) d on a.EmployeeName=d.EmployeeName and a.CheckDate=d.CheckDate