基于条件的列数
问题描述：
我有一个sql程序来获得员工出勤结果如下，我想在另一列中显示当天的数量
I have a sql procedure to get a Employee Attendance result Like Below, I wan to Show the No of Days Present in another Column
ID | Day1 | Day2 | Day3
1 | p | p | p
2 | p | A | p
3 | p | p | p
4 | p | p | A
5 | p | p | p
想要显示以下结果
Want to show the Below Result
ID | Day1 | Day2 | Day3 | Noof Days Present
1 | p | p | p | 3
2 | p | A | p | 2
3 | p | p | p | 3
4 | p | p | A | 2
5 | p | p | p | 3
答
试试这个...
Try this...
Declare @ColumnName Nvarchar(40) , @Sql Nvarchar(1000),@ColumnCount Int, @I Int
Select @Sql='Select Id,Day1,Day2,Day3, ',@I=0,@ColumnCount=Count(COLUMN_NAME)
From INFORMATION_SCHEMA.COLUMNS
Where TABLE_NAME='Temp' and COLUMN_NAME Like 'Day%'
While @I<@ColumnCount
Begin
Select @ColumnName=COLUMN_NAME From INFORMATION_SCHEMA.COLUMNS
Where TABLE_NAME='Temp'
Order by COLUMN_NAME
OFFSET @I Rows -- If Using Sqlserver 2012 use this else
Fetch Next 1 Rows only; -- use Cursor or while loop with Top(1)
Set @Sql= @Sql +' ' +'Case When '+ @ColumnName +' =''P'' Then 1 Else 0 End +'
Set @I=@I+1
End
Set @Sql = Left(@Sql,len(@Sql)-1) +' as NoofDaysPresent From Temp'
Exec(@Sql)
用 TableName Temp br />
已测试：
Replace Temp with your TableName
Tested:
Create Table Temp (Id Int, Day1 varchar(2),Day2 varchar(2),Day3 varchar(2))
Insert into Temp
Select 1,'P','P','P' Union all
Select 2,'P','A','P' Union all
Select 3,'P','P','P' Union all
Select 4,'P','P','A' Union all
Select 5,'P','P','P'
输出：
Output:
Id Day1 Day2 Day3 NoofDaysPresent
---- ------ ----- ----- ---------------
1 P P P 3
2 P A P 2
3 P P P 3
4 P P A 2
5 P P P 3
如果你想输入e那么你可以使用......
If You Want to type every thing then u can use...
Select Id,ColumnNames,Case When Day1='P' Then 1 Else 0 End+..... as NoofDaysPresent From TableName
编写一个函数GetPresent（ID），它将员工ID与员工在场的天数相加并使用，如下所示： br />
选择*，GetPresent（ID）作为出席时出现的天数
Write a function GetPresent(ID) which takes the employee ID sums the number of days the employee is present and use as below:
select *, GetPresent(ID) as Noof Days Present from Attendance