在SQL中计算列值

本文介绍了在SQL中计算列值的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我有一个问题，我有一张桌子 ImpID 001 编号01 X 编号02 X NumBer03 X Number04 X ........ 31号X 我是负荷报告:

EmpID	01	02	03	04	....	31	SUM
001	X	X	X	X	....	X	31

我打算如何通过SQL查询计数字符"X"?我计划将行转换为列，但查询失败.请帮我.感谢所有

I have a problem,I have a table ImpID 001 Number01 X Number02 X NumBer03 X Number04 X ........ Number31 X I were Load Report:

EmpID	01	02	03	04	....	31	SUM
001	X	X	X	X	....	X	31

How I can query by SQL for count char ''X''?I plan to convert rows into columns, but I query fails. Please help me. Thanks all

推荐答案

我认为您不应该将行更改为列...我从您的问题中得出的估计是: 你有(应该有) a.)具有字段的表EMP EMP.EMPID EMP.ANYTHING_OTHER EMP.THIS_AN_THAT b.)带有字段的表EMP_DTL EMP_DTL.EMPID EMP_DTL.EMP_DTL_POS EMP_DTL.NUMBER 如果这个假设正确，那么您可以轻松解决您的要求. I think you should not change rows to columns...What I estimate from your question is: You have (should have) a.) a table EMP with fields EMP.EMPID EMP.ANYTHING_OTHER EMP.THIS_AN_THAT b.) a table EMP_DTL with fields EMP_DTL.EMPID EMP_DTL.EMP_DTL_POS EMP_DTL.NUMBER If this assumption is right you can easily solve your request. SELECT COUNT(*) FROM EMP_DTL WHERE EMP_DTL.EMPID = '001' AND EMP_DTL.NUMBER = 'X'

或更好的参数来计算任何Imp(参数:EMPID)的"X"或"Y"(查询参数:NUMBER)等

or better with parameters to count the "X" or the "Y" (query parameter :NUMBER) etc. for any Imp (parameter :EMPID)

SELECT COUNT(*) FROM EMP_DTL WHERE EMP_DTL.EMPID = :EMPID AND EMP_DTL.NUMBER = :NUMBER

像这样，您可以使用GROUP BY，拥有更多的可能性... 希望我估计正确.问候.

And like this you much more possibilities using GROUP BY, HAVING... Hope I estimated right. Regards.

让我们说，您有一个表名MyData像这样:EmpID VARCHAR(3)，Number VARCHAR(3)，IsSelected VARCHAR(3) 它存储值: EmpId Number IsSelected

001	01	x
001	02	x
001	03	x
001	04	x
001	05	x
...
001	31	x

Let''s say, you have a table name MyData like this: EmpID VARCHAR(3), Number VARCHAR(3), IsSelected VARCHAR(3) It stores the values: EmpIdNumberIsSelected

001	01	x
001	02	x
001	03	x
001	04	x
001	05	x
...
001	31	x

DECLARE @sqry NVARCHAR(2000) DECLARE @dtqry NVARCHAR(2000) DECLARE @pvqry NVARCHAR(2000) DECLARE @cols NVARCHAR(2000) --select all Numbers to use it as dynamic columns SET @cols = STUFF(( SELECT DISTINCT '],[' + [Number] FROM [dbo].[Mydata] ORDER BY '],[' + [Number] FOR XML PATH('')), 1, 2, '') + ']' --count numbers for each empid SET @dtqry = 'SELECT [EmpId], [Number] , COUNT([IsSelected]) AS [CIS] ' + 'FROM [dbo].[MyData] ' + 'GROUP BY [EmpId]' EXECUTE (@dtqry) --pivot empid for number SET @ptqry = 'SELECT [EmpId], ' + @cols + ' ' + 'FROM (' + @dtqry + ') AS DT ' + 'PIVOT (SUM(DT.[CIS]) FOR DT.[Number] IN (' + @cols + ')) AS PT ' + 'ORDER BY PT.[EmpId]' EXECUTE (@ptqry)

结果集:

EmpId 01 02 03 04 05 ... 31

001	1	1	1	1	1	...	1

有关更多信息:使用透视和Upivot [ ^ ] 如何获得总计?在类似的讨论中(示例)查看我的答案: 在MS SQL中找到3个项目关联规则 [ ^ ] 行数在sql中更改 [ ^ ] 计算问题 [ ^ ]

Resultset:

EmpId0102030405...31

001	1	1	1	1	1	...	1

More about: Using Pivot and Upivot[^] How to get total? See my answers in similar discussions (examples): Find 3 item association rules in MS SQL[^] rows to columns change in sql[^] Problem in calculation[^]