数据库中选取某字段出现次数最多的记录

[xsyxt]

大家好，我有个问题想请教大家
比如说数据库中有这样一些记录
..a..
..a..
..b..
..b..
..b..
..c..
..c..
..c..
..c..
其中abc都是某一个字段的值
我想将这里面出现次数最多的记录（这里是c 4次）取出来，请问如何实现？

[--小F--]

select max(num) from (select col,count(1) as num from tb group by col)

[jiangshun]

select top 1 * from (select col,flag=count(1) as num from tb group by col)t
order by flag

[fhjzgsy]

[Quote=引用 1 楼 fredrickhu 的回复:]
SQL codeselectmax(num)from (select col,count(1)as numfrom tbgroupby col)
[/Quote]
没有什么问题为什么
消息 102，级别 15，状态 1，第 1 行
')' 附近有语法错误。

[SQL77]

select max(num) from (select col,count(1) as num from tb group by col)AS T

[--小F--]

[Quote=引用 3 楼 fhjzgsy 的回复:]
引用 1 楼 fredrickhu 的回复:
SQL codeselectmax(num)from (select col,count(1)as numfrom tbgroupby col)

没有什么问题为什么
消息 102，级别 15，状态 1，第 1 行
')' 附近有语法错误。
[/Quote]

不好意思 写掉了个别名

select max(num) from (select col,count(1) as num from tb group by col)t

[叶子]

declare @table table (CName varchar(5))
insert into @table
select '..a..' union all
select '..a..' union all
select '..b..' union all
select '..b..' union all
select '..b..' union all
select '..c..' union all
select '..c..' union all
select '..c..' union all
select '..c..'

select top 1 CName,COUNT(CName) as CnameCount from @table 
group by CName order by COUNT(CName) desc

/*
CName CnameCount
----- -----------
..c.. 4
*/

[ChinaJiaBing]

declare @tb table (px nvarchar(10))
insert into @tb select 'a'
      union all select 'a'
      union all select 'b'
      union all select 'b'
      union all select 'b'
      union all select 'c'
      union all select 'c'
      union all select 'c'
      union all select 'c'
select max(次数) from 
(select px, count(*) 次数 from @tb group by px) tb


----------
4

[cxmcxm]

用聚合统计

[xsyxt]

count(1)是什么意思啊

[xsyxt]

[Quote=引用 5 楼 fredrickhu 的回复:]
引用 3 楼 fhjzgsy 的回复:
引用 1 楼 fredrickhu 的回复:
SQL codeselectmax(num)from (select col,count(1)as numfrom tbgroupby col)

没有什么问题为什么
消息 102，级别 15，状态 1，第 1 行
')' 附近有语法错误。


不好意思 写掉了个别名
SQL codeselectmax(num)from (select col,count(1)as numfrom tbgroupby col)t

[/Quote]末尾加个t是什么意思啊

[ycagri]

第一个索引吧，并不是指第一列

[阿_布]

count(1)跟count(*)是一样的,就是记录数。

[xsyxt]

还有一个问题，这些得到的数据怎么导入到程序里面呢？

[xiuxiu6]

程序里连接数据库，然后用上面的语句查询

[xsyxt]

[Quote=引用 14 楼 xiuxiu6 的回复:]
程序里连接数据库，然后用上面的语句查询
[/Quote]
查询了至少有返回结果啊，放到哪儿呢？

[yjh53615728]

你用的是什么语言？C#？

[wxjdlut]

CREATE TABLE t(c CHAR);
INSERT INTO t VALUES('a');
INSERT INTO t VALUES('a');
INSERT INTO t VALUES('b');
INSERT INTO t VALUES('b');
INSERT INTO t VALUES('b');
INSERT INTO t VALUES('c');
INSERT INTO t VALUES('c');
INSERT INTO t VALUES('c');
INSERT INTO t VALUES('c');

SELECT * FRON t WHERE c =
(SELECT t1.c FROM
(SELECT c, count(c) AS num FROM t) AS t1
WHERE t1.num = (SELECT max(num) FROM t1));

[Rotel-刘志东]

select max(num) from (select col,count(1) as num from tb group by col)t

[aaajedll]

IF(OBJECT_ID('TEMPDB..#T') IS NOT NULL)
BEGIN
DROP TABLE #T
END
CREATE TABLE #T
(
	[CODE] NVARCHAR(100)
)
INSERT INTO #T SELECT [CODE]='..a..'
INSERT INTO #T SELECT [CODE]='..a..'
INSERT INTO #T SELECT [CODE]='..b..'
INSERT INTO #T SELECT [CODE]='..b..'
INSERT INTO #T SELECT [CODE]='..b..'
INSERT INTO #T SELECT [CODE]='..c..'
INSERT INTO #T SELECT [CODE]='..c..'
INSERT INTO #T SELECT [CODE]='..c..'
INSERT INTO #T SELECT [CODE]='..c..'



SELECT TOP 1 [CODE],COUNT(*) AS [COUNT] FROM #T GROUP BY [CODE] ORDER BY [COUNT] DESC


----------------------------
CODE   COUNT
..c..	4