表A
ID FAC CAT
1 Y RP
2 Z HA
3 Z HA
4 Z RP
5 Y HA
.......
N
SELECT Count(ID), FAC, CAT
FROM A
GROUP BY FAC, CAT;
运行这个语句得出这个结果
1 Y HA
1 Y RP
2 Z HA
1 Z RP
请问应该怎么写语句能再这个基础上再得出个汇总行来,谢谢!
希望得出这个结果
1 Y HA
1 Y RP
2 Y NULL
2 Z HA
1 Z RP
3 Z NULL
ID FAC CAT
1 Y RP
2 Z HA
3 Z HA
4 Z RP
5 Y HA
.......
N
SELECT Count(ID), FAC, CAT
FROM A
GROUP BY FAC, CAT;
运行这个语句得出这个结果
1 Y HA
1 Y RP
2 Z HA
1 Z RP
请问应该怎么写语句能再这个基础上再得出个汇总行来,谢谢!
希望得出这个结果
1 Y HA
1 Y RP
2 Y NULL
2 Z HA
1 Z RP
3 Z NULL
FROM A
GROUP BY FAC, CAT
with rollup;
SELECT Count(ID), FAC, CAT
FROM A
GROUP BY FAC, CAT
with rollup
-- Author: happyflystone
-- Date : 2009-05-11 16:48:34
-- Ver: Microsoft SQL Server 2005 - 9.00.2047.00 (Intel X86)
-- Apr 14 2006 01:12:25
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
--
-------------------------------------------------------------------------- Test Data: T1
IF OBJECT_ID('T1') IS NOT NULL
DROP TABLE T1
Go
CREATE TABLE T1(ID INT,FAC NVARCHAR(1),CAT NVARCHAR(2))
Go
INSERT INTO T1
SELECT 1,'Y','RP' UNION ALL
SELECT 2,'Z','HA' UNION ALL
SELECT 3,'Z','HA' UNION ALL
SELECT 4,'Z','RP' UNION ALL
SELECT 5,'Y','HA'
GO
--Start
SELECT Count(ID), FAC, CAT
FROM t1
GROUP BY FAC, CAT
with rollup
having grouping(fac) = 0 or grouping(cat) = 0;
--Result:
/* FAC CAT
----------- ---- ----
1 Y HA
1 Y RP
2 Y NULL
2 Z HA
1 Z RP
3 Z NULL
*/
--End
SELECT Count(ID), FAC, ISNULL(CAT ,'NULL')
FROM A
GROUP BY FAC, CAT
with rollup
SELECT
Count(ID), FAC, CAT
FROM
表A
GROUP BY
FAC, CAT with rollup
HAVING
grouping(fac) = 0 or grouping(cat) = 0
DROP TABLE LI CREATE TABLE LI (ID INT ,FAC VARCHAR(10), CAT varchar(10))insert LI (ID,FAC, CAT)
SELECT 1 , 'Y', 'RP' UNION ALL
SELECT 2 , 'Z', 'HA' UNION ALL
SELECT 3 , 'Z', 'HA' UNION ALL
SELECT 4 , 'Z', 'RP' UNION ALL
SELECT 5 , 'Y', 'HA'
--结果
SELECT Count(ID) AS ID, FAC, CAT
FROM LI
GROUP BY FAC, CAT WITH ROLLUP
--RESULT
ID FAC CAT
1 Y HA
1 Y RP
2 Y NULL
2 Z HA
1 Z RP
3 Z NULL
5 NULL NULL
access:SELECT Count(ID), FAC, CAT
FROM t1
GROUP BY FAC, CAT
union all
select count(1),fac,null
from t1
group by fac
access也没问题,可是出来的结果排序不是我想要的,是不是只能再select一下你给的语句order by fac,谢谢!
SELECT Count(ID), FAC, CAT
FROM t1
GROUP BY FAC, CAT
union all
select count(id),fac,null
from t1
group by fac
order by fac,1
--Result:
/* FAC CAT
----------- ---- ----
1 Y HA
1 Y RP
2 Y NULL
2 Z HA
1 Z RP
3 Z NULL
*/
--End