解决方案 »
- 班级信息管理系统的班费表,和班费记录表应该有哪些字段?
- SQL 数据库还原。。。
- 求一多表关联SQL语句
- 错误:未能找到 ID 为 104 的数据库。可能该数据库尚未激活,也可能正在转换过程中。
- 字段赋值
- 如何将几个表中的若干字段组合起来,存放到一个新表中?
- 关于存储过程的问题
- MSSQL Store Procedure datebase name 问题
- 创建触发器时 ,其中的关键字 after 和instead of 有什么用,能不能举个例子,十分感谢
- 用pb编酒店管理系统,如何实现c/s模式?
- C#数据库SQL插入两条数据,第二条要引用第一天自动产生的ID怎么写?
- 找不到sql server 2008 R2 SP2 express support .Net4的版本。
from test
where 客户 in
(select 客户
from test
where DATE >= '2011-06-30'
group by 客户
having (count (distinct 商品)between 2 and 6)
)
group by 客户,商品试一下呢
select * from
(
select [客户名称],COUNT(1) as [商品种类],SUM([金额]) as [金额] from
(
select [客户名称],[商品],SUM([金额]) as [金额] from TEST group by [客户名称],[商品]
) a group by [客户名称]
)b having [商品种类]>1 and [商品种类]<7
from test
where 客户 in
(select 客户
from test
where [DATE] >= '2011-06-30'
group by 客户
having (count (distinct 商品)between 2 and 6))
and [DATE] >= '2011-06-30'
group by 客户,商品
order by 客户,商品
1、要删除in里面的select列表里的count计算表达式
select a.客户,a.商品,sum(a.金额) as 金额
from test a join
(
select 客户 ,count (distinct 商品) as 商品类别
from test
where DATE >= '2011-06-30'
group by 客户
having count (distinct 商品) between 2 and 6
) b on a.客户=b.客户
group by a.客户,a.商品
from test
where 客户 in
(select 客户
from test
where [DATE] >= '2011-06-30'
group by 客户
having (count (distinct 商品)between 2 and 6))
and [DATE] >= '2011-06-30'
group by 客户,商品
order by 客户,商品
2,SQL执行的逻辑你可以看下: microsoft sql server 2005 技术内幕 这本书 里面有一个章节:查询的逻辑执行过程