在SQL中计算列值 [英] Counting column values ​​in 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:

EmpId	Number	IsSelected
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:

EmpId	01	02	03	04	05	...	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[^]

这篇关于在SQL中计算列值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！