如何在SQL Server中对单行数据进行计数

问题描述

你好
我有一张桌子，数据就像:

Mon	year	Emp_Id	D1	D2	D3	D4	D5	D6	D7	D8
Aug	2012	E001	NULL	NULL	A	P	P	P	P	NULL

在上表中，它是D1,D2 .....直到D30

所以我想知道不. P从D1到D30的一行.是否可以在单个查询中使用?

请帮帮我.

添加了标签-LOSMAC [/EDIT]

Hello
I have a table and the data is like:

Mon	year	Emp_Id	D1	D2	D3	D4	D5	D6	D7	D8
Aug	2012	E001	NULL	NULL	A	P	P	P	P	NULL

In the above table It is D1,D2.....up to D30

So I want to know no. of P from D1 to D30 of a single row. Is it possible in a single query.

Please, help me.

Tags added - LOSMAC[/EDIT]

推荐答案

select Emp_Id,d1+d2+d3+d4+d5+d6+d7+d8+d9+d10+d11++d12+d13+d14+d15+d16+d17+d18+d19+d20+d21+d22+d23+d24+d25+d26+d27+d28+d29+d30 as [total days] from [tablename]

拥有下表更容易和更好的设计:

Edit :

It is much easier and a better design to have the following table:

EMP_ID, MON, YEAR, DAY

您可以根据某人的存在来输入数据，因此您可以执行以下操作:

Where you enter data based on the presence of a person hence you can do :

select EMP_ID, [MON], [YEAR], Count(DAY) from [tablename] group by Emp_ID,[MON],[YEAR]

SELECT 
Mon
, year
, Emp_Id
, LEN(REPLACE(COALESCE(D1, '') + COALESCE(D2, '') + COALESCE(D3, '') +... +COALESCE(D30, ''),'A',''))  
FROM yourTable

谢谢，

Kuthuparakkal

Thanks,

Kuthuparakkal

这篇关于如何在SQL Server中对单行数据进行计数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！