如何在SQL Server中对单行数据进行计数 [英] How to count data of a single row in sql server
本文介绍了如何在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屋!
查看全文