在客户订购表中统计商品被订购数量超过500个的商品编号。

SQL with cube
如何查询在客户订购表中统计商品被订购数量超过500个的商品编号。
1、TB_Buy的表结构

CREATE TABLE [dbo].[TB_Buy](
	[BID] [int] NOT NULL,
	[CID] [char](10) NOT NULL,
	[PID] [char](10) NOT NULL,
	[BTime] [datetime] NOT NULL,
	[BNum] [int] NOT NULL
) ON [PRIMARY]

GO

2、向表中插入数据

INSERT [dbo].[TB_Buy] ([BID], [CID], [PID], [BTime], [BNum]) VALUES (1, N'CR001     ', N'PD002     ', CAST(0x0000A26500000000 AS DateTime), 400)
INSERT [dbo].[TB_Buy] ([BID], [CID], [PID], [BTime], [BNum]) VALUES (2, N'CR002     ', N'PD001     ', CAST(0x0000A28B00000000 AS DateTime), 200)
INSERT [dbo].[TB_Buy] ([BID], [CID], [PID], [BTime], [BNum]) VALUES (3, N'CR002     ', N'PD004     ', CAST(0x0000A28200000000 AS DateTime), 100)
INSERT [dbo].[TB_Buy] ([BID], [CID], [PID], [BTime], [BNum]) VALUES (4, N'CR002     ', N'PD008     ', CAST(0x0000A29500000000 AS DateTime), 300)
INSERT [dbo].[TB_Buy] ([BID], [CID], [PID], [BTime], [BNum]) VALUES (5, N'CR003     ', N'PD004     ', CAST(0x0000A28700000000 AS DateTime), 100)
INSERT [dbo].[TB_Buy] ([BID], [CID], [PID], [BTime], [BNum]) VALUES (6, N'CR004     ', N'PD003     ', CAST(0x0000A23F00000000 AS DateTime), 150)
INSERT [dbo].[TB_Buy] ([BID], [CID], [PID], [BTime], [BNum]) VALUES (7, N'CR004     ', N'PD005     ', CAST(0x0000A23F00000000 AS DateTime), 80)
INSERT [dbo].[TB_Buy] ([BID], [CID], [PID], [BTime], [BNum]) VALUES (8, N'CR004     ', N'PD006     ', CAST(0x0000A25100000000 AS DateTime), 50)
INSERT [dbo].[TB_Buy] ([BID], [CID], [PID], [BTime], [BNum]) VALUES (9, N'CR005     ', N'PD002     ', CAST(0x0000A26900000000 AS DateTime), 300)
INSERT [dbo].[TB_Buy] ([BID], [CID], [PID], [BTime], [BNum]) VALUES (10, N'CR007     ', N'PD002     ', CAST(0x0000A1EF00000000 AS DateTime), 50)
INSERT [dbo].[TB_Buy] ([BID], [CID], [PID], [BTime], [BNum]) VALUES (11, N'CR008     ', N'PD001     ', CAST(0x0000A27A00000000 AS DateTime), 280)
INSERT [dbo].[TB_Buy] ([BID], [CID], [PID], [BTime], [BNum]) VALUES (12, N'CR008     ', N'PD003     ', CAST(0x0000A26500000000 AS DateTime), 200)
INSERT [dbo].[TB_Buy] ([BID], [CID], [PID], [BTime], [BNum]) VALUES (13, N'CR008     ', N'PD004     ', CAST(0x0000A26D00000000 AS DateTime), 100)
INSERT [dbo].[TB_Buy] ([BID], [CID], [PID], [BTime], [BNum]) VALUES (14, N'CR008     ', N'PD006     ', CAST(0x0000A25100000000 AS DateTime), 150)

3、用select及group by 语句查询
**

group by *1,2 with cube(这样会根据1，*2做一个汇总计算，即group by后的所有列)

**
同理，with rollup，只对group by 分组后的第一个字段做汇总计算。
cube运算符在 SELECT 语句的 GROUP BY 子句中指定。该语句的选择列表应包含维度列和聚合函数表达式。GROUP BY 应指定维度列和关键字 WITH CUBE。结果集将包含维度列中各值的 所有 可能组合，以及与这些维度值组合相匹配的基础行中的聚合值。

 select  BNum,PID,Sum(BNum) as 订购数量
 from TB_Buy 
 group by BNum,PID,with cube 
 having SUM(BNum)>500
 order by PID