本文介绍了在SQL中计算列值的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我打算如何通过SQL查询计数字符"X"?我计划将行转换为列,但查询失败.请帮我.感谢所有
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
推荐答案
Let''s say, you have a table name MyData like this: EmpID VARCHAR(3), Number VARCHAR(3), IsSelected VARCHAR(3)
It stores the values:
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)
有关更多信息:使用透视和Upivot [ ^ ]
如何获得总计?在类似的讨论中(示例)查看我的答案:
在MS SQL中找到3个项目关联规则 [ ^ ]
行数在sql中更改 [ ^ ]
计算问题 [ ^ ]
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[^]
我有一个问题,我有一张桌子 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 |
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 |
我认为您不应该将行更改为列...我从您的问题中得出的估计是: 你有(应该有) 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) 它存储值:
001 | 01 | x |
001 | 02 | x |
001 | 03 | x |
001 | 04 | x |
001 | 05 | x |
... | ||
001 | 31 | x |
001 | 01 | x |
001 | 02 | x |
001 | 03 | x |
001 | 04 | x |
001 | 05 | x |
... | ||
001 | 31 | x |
结果集:
001 | 1 | 1 | 1 | 1 | 1 | ... | 1 |
Resultset:
001 | 1 | 1 | 1 | 1 | 1 | ... | 1 |
更多推荐
在SQL中计算列值
发布评论