27,579
社区成员
发帖
与我相关
我的任务
分享
with t as
(select col2,col3,cs,row_number()over (partition by col2 order by cs desc) as row from (select col2,col3,count(col3) AS CS from table5
group by col2,col3
) a)
select col2,col3,cs from t where row=1
北部地区 裸色系 1
东部地区 红色系 2
华中地区 粉色系 2
CREATE TABLE TABLE5
(
Col1 INT,
Col2 VARCHAR(10),
Col3 VARCHAR(10)
)
GO
INSERT INTO TABLE5
SELECT 5, '华中地区', '红色系' UNION
SELECT 9, '华中地区', '粉色系' UNION
SELECT 10, '华中地区', '粉色系' UNION
SELECT 6, '东部地区', '红色系' UNION
SELECT 1, '东部地区', '红色系' UNION
SELECT 2, '东部地区', '紫色系' UNION
SELECT 3, '东部地区', '粉色系' UNION
SELECT 4, '北部地区', '裸色系' UNION
SELECT 1, '东部地区', '裸色系'
SELECT MAX(tt.NUM) AS num2,tt.Col2, tt.Col3
FROM (SELECT sum(t.Col1) AS NUM,t.Col2, t.Col3 FROM TABLE5 t
GROUP BY t.Col2,t.Col3) tt
GROUP BY tt.Col2,tt.Col3
num2 Col2 Col3
----------- ---------- ----------
3 东部地区 粉色系
19 华中地区 粉色系
7 东部地区 红色系
5 华中地区 红色系
4 北部地区 裸色系
1 东部地区 裸色系
2 东部地区 紫色系
CREATE TABLE TABLE5
(
Col1 INT,
Col2 VARCHAR(10),
Col3 VARCHAR(10)
)
GO
INSERT INTO TABLE5
SELECT 5, '华中地区', '红色系' UNION
SELECT 9, '华中地区', '粉色系' UNION
SELECT 10, '华中地区', '粉色系' UNION
SELECT 6, '东部地区', '红色系' UNION
SELECT 1, '东部地区', '红色系' UNION
SELECT 2, '东部地区', '紫色系' UNION
SELECT 3, '东部地区', '粉色系' UNION
SELECT 4, '北部地区', '裸色系' UNION
SELECT 1, '东部地区', '裸色系'
WITH table1 as (SELECT Col2,Col3,COUNT(1) AS num
FROM Table5
GROUP BY Col2,Col3)
SELECT Col2,Col3,num
FROM TABLE1 AS B
WHERE num >= ALL(SELECT num from TABLE1 as A WHERE A.Col2 = B.col2)
SELECT ROW_NUMBER() OVER (PARTITION BY 地区 ORDER BY 次数 DESC) AS num FROM dbo.a
WHERE num=1
select 地区,色系名,max(次数) from tb group by 地区,色系名