-->测试数据：[tb]
ifobject_id('[tb]')isnotnulldroptable[tb]
go
createtable[tb]([姓名]varchar(1),[部门]varchar(4),[学历]varchar(4),[出生年月]datetime)
insert[tb]
select'A','后勤','高中','1986-1-1'unionall
select'B','后勤','初中','1984-3-7'unionall
select'C','管理','本科','1987-2-1'unionall
select'D','操作','专科','1976-2-1'unionall
select'E','操作','专科','1943-2-1'
--------------开始查询--------------------------
declare@sqlvarchar(8000)
set@sql='select部门,dbo.AgeLevel([出生年月])as年龄段'
select@sql=@sql+',sum(case学历when'''+学历+'''then1else0end)['+学历+']'
from(selectdistinct学历fromtb)asa
set@sql=@sql+'fromtbgroupby部门,dbo.AgeLevel([出生年月])'
exec(@sql)
/*
部门年龄段本科初中高中专科
----------------------------------------------------------
管理21－301000
后勤21－300110
操作31－400001
操作50以上0001
(4行受影响)
*/
dropfunctionAgeLevel
go
--获取年龄段
createfunctionAgeLevel(@birthdaydatetime)
returnsvarchar(10)
as
begin
declare@AgeLevelvarchar(10)
select@AgeLevel=case((datediff(year,@birthday,getdate())-1)/10)when2then'21－30'when3then'31－40'when4then'41－50'else'50以上'end
return@AgeLevel
end
go
select*,dbo.AgeLevel([出生年月])as年龄段fromtb
/*
姓名部门学历出生年月年龄段
---------------------------------------------
A后勤高中1986-01-0100:00:00.00021－30
B后勤初中1984-03-0700:00:00.00021－30
C管理本科1987-02-0100:00:00.00021－30
D操作专科1976-02-0100:00:00.00031－40
E操作专科1943-02-0100:00:00.00050以上
*/
selectN'年龄段'=(
case((datediff(year,[出生年月],getdate())-1)/10)
when2then'21－30'
when3then'31－40'
when4then'41－50'
else'50以上'
end),
count(*)ascount
fromtb
groupby(
case((datediff(year,[出生年月],getdate())-1)/10)
when2then'21－30'
when3then'31－40'
when4then'41－50'
else'50以上'
end)
/*
年龄段count
-----------------
21－303
31－401
50以上1
(3行受影响)
*/
--以10岁为递增
select
cast(f1*10+1asvarchar(3))+'-'+cast(f1*10+10asvarchar(3))as年龄段,f2as人数
from
(
selectdatediff(d,[出生年月],getdate())/365/10asf1,
count(*)asf2
fromtb
groupbydatediff(d,[出生年月],getdate())/365/10)a
orderbycast(f1*10+1asvarchar(3))+'-'+cast(f1*10+10asvarchar(3))
/*
年龄段人数
------------------
21-303
31-401
61-701
(3行受影响)
*/
SELECT
SUM(
CASEWHENdatediff(year,[出生年月],getdate())BETWEEN16AND20THEN1ELSE0END)AS'16-20',
SUM(CASEWHENdatediff(year,[出生年月],getdate())BETWEEN21AND30THEN1ELSE0END)AS'21-30',
SUM(CASEWHENdatediff(year,[出生年月],getdate())BETWEEN31AND40THEN1ELSE0END)AS'31-40',
SUM(CASEWHENdatediff(year,[出生年月],getdate())BETWEEN41AND50THEN1ELSE0END)AS'41-50',
SUM(CASEWHENdatediff(year,[出生年月],getdate())BETWEEN51AND60THEN1ELSE0END)AS'51-60',
SUM(CASEWHENdatediff(year,[出生年月],getdate())BETWEEN61AND70THEN1ELSE0END)AS'61-70'
FROMtb
/*
16--3031-4041-5051-6061-70
------------------------------------------------------------------
031001
(1行受影响)
*/
create table brands(id int,brand varchar(10), address varchar(10))
insert into brands values(1 ,'联想', '北京')
insert into brands values(2 ,'惠普', '美国')
insert into brands values(3 ,'神舟', '深圳')
create table products(id int, brand int, name varchar(10))
insert into products values(1 ,1, '联想1')
insert into products values(2 ,1, '联想2')
insert into products values(3 ,2, '惠普1')
insert into products values(4 ,2, '惠普2'
) insertinto products values(5 ,1, '联想3')
insertinto products values(6 ,3, '神舟1')
insertinto products values(7 ,1, '联想4')
go
selectID=row_number()over(orderbygetdate()),
b.产品数量,
a.[brand],
a.[address]
frombrandsa,
(select[brand],
count([brand])产品数量
fromproducts
groupby[brand])b
wherea.[ID]=b.[brand]
orderbyb.产品数量desc
selectb.id,tas产品数量,b.brand,b.address
frombrandsb
join
(
selectbrand,count(brand)cnt
fromproducts
groupbybrand
)b1
onb1.brand=b.id
id产品数量brandaddress
----------------------------------------------------------------------------------
14联想北京
22惠普美国
31神舟深圳
(3行受影响)
select
sum(casewhen(字段名>0and字段名<4000)then1else0end)别名,
sum(casewhen字段名>=4000and字段名<8000then1else0end)别名,
sum(casewhen字段名>=8000then1else0end)别名,
count(*)astotalfrom表名
如果觉得《SQL 年龄段 品牌分类 分组统计》对你有帮助，请点赞、收藏，并留下你的观点哦！
