江湖救济阿,帮我看看这句sql应该怎么写?表格主要有2列状态 申请人名
-----------------------------------
1 A
1 A
2 A
3 A
3 B
3 C
4 A
4 B
4 B
4 C
怎样根据状态(假设状态=4)和分组申请人名得到如下数量统计?状态 申请人名 数量
-----------------------------------
4 A 1
4 B 2
4 C 1
-----------------------------------
1 A
1 A
2 A
3 A
3 B
3 C
4 A
4 B
4 B
4 C
怎样根据状态(假设状态=4)和分组申请人名得到如下数量统计?状态 申请人名 数量
-----------------------------------
4 A 1
4 B 2
4 C 1
FROM tb
WHERE 状态=4
GROUP BY 状态,申请人名;
FROM tb
WHERE 状态=4
GROUP BY 状态,申请人名;
帅
FROM tb
GROUP BY 状态,申请人名
FROM tb
WHERE 状态=4
GROUP BY 状态,申请人名
SELECT 状态, 申请人名, COUNT(*) AS '数量'
FROM TABLE
WHERE (userid = '4')
GROUP BY 状态, 申请人名
from tb
group by 申请人名,状态
having 状态=4
DECLARE @t TABLE(zt INT,sm NVARCHAR(10))
INSERT INTO @t(zt,sm)
SELECT 1,'A' UNION ALL
SELECT 1,'A' UNION ALL
SELECT 2,'A' UNION ALL
SELECT 3,'A' UNION ALL
SELECT 3,'B' UNION ALL
SELECT 3,'C' UNION ALL
SELECT 4,'A' UNION ALL
SELECT 4,'B' UNION ALL
SELECT 4,'B' UNION ALL
SELECT 4,'C' SELECT zt,sm,COUNT(sm) FROM @t GROUP BY zt,sm ORDER BY zt zt sm
----------- ---------- -----------
1 A 2
2 A 1
3 A 1
3 B 1
3 C 1
4 A 1
4 B 2
4 C 1(8 row(s) affected)