背景:公司餐厅就餐纪录每天可以从考勤机上面直接抓去下来,怎么查询每天所有就餐厅人数中刷卡纪录大于等于4的纪录,其中每个人的工号,卡号是唯一的?select 工号(或卡号), convert(varchar(10),时间字段,120) 日期,count(*) 次数 from tb group by 工号(或卡号), convert(varchar(10),时间字段,120) having count(*) >= 4
select 工号 from table1 group by 工号 having count(1) >= 4
--insert into strecords --select * FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0', --'Data Source="c:\jc526613.xls";Extended properties=Excel 5.0')...[jc526613$] -- from v_stdatas where d_card='20060926' and t_card>='000000' -- group by emp_type order by emp_type --TO 行政部 的刷卡明细列表 select * from v_stdatas where D_CARD>='20070920' and d_card<='20070920' order by d_card --go --select * from strecords where d_card>='20070426' and t_card>='000000'---------------------------------------------------------------------------- ----------------------------2006.11.3刷卡机调换说明------------------------------------------ ------2006.11.3日上午因为刷卡机10与15相互调换使用,相应功能作了调整------------------------------ ------原四楼10号刷卡机调到门禁处作为出入功能 ------因此数据计算当中相关的参数需要更改,并且在11.3日之前与11.3日之后统计参数需要人工作调整------- ------否则统计结果有误!!!--------------------------------------------------------------------- use [kms-st] go begin declare @crq as varchar(8),@crq2 as varchar(8) set @crq='20070920' set @crq2='20070920' declare @dt11 as varchar(6),@dt12 as varchar(6),@dt21 as varchar(6),@dt22 as varchar(6),@dt31 as varchar(6),@dt32 as varchar(6) declare @dt41 as varchar(6),@dt42 as varchar(6),@dt51 as varchar(6),@dt52 as varchar(6),@dt61 as varchar(6),@dt62 as varchar(6) declare @dt71 as varchar(6),@dt72 as varchar(6),@dt81 as varchar(6),@dt82 as varchar(6),@dt91 as varchar(6),@dt92 as varchar(6) select @dt11='050000',@dt12='075099',@dt21='113000',@dt22='130099',@dt31='170000',@dt32='190000' select @dt41='233000',@dt42='240000',@dt51='000000',@dt52='003099',@dt61='003100',@dt62='045959' select @dt71='075100',@dt72='112959',@dt81='130100',@dt82='165959',@dt91='190100',@dt92='232959'---- select * from v_stdatas where emp_type is null AND D_CARD=@crq select top 5 d_card from v_stdatas group by d_card order by d_card desc --统计各餐人数 --早餐******************** select '栏位'=@crq+'早餐刷卡数',emp_type,'人数'=count(*) from v_stdatas where d_card=@crq and t_card>=@dt11 and t_card<=@dt12 group by emp_type order by emp_type--中餐******************** 从2006年11月3日起四楼食堂刷卡机参数变改为15号刷卡机 select '栏位'=@crq+'中餐:四楼刷卡数',emp_type,'人数'=count(*) from v_stdatas where d_card=@crq and t_card>=@dt21 and t_card<=@dt22 and node_no='15' group by emp_type order by emp_typeselect '栏位'=@crq+'中餐:一楼刷卡数',emp_type,'人数'=count(*) from v_stdatas where d_card=@crq and t_card>=@dt21 and t_card<=@dt22 and node_no='11' group by emp_type order by emp_type------- --晚餐********************从2007年09月1日起晚上就餐时间由:143000--163000,调整为:170000--190000. select '栏位'=@crq+'晚餐刷卡数',emp_type,'人数'=count(*) from v_stdatas where d_card=@crq and t_card>=@dt31 and t_card<=@dt32 group by emp_type order by emp_type--夜宵**************************************************************************************************** select '栏位'=@crq+'夜宵刷卡数',emp_type,'人数'=count(*) from v_stdatas where (d_card=@crq and t_card>=@dt41 and t_card<=@dt42 ) or (d_card=@crq and t_card>=@dt51 and t_card<=@dt52 ) group by emp_type order by emp_type-------------------------------------------- --没有在规定时间内刷卡人员********************************************************************************** select '栏位'=@crq+'没有在规定时间刷卡数',emp_type,'人数'=count(*) from v_stdatas where (d_card=@crq and t_card>=@dt61 and t_card<=@dt62 ) or (d_card=@crq and t_card>=@dt71 and t_card<=@dt72 ) or (d_card=@crq and t_card>=@dt81 and t_card<=@dt82 ) or (d_card=@crq and t_card>=@dt91 and t_card<=@dt92 ) group by emp_type order by emp_type--------------------------------------- --将次日凌晨的刷卡数据作为前一天的晚餐数 select @crq+'总刷卡数',count(*) from v_stdatas where (d_card=@crq and t_card>=@dt11 and t_card<@dt42) or (d_card=@crq2 and t_card>=@dt51 and t_card<=@dt52) end
select 员工号,卡号, convert(varchar(10),时间字段,112) as 刷卡日期,count(*) as 刷卡次数 from tb group by 员工号,卡号convert(varchar(10),时间字段,112) having count(*) >= 4
select 员工号,卡号, convert(varchar(10),时间字段,112) as 刷卡日期,count(*) as 刷卡次数 from tb group by 员工号,卡号,convert(varchar(10),时间字段,112) having count(*) >= 4
select work_no, convert(varchar(10),20070801,112) as 刷卡日期,count(*) as 刷卡次数 from v_stdatas group by work_no convert(varchar(10),20070801,112) having count(*) >= 4我运行上面语句出现错误说:服务器: 消息 156,级别 15,状态 1,行 3 在关键字 'convert' 附近有语法错误。 请指教,我的意思是想查出具体哪一天刷卡大于等于4的纪录
select card_no, convert(varchar(10),20070801,120) 日期,count(*) 次数 from v_stdatas group by card_no, convert(varchar(10),20070801,120) having count(*) >= 4错误提示: 服务器: 消息 164,级别 15,状态 1,行 4 GROUP BY 表达式必须引用出现在选择列表中的列名。 什么问题啊》?
convert(varchar(10),20070801,120) 换成'日期'
问题解决,马上结贴,正确语句如下select card_no,count(card_no) record_count from v_stdatas where d_card='20070911' --you can set this variable through D_CARD group by card_no having count(*)>=4
from tb
group by 工号(或卡号), convert(varchar(10),时间字段,120)
having count(*) >= 4
--select * FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
--'Data Source="c:\jc526613.xls";Extended properties=Excel 5.0')...[jc526613$]
-- from v_stdatas where d_card='20060926' and t_card>='000000'
-- group by emp_type order by emp_type
--TO 行政部 的刷卡明细列表
select * from v_stdatas where D_CARD>='20070920' and d_card<='20070920'
order by d_card
--go
--select * from strecords where d_card>='20070426' and t_card>='000000'----------------------------------------------------------------------------
----------------------------2006.11.3刷卡机调换说明------------------------------------------
------2006.11.3日上午因为刷卡机10与15相互调换使用,相应功能作了调整------------------------------
------原四楼10号刷卡机调到门禁处作为出入功能
------因此数据计算当中相关的参数需要更改,并且在11.3日之前与11.3日之后统计参数需要人工作调整-------
------否则统计结果有误!!!---------------------------------------------------------------------
use [kms-st]
go
begin
declare @crq as varchar(8),@crq2 as varchar(8)
set @crq='20070920'
set @crq2='20070920'
declare @dt11 as varchar(6),@dt12 as varchar(6),@dt21 as varchar(6),@dt22 as varchar(6),@dt31 as varchar(6),@dt32 as varchar(6)
declare @dt41 as varchar(6),@dt42 as varchar(6),@dt51 as varchar(6),@dt52 as varchar(6),@dt61 as varchar(6),@dt62 as varchar(6)
declare @dt71 as varchar(6),@dt72 as varchar(6),@dt81 as varchar(6),@dt82 as varchar(6),@dt91 as varchar(6),@dt92 as varchar(6)
select @dt11='050000',@dt12='075099',@dt21='113000',@dt22='130099',@dt31='170000',@dt32='190000'
select @dt41='233000',@dt42='240000',@dt51='000000',@dt52='003099',@dt61='003100',@dt62='045959'
select @dt71='075100',@dt72='112959',@dt81='130100',@dt82='165959',@dt91='190100',@dt92='232959'----
select * from v_stdatas where emp_type is null AND D_CARD=@crq
select top 5 d_card from v_stdatas group by d_card order by d_card desc
--统计各餐人数
--早餐********************
select '栏位'=@crq+'早餐刷卡数',emp_type,'人数'=count(*)
from v_stdatas where d_card=@crq and t_card>=@dt11 and t_card<=@dt12
group by emp_type order by emp_type--中餐******************** 从2006年11月3日起四楼食堂刷卡机参数变改为15号刷卡机
select '栏位'=@crq+'中餐:四楼刷卡数',emp_type,'人数'=count(*)
from v_stdatas where d_card=@crq and t_card>=@dt21 and t_card<=@dt22 and node_no='15'
group by emp_type order by emp_typeselect '栏位'=@crq+'中餐:一楼刷卡数',emp_type,'人数'=count(*)
from v_stdatas where d_card=@crq and t_card>=@dt21 and t_card<=@dt22 and node_no='11'
group by emp_type order by emp_type-------
--晚餐********************从2007年09月1日起晚上就餐时间由:143000--163000,调整为:170000--190000.
select '栏位'=@crq+'晚餐刷卡数',emp_type,'人数'=count(*)
from v_stdatas where d_card=@crq and t_card>=@dt31 and t_card<=@dt32
group by emp_type order by emp_type--夜宵****************************************************************************************************
select '栏位'=@crq+'夜宵刷卡数',emp_type,'人数'=count(*) from v_stdatas
where (d_card=@crq and t_card>=@dt41 and t_card<=@dt42 )
or (d_card=@crq and t_card>=@dt51 and t_card<=@dt52 )
group by emp_type order by emp_type--------------------------------------------
--没有在规定时间内刷卡人员**********************************************************************************
select '栏位'=@crq+'没有在规定时间刷卡数',emp_type,'人数'=count(*) from v_stdatas
where (d_card=@crq and t_card>=@dt61 and t_card<=@dt62 )
or (d_card=@crq and t_card>=@dt71 and t_card<=@dt72 )
or (d_card=@crq and t_card>=@dt81 and t_card<=@dt82 )
or (d_card=@crq and t_card>=@dt91 and t_card<=@dt92 )
group by emp_type order by emp_type---------------------------------------
--将次日凌晨的刷卡数据作为前一天的晚餐数
select @crq+'总刷卡数',count(*)
from v_stdatas
where (d_card=@crq and t_card>=@dt11 and t_card<@dt42)
or (d_card=@crq2 and t_card>=@dt51 and t_card<=@dt52)
end
from tb
group by 员工号,卡号convert(varchar(10),时间字段,112)
having count(*) >= 4
from tb
group by 员工号,卡号,convert(varchar(10),时间字段,112)
having count(*) >= 4
from v_stdatas
group by work_no convert(varchar(10),20070801,112)
having count(*) >= 4我运行上面语句出现错误说:服务器: 消息 156,级别 15,状态 1,行 3
在关键字 'convert' 附近有语法错误。
请指教,我的意思是想查出具体哪一天刷卡大于等于4的纪录
from v_stdatas
group by card_no, convert(varchar(10),20070801,120)
having count(*) >= 4错误提示:
服务器: 消息 164,级别 15,状态 1,行 4
GROUP BY 表达式必须引用出现在选择列表中的列名。
什么问题啊》?
where d_card='20070911' --you can set this variable through D_CARD
group by card_no having count(*)>=4