救命啊,哪们帮帮我
/*
查看签到历史情况
*/
CREATE PROCEDURE [dbo].[_ListLog]
@date datetime=null
AS
declare @dayNo int,
@sql varchar(8000),
@i int,
@m int
if @date is null
set @date=getdate()
set @date=dateadd(day,1-day(@date),@date)
set @dayNo=datediff(day,@date,(dateadd(month,1,@date)))
set @i=1
select @dayNo as dayNo
select * into #temp from signin where Datediff(month,TheDate,@date)=0
set @sql='Select username,'
while @i<=@dayNo
begin
set @sql=@sql+' D'+cast(@i as nvarchar)+'=(select UserFlag from #temp B where B.UserID=A.UserID and Datediff(day,'''+cast(@date as nvarchar)+''',TheDate)='+cast(@i-1 as nvarchar)+'),'+char(13)
set @i=@i+1
end
set @sql=@sql+' S0=(select count(*) from #temp B where A.UserID=B.UserID and userFlag=0),'+char(13)
set @sql=@sql+' S1=(select count(*) from #temp B where A.UserID=B.UserID and userFlag=1),'+char(13)
set @sql=@sql+' S2=(select count(*) from #temp B where A.UserID=B.UserID and userFlag=2),'+char(13)
set @sql=@sql+' S3=(select count(*) from #temp B where A.UserID=B.UserID and userFlag=3),'+char(13)
set @sql=@sql+' S4=(select count(*) from #temp B where A.UserID=B.UserID and userFlag=4),'+char(13)
set @sql=@sql+' S5=(select count(*) from #temp B where A.UserID=B.UserID and userFlag=5),'+char(13)
set @sql=@sql+' S6=(select count(*) from #temp B where A.UserID=B.UserID and userFlag=6) ,'+char(13)
set @sql=@sql+' S7=(select count(*) from #temp B where A.UserID=B.UserID and userFlag=7),'+char(13)
set @sql=@sql+' S8=(select count(*) from #temp B where A.UserID=B.UserID and userFlag=8),'+char(13)
set @sql=@sql+' S9=(select count(*) from #temp B where A.UserID=B.UserID and userFlag=9),'+char(13)
set @sql=@sql+' S10=(select count(*) from #temp B where A.UserID=B.UserID and userFlag=10),'+char(13)
set @sql=@sql+'userid from [User] A order by userName,userKind '
--print (@sql)
exec(@sql)
GO
这是显示所有的数据,但我想根据userkind来显示数据
signin表:
CREATE TABLE [dbo].[SignIn] (
[ID] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL ,
[TheDate] [smalldatetime] NULL ,
[UserID] [int] NULL ,
[UserName] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[UserKind] [int] NULL ,
[UserFlag] [int] NULL ,
[SignInTime] [datetime] NULL ,
[Explain] [nvarchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
[userIP] [nvarchar] (15) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO
user表:
CREATE TABLE [dbo].[User] (
[userID] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL ,
[username] [nvarchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,
[userkind] [float] NULL ,
[userpwd] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO