sql server数据库简单的sql 统计

 

一、先建库表

use master

if exists (select 1
from sysobjects
where id = object_id('testsum')
and type = 'U')
drop table testsum
go

create table testsum
(
id int identity(1,1),
name nvarchar(50) default(''),
typename nvarchar(50) default(''),
num int default(0),
val float default(9)
)

 

begin

declare @i int;
declare @ii int;
set @i= 1;

while( @i<1000)
begin
  set @ii= @i%5;
insert into testsum (name,typename,num ,val)values ('名称'+cast(@i as varchar(4)),'类型'++cast(@ii as varchar(1)), cast( floor(rand()*1000) as int),round((rand()*1000),2) );
set @i=@i+1;
end

end

二、查询建表情况

 

select * FROM testsum

 

三、进入主题，简单sql语句汇总

--1.总计多少条数据count()函数
select count (0) from testsum
where 1=1

 

-- 2.求和sum()函数 针对某字段
select sum (num),sum(val) from testsum ;
select sum (num) from testsum
select sum (val) from testsum ;

-- 3.根据名称分类求和,分类查询必须有group by
select sum (val),name from testsum
where 1=1
group by name
order by name

-- 4.根据类型分类求和,求数量,分类查询必须有group by 

select sum (val),count(typename),typename from testsum
where 1=1
group by typename
order by typename

 

--5 最大值max()，最小值min()
select max (val),min(val),max(num),min(num) from testsum