我有以下源表:
pid | code
----------
1 P2
2 P2
3 P3
4 P1
5 P2
6 P1
现在,我想得到每个代码存在多少次的信息:
code | count
------------
P2 3
P1 2
P3 1
因此,我想计算代码列中的值,并将其分配给一组不同的可用代码值。最后我想按点号订购。
SQL Fiddle:
SELECT t.code, COUNT(*) AS `count`
FROM MyTable t
GROUP BY t.code
ORDER BY COUNT(*) DESC
DECLARE @testTable table (pid int
,code varchar(2)
)
insert into @testTable values (1, 'P2')
insert into @testTable values (2, 'P2')
insert into @testTable values (3, 'P3')
insert into @testTable values (4, 'P1')
insert into @testTable values (5, 'P2')
insert into @testTable values (6, 'P1')
SELECT CODE, COUNT(1) AS [COUNT]
FROM @testTable
GROUP BY CODE
ORDER BY [COUNT] DESC