数据如下:
id 设备 金额
1 笔记本 4500
2 打印机 600
3 数码象机 2100
4 空调 1300
5 台式电脑 3300
6 东芝复印机 5000
7 显示器 1100要求取得的数据如下:
金额结构 数量 总金额
1000元以下 1 600
1000-2000 2 3200
2000-3000 1 2100
3000以上 3 12800
谢谢
id 设备 金额
1 笔记本 4500
2 打印机 600
3 数码象机 2100
4 空调 1300
5 台式电脑 3300
6 东芝复印机 5000
7 显示器 1100要求取得的数据如下:
金额结构 数量 总金额
1000元以下 1 600
1000-2000 2 3200
2000-3000 1 2100
3000以上 3 12800
谢谢
解决方案 »
- 触发器的语法
- 买书问题!
- SSIS实现 sqlserver 2005 资料抛砖informix资料库
- 高分求存储过程,关于记录插入
- 不同的页面引用相同的“数据库连接页面inc/conn.asp",为什么连接结果不一样?
- 大家来看看 出了什么问题啊
- 请问以下文件是何种类型的库文件,如何将其转换为access库呢?
- 在MS SQL Server中如何设计在insert操作执行前的触发器
- distinct 问题
- SQL server2008 java jdbc 链接问题 网上很多方法都试了大家来看下谢谢了
- 财付通的查询结果,用csv格式下载后,为何每个单元格前都加上 ` 字符呢?
- VB6.0的select max返回值问题
if object_id('[tb]') is not null drop table [tb]
create table [tb]([id] int,[设备] varchar(10),[金额] int)
insert [tb]
select 1,'笔记本',4500 union all
select 2,'打印机',600 union all
select 3,'数码象机',2100 union all
select 4,'空调',1300 union all
select 5,'台式电脑',3300 union all
select 6,'东芝复印机',5000 union all
select 7,'显示器',1100select
case when 金额 > 3000 then '3000以上' when 金额 > 2000 and 金额 <=3000 then '2000-3000'
when 金额 > 1000 and 金额 <=2000 then '1000-2000' else '1000元以下' end as 金额结构,
count(1) as 数量,
sum(金额) as 总金额
from [tb]
group by
case when 金额 > 3000 then '3000以上' when 金额 > 2000 and 金额 <=3000 then '2000-3000'
when 金额 > 1000 and 金额 <=2000 then '1000-2000' else '1000元以下' end
----------------------------
1000-2000 2 2400
1000元以下 1 600
2000-3000 1 2100
3000以上 3 12800
declare @tb table([id] int,[设备] varchar(10),[金额] int)
insert @tb
select 1,'笔记本',4500 union all
select 2,'打印机',600 union all
select 3,'数码象机',2100 union all
select 4,'空调',1300 union all
select 5,'台式电脑',3300 union all
select 6,'东芝复印机',5000 union all
select 7,'显示器',1100select 金额结构= '1000元以下',数量=sum (case when [金额]<1000 then 1 else 0 end),总金额=sum(case when [金额]<1000 then[金额] else 0 end) from @tb
union all
select 金额结构= '1000-2000', 数量=sum (case when [金额]>= 1000 and [金额]<2000 then 1 else 0 end),总金额=sum(case when [金额]>= 1000 and [金额]<2000 then[金额] else 0 end) from @tb
union all
select 金额结构= '2000-3000', 数量=sum (case when [金额]>= 2000 and [金额]<3000 then 1 else 0 end),总金额=sum(case when [金额]>= 2000 and [金额]<3000 then[金额] else 0 end) from @tb
union all
select 金额结构= '1000-2000', 数量=sum (case when [金额]>3000 then 1 else 0 end),总金额=sum(case when [金额]>3000 then[金额] else 0 end) from @tb
/*
金额结构 数量 总金额
---------- ----------- -----------
1000元以下 1 600
1000-2000 2 2400
2000-3000 1 2100
1000-2000 3 12800
*/
create table [tb]([id] int,[设备] varchar(10),[金额] int)
insert [tb]
select 1,'笔记本',4500 union all
select 2,'打印机',600 union all
select 3,'数码象机',2100 union all
select 4,'空调',1300 union all
select 5,'台式电脑',3300 union all
select 6,'东芝复印机',5000 union all
select 7,'显示器',1100select 金额结构,
count(1) as 数量,
sum(金额) as 总金额
from (select case when 金额 > 3000 then '3000以上'
when 金额 > 2000 and 金额 <=3000 then '2000-3000'
when 金额 > 1000 and 金额 <=2000 then '1000-2000'
else '1000 元以下' end as 金额结构,* from [tb]) a
group by 金额结构
金额结构 数量 总金额
----------- ----------- -----------
1000 元以下 1 600
1000-2000 2 2400
2000-3000 1 2100
3000以上 3 12800(所影响的行数为 4 行)
declare @tb table([id] int,[设备] varchar(10),[金额] int)
insert @tb
select 1,'笔记本',4500 union all
select 2,'打印机',600 union all
select 3,'数码象机',2100 union all
select 4,'空调',1300 union all
select 5,'台式电脑',3300 union all
select 6,'东芝复印机',5000 union all
select 7,'显示器',1100select 金额结构=case when [金额]<1000 then '1000元以下'
when [金额]>= 1000 and [金额]<2000 then '1000-2000'
when [金额]>= 2000 and [金额]<3000 then '2000-3000'
else '3000以上' end,
数量=count(1),
总金额=sum([金额])
from @tb
group by case when [金额]<1000 then '1000元以下'
when [金额]>= 1000 and [金额]<2000 then '1000-2000'
when [金额]>= 2000 and [金额]<3000 then '2000-3000'
else '3000以上' end
/*
金额结构 数量 总金额
---------- ----------- -----------
1000-2000 2 2400
1000元以下 1 600
2000-3000 1 2100
3000以上 3 12800
*/
--> 测试数据:@tb
declare @tb table([id] int,[设备] varchar(10),[金额] int)
insert @tb
select 1,'笔记本',4500 union all
select 2,'打印机',600 union all
select 3,'数码象机',2100 union all
select 4,'空调',1300 union all
select 5,'台式电脑',3300 union all
select 6,'东芝复印机',5000 union all
select 7,'显示器',1100
select * from
(
select 金额结构=(case when [金额]<1000 then '1000元以下'
when [金额]>= 1000 and [金额]<2000 then '1000-2000'
when [金额]>= 2000 and [金额]<3000 then '2000-3000'
else '3000以上' end),
数量=count(1),
总金额=sum([金额])
from @tb
group by case when [金额]<1000 then '1000元以下'
when [金额]>= 1000 and [金额]<2000 then '1000-2000'
when [金额]>= 2000 and [金额]<3000 then '2000-3000'
else '3000以上' end
) t
order by case 金额结构 when '1000元以下' then 1
when '1000-2000' then 2
when '2000-3000' then 3
else 4 end
/*
金额结构 数量 总金额
---------- ----------- -----------
1000元以下 1 600
1000-2000 2 2400
2000-3000 1 2100
3000以上 3 12800*/
create table tj_b (id int,jejg varchar(20),minje money,maxje money)
go
insert into tj_b (id,jejg,minje,maxje)
select 1,'1000元以下',0,1000
union all select 2,'1000-2000',1000,2000
union all select 3,'2000-3000',2000,3000
union all select 4,'3000以上',3000,-1declare @tb table([id] int,[设备] varchar(10),[金额] money)
insert @tb
select 1,'笔记本',4500 union all
select 2,'打印机',600 union all
select 3,'数码象机',2100 union all
select 4,'空调',1300 union all
select 5,'台式电脑',3300 union all
select 6,'东芝复印机',5000 union all
select 7,'显示器',1100select b.jejg as 范围,count(*) as 数量,sum(a.[金额]) as 金额
from @tb a,tj_b b
where a.[金额]>=b.minje and (a.[金额]<b.maxje or b.maxje=-1)
group by b.id,b.jejg
order by b.id
when 金额<=1000 then '1000元以下'
when 金额<=2000 then '1000-2000'
when 金额<=3000 then '2000-3000'
else '3000以上' end,
sum(总金额)
from table1
group by case
when 金额<=1000 then '1000元以下'
when 金额<=2000 then '1000-2000'
when 金额<=3000 then '2000-3000'
else '3000以上' end
金额结构,
count(1) as 数量,
sum(金额) as 总金额
from
(select
case when 金额 > 3000 then '3000以上'
when 金额 > 2000 and 金额 <=3000 then '2000-3000'
when 金额 > 1000 and 金额 <=2000 then '1000-2000'
else '1000 元以下' end as 金额结构,*
from [tb])t
group by
金额结构
case when 金额 > 3000 then '3000以上' when 金额 > 2000 and 金额 <=3000 then '2000-3000'
when 金额 > 1000 and 金额 <=2000 then '1000-2000' else '1000元以下' end as 金额结构,
count(1) as 数量,
sum(金额) as 总金额
from [tb]
group by
case when 金额 > 3000 then '3000以上' when 金额 > 2000 and 金额 <=3000 then '2000-3000'
when 金额 > 1000 and 金额 <=2000 then '1000-2000' else '1000元以下' end
count(1) as 数量,
sum(金额) as 总金额
from (select case when 金额 > 3000 then '3000以上'
when 金额 > 2000 and 金额 <=3000 then '2000-3000'
when 金额 > 1000 and 金额 <=2000 then '1000-2000'
else '1000 元以下' end as 金额结构,* from [tb]) a
group by 金额结构顶狙击手的
create table #tb1(id int,设备 varchar(16),金额 int)
insert into #tb1
select 1,'笔记本',4500 union all
select 2,'打印机',600 union all
select 3,'数码象机',2100 union all
select 4,'空调',1300 union all
select 5,'台式电脑',3300 union all
select 6,'东芝复印机',5000 union all
select 7,'显示器',1100
--正式语句:
select case when 金额<1000 then '1000元以下'
when 金额 between 1000 and 2000 then '1000到2000'
when 金额 between 2000 and 3000 then '2000到3000'
when 金额>3000 then '3000元以下' else '金额结构' end as 金额结构,sum(金额) as 金额,count(*) as 数量
from #tb1
group by case when 金额<1000 then '1000元以下'
when 金额 between 1000 and 2000 then '1000到2000'
when 金额 between 2000 and 3000 then '2000到3000'
when 金额>3000 then '3000元以下' else '金额结构' end
order by sum(金额)
when 金额 > 2000 and 金额 <=3000 then '2000-3000'
when 金额 > 1000 and 金额 <=2000 then '1000-2000'
else '1000 元以下' end as 金额结构,* from [tt]) a
group by 金额结构
这样得出来的结果金额字段是排序好了的。为什么我下面写的就没有排序呢?倒序也没有,完全是乱序,这是怎么回事?select 金额结构, count(1) as 数量, sum(金额) as 总金额from ( select case when 金额 < 1000 then '1000以下'
when 金额 >=1000 and 金额<= 2000 then '1000-2000'
when 金额 >2000 and 金额 < 3000 then '2000-3000'
else '3000以上' end as 金额结构,* from [tt] )d
group by 金额结构
--建立测试表
create table ITProducts
(
[id] int primary key identity(1,1),
ProductName varchar(10),
UnitPrice int
)--插入测试数据
insert into ITProducts values('笔记本',4500);
insert into ITProducts values('打印机',600);
insert into ITProducts values('数码相机',2100);
insert into ITProducts values('空调',1300);
insert into ITProducts values('台式电脑',3300);
insert into ITProducts values('东芝复印机',5000);
insert into ITProducts values('显示器',1100);--执行查询
select '金额结构'=case
when UnitPrice >3000 then '3000以上'
when UnitPrice between 2001 and 3000 then '2000-3000'
when UnitPrice between 1000 and 2000 then '1000-2000'
when UnitPrice<1000 then '1000以下'
end,
'数量'=count(1),
'总金额'=sum(UnitPrice)
from ITProducts
group by case
when UnitPrice >3000 then '3000以上'
when UnitPrice between 2001 and 3000 then '2000-3000'
when UnitPrice between 1000 and 2000 then '1000-2000'
when UnitPrice<1000 then '1000以下'
end/*
金额结构 数量 总金额
--------- ----------- -----------
1000-2000 2 2400
1000以下 1 600
2000-3000 1 2100
3000以上 3 12800(所影响的行数为 4 行)
*/
create table #test(id int,sb varchar(50),je numeric(14,4))
insert #test
select 1,'笔记本',4500 union all
select 2,'打印机',600 union all
select 3,'数码相机',2100 union all
select 4,'空调',1300 union all
select 5,'台式电脑',3300 union all
select 6,'东芝复印机',5000 union all
select 7,'显示器',1100select type as '金额结构', count(1) as '数量', sum(je) as '总额'
from
(
select *,
type = case when je < 1000 then '1000元以下'
when je >= 1000 and je < 2000 then '1000-2000'
when je >= 2000 and je < 3000 then '2000-3000'
else '3000以上'
end
from #test
) a group by type
select '1000-2000',count(ID),sum(金额) from tb where 金额<2000 and 金额>1000 union
select '2000-3000',count(ID),sum(金额) from tb where 金额<3000 and 金额>2000 union
select '3000以上',count(ID),sum(金额) from tb where 金额>3000
select '1000-2000',count(ID),sum(金额) from tb where 金额<2000 and 金额>1000 union
select '2000-3000',count(ID),sum(金额) from tb where 金额<3000 and 金额>2000 union
select '3000以上',count(ID),sum(金额) from tb where 金额>3000
“请问以金额为来group by”,研究了好久,还是没明白这句话是啥意思。楼主是老外吗?
select 设备金额,count(数量) as 数量,sum(总金额)as 金额 from (
select
case when 金额<1000 then '小于1000'
when 2000>金额 and 金额>1000 then '1000-2000'
when 2000<金额 and 金额<3000 then '2000-3000'
when 金额>3000 then '大于3000'
end as 设备金额,
count(1) as 数量,
sum(金额) as 总金额
from tb
group by 金额) a
group by 设备金额
-->方法1 6s 写的很巧,但一但表的数据量大的话,可能就影响查询速度,建议针对当前这个问题不要使用嵌套子查询,可以最后进行排序一下即可!
select 金额结构,
count(1) as 数量,
sum(金额) as 总金额
from (select case when 金额 > 3000 then '3000以上'
when 金额 > 2000 and 金额 <=3000 then '2000-3000'
when 金额 > 1000 and 金额 <=2000 then '1000-2000'
else '1000 元以下' end as 金额结构,* from [tb]) a
group by 金额结构
-->方法2 7sselect 金额结构= '1000元以下',数量=sum (case when [金额]<1000 then 1 else 0 end),总金额=sum(case when [金额]<1000 then[金额] else 0 end) from tb
union all
select 金额结构= '1000-2000', 数量=sum (case when [金额]>= 1000 and [金额]<2000 then 1 else 0 end),总金额=sum(case when [金额]>= 1000 and [金额]<2000 then[金额] else 0 end) from tb
union all
select 金额结构= '2000-3000', 数量=sum (case when [金额]>= 2000 and [金额]<3000 then 1 else 0 end),总金额=sum(case when [金额]>= 2000 and [金额]<3000 then[金额] else 0 end) from tb
union all
select 金额结构= '1000-2000', 数量=sum (case when [金额]>3000 then 1 else 0 end),总金额=sum(case when [金额]>3000 then[金额] else 0 end) from tb-->方法3 6s
select
case when 金额 > 3000 then '3000以上' when 金额 > 2000 and 金额 <=3000 then '2000-3000'
when 金额 > 1000 and 金额 <=2000 then '1000-2000' else '1000元以下' end as 金额结构,
count(1) as 数量,
sum(金额) as 总金额
from [tb]
group by
case when 金额 > 3000 then '3000以上' when 金额 > 2000 and 金额 <=3000 then '2000-3000'
when 金额 > 1000 and 金额 <=2000 then '1000-2000' else '1000元以下' end -->方法4 2s
select '1000以下' as 金额结构 , count(ID) as 数量 ,sum(金额) as 总金额 from tb where 金额<1000 union
select '1000-2000',count(ID),sum(金额) from tb where 金额<2000 and 金额>1000 union
select '2000-3000',count(ID),sum(金额) from tb where 金额<3000 and 金额>2000 union
select '3000以上',count(ID),sum(金额) from tb where 金额>3000
case when 金额 > 3000 then '3000以上' when 金额 > 2000 and 金额 <=3000 then '2000-3000'
when 金额 > 1000 and 金额 <=2000 then '1000-2000' else '1000元以下' end as 金额结构,
count(1) as 数量,
sum(金额) as 总金额
from [tb]
group by
case when 金额 > 3000 then '3000以上' when 金额 > 2000 and 金额 <=3000 then '2000-3000'
when 金额 > 1000 and 金额 <=2000 then '1000-2000' else '1000元以下' end
select
t.金额结构,
count(1) as 数量,
sum(金额) as 总金额
from
(select
case when 金额 > 3000 then '3000以上'
when 金额 > 2000 and 金额 <=3000 then '2000-3000'
when 金额 > 1000 and 金额 <=2000 then '1000-2000'
else '1000 元以下' end as 金额结构,*
from [tb])t
group by
金额结构
drop table tb
go
create table tb(id int,name1 nvarchar(20),price float)
insert into tb
select 1 , '笔记本' , 4500 union all
select 2 , '打印机' , 600 union all
select 3 , '数码象机' , 2100 union all
select 4 , '空调' , 1300 union all
select 5 , '台式电脑' , 3300 union all
select 6 , '东芝复印机' , 5000 union all
select 7 , '显示器' , 1100
select t1,count(1),sum(price) from (
select case when price<1000 then '1000元以下' when price>=1000 and price<2000 then '1000-2000' when
price>=2000 and price<3000 then '1000-2000' when price>=3000 then '3000以上' end as t1,price from tb
) t group by t1
select '1000-2000',count(ID),sum(金额) from tb where 金额 <2000 and 金额>1000 union
select '2000-3000',count(ID),sum(金额) from tb where 金额 <3000 and 金额>2000 union
select '3000以上',count(ID),sum(金额) from tb where 金额>3000
union all
select '1000-2000' as 金额结构,count(*) as 数量,sum(金额) as 总金额 from 数据表 where 金额>=1000 and 金额<2000
union all
select '2000-3000' as 金额结构,count(*) as 数量,sum(金额) as 总金额 from 数据表 where 金额>=2000 and 金额<3000
union all
select '3000以上' as 金额结构,count(*) as 数量,sum(金额) as 总金额 from 数据表 where 金额>=3000