MSSQL数据库中，一张表有A,B,C三列，怎么求出数据最多的一列，谢谢
0
所有回答(5)
0
数据最多？不明白
比如说A列有10条数据，B列有8条数据，C列有9条数据，A,B,C三列都可以为null
@小小一书童: select count(A) from tablenam where A is not null
select count(B) from tablenam where B is not null
select count(C) from tablenam where C is not null
0
1、count三次，然后比较。
2、行转列，然后分组就count
0
DECLARE @T TABLE(
COL1 INT
,COL2 INT
,COL3 INT
)
INSERT INTO @T
SELECT 1,NULL,NULL UNION ALL
SELECT NULL,2,NULL UNION ALL
SELECT 3,NULL,4
SELECT COUNT(COL1),COUNT(COL2),COUNT(COL3)
FROM @T
--2 1 1
0
CREATE TABLE AText ( a char(3), b varchar(5), c int ) insert into AText values('v','a',null) insert into AText values(null,'a',2) insert into AText values('v',null,1) insert into AText values('v',null,null) insert into AText values('','a',2) insert into AText values('v',null,1) with ta as (select COUNT(a) as a from AText union all select COUNT(b) as a from AText union all select COUNT(c) as a from AText ) select max(a)from ta
这个应该是你想要的吧
0
模拟了一个
建表：
CREATE TABLE TAB ( COL1 INT ,COL2 INT ,COL3 INT ) INSERT INTO TAB(COL1,COL2,COL3) VALUES(1,NULL,3); INSERT INTO TAB(COL1,COL2,COL3) VALUES(NULL,NULL,3); INSERT INTO TAB(COL1,COL2,COL3) VALUES(NULL,1,3); INSERT INTO TAB(COL1,COL2,COL3) VALUES(NULL,NULL,2); SELECT * FROM TAB T;
得到表：
查询每一列值的个数
SELECT 'COL1' COL, COUNT(TAB.COL1) COLNUM FROM TAB UNION ALL SELECT 'COL2' COL,COUNT(TAB.COL2) COLNUM FROM TAB UNION ALL SELECT 'COL3' COL, COUNT(TAB.COL3) COLNUM FROM TAB
查询结果：
查询列值最多的列的列名和该列值的数量
SELECT TT.COL,TT.COLNUM FROM ( SELECT 'COL1' COL, COUNT(TAB.COL1) COLNUM FROM TAB UNION ALL SELECT 'COL2' COL,COUNT(TAB.COL2) COLNUM FROM TAB UNION ALL SELECT 'COL3' COL, COUNT(TAB.COL3) COLNUM FROM TAB )TT WHERE TT.COLNUM=(SELECT MAX(TT.COLNUM) FROM ( SELECT 'COL1' COL, COUNT(TAB.COL1) COLNUM FROM TAB UNION ALL SELECT 'COL2' COL,COUNT(TAB.COL2) COLNUM FROM TAB UNION ALL SELECT 'COL3' COL, COUNT(TAB.COL3) COLNUM FROM TAB ) TT )
查询结果：
