select u_id,[count]=count(1) from tb group by u_id
/*------------------------------------------------------------------ -- Author : htl258(Tony) -- Date : 2010-04-10 16:17:31 -- 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 3)------------------------------------------------------------------*/ --> 生成测试数据表:tbIF OBJECT_ID('[tb]') IS NOT NULL DROP TABLE [tb] GO CREATE TABLE [tb]([id] INT,[U_ID] INT,[P_ID] INT,[price] INT) INSERT [tb] SELECT 1,2,1,10 UNION ALL SELECT 2,2,1,11 UNION ALL SELECT 3,3,1,12 UNION ALL SELECT 4,3,1,44 UNION ALL SELECT 5,5,1,45 UNION ALL SELECT 6,2,1,45 GO --SELECT * FROM [tb]-->SQL查询如下: select u_id,[count]=count(1) from tb group by u_id /* u_id count ----------- ----------- 2 3 3 2 5 1(3 行受影响) */
/*------------------------------------------------------------------
-- Author : htl258(Tony)
-- Date : 2010-04-10 16:17:31
-- 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 3)------------------------------------------------------------------*/
--> 生成测试数据表:tbIF OBJECT_ID('[tb]') IS NOT NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb]([id] INT,[U_ID] INT,[P_ID] INT,[price] INT)
INSERT [tb]
SELECT 1,2,1,10 UNION ALL
SELECT 2,2,1,11 UNION ALL
SELECT 3,3,1,12 UNION ALL
SELECT 4,3,1,44 UNION ALL
SELECT 5,5,1,45 UNION ALL
SELECT 6,2,1,45
GO
--SELECT * FROM [tb]-->SQL查询如下:
select u_id,[count]=count(1) from tb group by u_id
/*
u_id count
----------- -----------
2 3
3 2
5 1(3 行受影响)
*/