数据如下:
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
谢谢
解决方案 »
- 一般来讲临时表是不是效率比直接使用sql语句低很多?
- SQL 函数执行过程自动排序的奇怪问题?
- SQL語句如何以變量形式執行 高手請進
- 怎么样复制SQL SERVER 现有数据库结构?
- 嵌套查询的问题
- 数据库恢复备份,只恢复新变的结构,不覆盖原来的数据
- 我需要一个sql语句,可能要用到count(1),DISTINCT group by....
- 记录按条进行字段赋值该如何实现呢?【请高手来救命....】
- 讨论:在数据库表中增删列好不好,以例子说明
- 请教各位大侠,关于数据库连接的问题??
- 财付通的查询结果,用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