COMPUTE BY 子句 cube 子句

最新推荐文章于 2022-03-08 14:31:06 发布

diaoxiangsi1087

最新推荐文章于 2022-03-08 14:31:06 发布

阅读量214

点赞数

文章标签：数据库

原文链接：http://www.cnblogs.com/buaaboyi/archive/2011/08/11/2134845.html

版权

declare @Temp TABLE (
pid INT PRIMARY KEY IDENTITY ( 1 , 1 ),
name VARCHAR ( 100 ),
color VARCHAR ( 50 ),
num INT
)

INSERT INTO @Temp (name,color,num)
SELECT ' ball ' , ' red ' , 12
UNION ALL
SELECT ' ball ' , ' red ' , 24
UNION ALL
SELECT ' cat ' , ' yellow ' , 13
UNION ALL
SELECT ' ball ' , ' yellow ' , 13
UNION ALL
SELECT ' dog ' , ' yellow ' , 13
UNION ALL
SELECT ' cat ' , ' black ' , 4
UNION ALL
SELECT ' cat ' , ' white ' , 5

select * from @Temp

SELECT NAME,color,num
FROM @Temp
ORDER BY color

COMPUTE SUM(num),MAX(num) by color

-----------------------------------------------------

DECLARE @CubeExample TABLE (
ProductName VARCHAR ( 30 ) NULL ,
CustomerName VARCHAR ( 30 ) NULL ,
Orders INT NULL
)

INSERT @CubeExample (ProductName, CustomerName, Orders)
VALUES ( ' Filo Mix ' , ' Romero y tomillo ' , 10 )
INSERT @CubeExample (ProductName, CustomerName, Orders)
VALUES ( ' Outback Lager ' , ' Wilman Kala ' , 10 )
INSERT @CubeExample (ProductName, CustomerName, Orders)
VALUES ( ' Filo Mix ' , ' Romero y tomillo ' , 20 )
INSERT @CubeExample (ProductName, CustomerName, Orders)
VALUES ( ' Ikura ' , ' Wilman Kala ' , 10 )
INSERT @CubeExample (ProductName, CustomerName, Orders)
VALUES ( ' Ikura ' , ' Romero y tomillo ' , 10 )
INSERT @CubeExample (ProductName, CustomerName, Orders)
VALUES ( ' Outback Lager ' , ' Wilman Kala ' , 20 )
INSERT @CubeExample (ProductName, CustomerName, Orders)
VALUES ( ' Filo Mix ' , ' Wilman Kala ' , 30 )
INSERT @CubeExample (ProductName, CustomerName, Orders)
VALUES ( ' Filo Mix ' , ' Eastern Connection ' , 40 )
INSERT @CubeExample (ProductName, CustomerName, Orders)
VALUES ( ' Outback Lager ' , ' Eastern Connection ' , 10 )
INSERT @CubeExample (ProductName, CustomerName, Orders)
VALUES ( ' Ikura ' , ' Wilman Kala ' , 40 )
INSERT @CubeExample (ProductName, CustomerName, Orders)
VALUES ( ' Ikura ' , ' Romero y tomillo ' , 10 )
INSERT @CubeExample (ProductName, CustomerName, Orders)
VALUES ( ' Filo Mix ' , ' Romero y tomillo ' , 50 ) ;

SELECT * FROM @CubeExample
order by ProductName

SELECT ProductName, CustomerName, SUM (Orders)
FROM @CubeExample
GROUP BY ProductName, CustomerName
ORDER BY ProductName ;

SELECT ProductName, CustomerName, SUM (Orders)
FROM @CubeExample
GROUP BY ProductName, CustomerName
WITH CUBE

转载于:https://www.cnblogs.com/buaaboyi/archive/2011/08/11/2134845.html