如何在SQL Server中对单行数据进行计数 [英] How to count data of a single row in 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]

推荐答案

执行以下操作:

Do the following :

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]

您必须使用数据透视.
检查这篇文章.同类表结构

在SQL Server 2005中透视两个或更多列 [ ^ ]

You have to use pivot.
Check this article. Similar kind of table structure

Pivot two or more columns in SQL Server 2005[^]

这是一个棘手的解决方案，我们检查是否有效(即等于P)，然后连接字符串，而NULL使用空字符串.然后，我们将所有的"A"替换为空字符串.

假设您有5个礼物，您将获得PPPPP，现在您要花费这个长度，您将获得多少个P，因为它们都是单个字符.

This is a tricky solution, we check to see if valid (ie equals P) then Concatenate string and wehen NULL use an empty string. And we replace any ''A'' comin in to empty string.

Suppose you have 5 Presents, you get PPPPP, now you take length of the this, you get how many P''s as they all are single character.

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

谢谢，

Kuthuparakkal

Thanks,

Kuthuparakkal

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