SQL组By / Count:跨多列计数相同的值? [英] SQL Group By / Count: Count Same Values Across Multiple Columns?
问题描述
我试图弄清楚如何编写一个查询来计算跨多个列的值,并且结果表中每个列的每个可能值都包含任意列的每个可能值。
示例:说我有 mytable
来源数据表:
P1 P2 P3
-----------
aba
aaa
bbb
abb
我想要一个查询来计算每列中的a和b,生成如下所示的内容:
所需查询输出:
P1 P2 P3
-------------
a | 3 1 2
b | 1 3 2
我知道我可以通过 : 选择P1,将count(*)作为mycounts
从mytable
group by P1
但是可以在 / em>列?
我正在使用SQL Server 2008(T-SQL)。
也许是这样的:
<首先一些测试数据:
$ b $ pre $
DECLARE @tbl TABLE(P1 VARCHAR,P2 VARCHAR,P3 VARCHAR)
INSERT INTO @tbl
SELECT'a','b','a'UNION ALL
SELECT'a','a','a'UNION ALL
SELECT'b ','b','b'UNION ALL
SELECT'a','b','b'
然后,像这样的一个枢轴:
SELECT
*
FROM
(
SELECT'P1'AS P,P1 AS PValue,P1 AS test FROM @tbl
UNION ALL
SELECT'P2',P2,P2 FROM @tbl
UNION ALL
SELECT'P3',P3,P3 FROM @tbl
)AS p
PIVOT
(
COUNT(PValue)
FOR P IN([P1 ],[P2],[P3])
)AS pvt
这里是关于pivot和un的更多信息枢轴
I'm trying to figure out how to write a query that counts values across multiple columns, with the result table having a count in each column for every possible value of any column.
Example: Say I have mytable
Source data table:
P1 P2 P3
-----------
a b a
a a a
b b b
a b b
I want a query that counts a's and b's in each column, producing something like:
Desired query output:
P1 P2 P3
-------------
a | 3 1 2
b | 1 3 2
I know I can do this for a single column easily with a group by:
select P1, count(*) as mycounts
from mytable
group by P1
But is it possible to do this for every column?
I'm using SQL Server 2008 (T-SQL). Thanks in advance for any help!
Maybe something like this:
First some test data:
DECLARE @tbl TABLE(P1 VARCHAR,P2 VARCHAR,P3 VARCHAR)
INSERT INTO @tbl
SELECT 'a','b','a' UNION ALL
SELECT 'a','a','a' UNION ALL
SELECT 'b','b','b' UNION ALL
SELECT 'a','b','b'
Then a pivot like this:
SELECT
*
FROM
(
SELECT 'P1' AS P, P1 AS PValue,P1 AS test FROM @tbl
UNION ALL
SELECT 'P2',P2,P2 FROM @tbl
UNION ALL
SELECT 'P3',P3,P3 FROM @tbl
) AS p
PIVOT
(
COUNT(PValue)
FOR P IN ([P1],[P2],[P3])
) AS pvt
Here is more information about pivot and unpivot
这篇关于SQL组By / Count:跨多列计数相同的值?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!