select NumName , count(1) from DataTb group by NumName
create table DataTb(Number int,NumName varchar(10)) insert into DataTb values(1 ,'a') insert into DataTb values(2 ,'b') insert into DataTb values(3 ,'b') insert into DataTb values(4 ,'a') insert into DataTb values(5 ,'c') goselect NumName , count(1) 重复个数 from DataTb group by NumNamedrop table DataTb/* NumName 重复个数 ---------- ----------- a 2 b 2 c 1(所影响的行数为 3 行) */
select sum(case NumName when 'a' then 1 else 0 end) as a的个数, sum(case NumName when 'b' then 1 else 0 end) as b的个数, sum(case NumName when 'c' then 1 else 0 end) as c的个数 from DataTb /* a的个数 b的个数 c的个数 ----------- ----------- ----------- 2 2 1(1 row(s) affected) */
---------------------------------------------------------------------------------- -- Author : htl258(Tony) -- Date : 2010-05-11 00:16:46 -- Version: Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86) -- Jul 9 2008 14:43:34 -- Copyright (c) 1988-2008 Microsoft Corporation -- Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 2) -- Blog : http://blog.csdn.net/htl258 ------------------------------------------------------------------------------------> 生成测试数据表: [tb] IF OBJECT_ID('[tb]') IS NOT NULL DROP TABLE [tb] GO CREATE TABLE [tb] ([Number] [int],[NumName] [nvarchar](10)) INSERT INTO [tb] SELECT '1','a' UNION ALL SELECT '2','b' UNION ALL SELECT '3','b' UNION ALL SELECT '4','a' UNION ALL SELECT '5','c'--SELECT * FROM [tb]-->SQL查询如下: select * from tb pivot(count(Number) for numname in(a,b,c)) b /* a b c ----------- ----------- ----------- 2 2 1(1 行受影响) */
insert into DataTb values(1 ,'a')
insert into DataTb values(2 ,'b')
insert into DataTb values(3 ,'b')
insert into DataTb values(4 ,'a')
insert into DataTb values(5 ,'c')
goselect NumName , count(1) 重复个数 from DataTb group by NumNamedrop table DataTb/*
NumName 重复个数
---------- -----------
a 2
b 2
c 1(所影响的行数为 3 行)
*/
sum(case NumName when 'a' then 1 else 0 end) as a的个数,
sum(case NumName when 'b' then 1 else 0 end) as b的个数,
sum(case NumName when 'c' then 1 else 0 end) as c的个数
from DataTb
/*
a的个数 b的个数 c的个数
----------- ----------- -----------
2 2 1(1 row(s) affected)
*/
-- Author : htl258(Tony)
-- Date : 2010-05-11 00:16:46
-- Version: Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
-- Jul 9 2008 14:43:34
-- Copyright (c) 1988-2008 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 2)
-- Blog : http://blog.csdn.net/htl258
------------------------------------------------------------------------------------> 生成测试数据表: [tb]
IF OBJECT_ID('[tb]') IS NOT NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb] ([Number] [int],[NumName] [nvarchar](10))
INSERT INTO [tb]
SELECT '1','a' UNION ALL
SELECT '2','b' UNION ALL
SELECT '3','b' UNION ALL
SELECT '4','a' UNION ALL
SELECT '5','c'--SELECT * FROM [tb]-->SQL查询如下:
select * from tb pivot(count(Number) for numname in(a,b,c)) b
/*
a b c
----------- ----------- -----------
2 2 1(1 行受影响)
*/
tony 哥的回答总是如此精准!顶...