Sql Server分组查询示例
来源:互联网 发布:红蜘蛛mac版 编辑:程序博客网 时间:2024/05/19 15:41
create table Guest_info
(
GuestID int primary key,
GuestName varchar(50),
birthday datetime
)
create table Or_info
(
Order_id int identity(1,1) primary key,
GuestID int foreign key REFERENCES Guest_info(GuestID),
Isyes int -- 0:notOK 1:Ok 2:Cancel 3:Renege
)
select c.age,
TotalCount=count(*),
notOKCount=sum(case c.Isyes when 0 then 1 else 0 end) ,
cancelCount=sum(case c.Isyes when 2 then 1 else 0 end) ,
RenegeCount=sum(case c.Isyes when 3 then 1 else 0 end) ,
OKCount=sum(case c.Isyes when 1 then 1 else 0 end)
from
(select
age = case
when datediff(yy,cast(a.birthday as datetime),getdate()) >50
then '50以上'
when datediff(yy,cast(a.birthday as datetime),getdate()) between 41 and 50
then '41-50'
when datediff(yy,cast(a.birthday as datetime),getdate()) between 31 and 40
then '31-40'
when datediff(yy,cast(a.birthday as datetime),getdate()) between 20 and 30
then '20-30'
else
'20以下'
end,
b.*
from
Guest_info a inner join
Or_info b
on
a.GuestID = b.GuestID) c
group by c.age
(
GuestID int primary key,
GuestName varchar(50),
birthday datetime
)
create table Or_info
(
Order_id int identity(1,1) primary key,
GuestID int foreign key REFERENCES Guest_info(GuestID),
Isyes int -- 0:notOK 1:Ok 2:Cancel 3:Renege
)
select c.age,
TotalCount=count(*),
notOKCount=sum(case c.Isyes when 0 then 1 else 0 end) ,
cancelCount=sum(case c.Isyes when 2 then 1 else 0 end) ,
RenegeCount=sum(case c.Isyes when 3 then 1 else 0 end) ,
OKCount=sum(case c.Isyes when 1 then 1 else 0 end)
from
(select
age = case
when datediff(yy,cast(a.birthday as datetime),getdate()) >50
then '50以上'
when datediff(yy,cast(a.birthday as datetime),getdate()) between 41 and 50
then '41-50'
when datediff(yy,cast(a.birthday as datetime),getdate()) between 31 and 40
then '31-40'
when datediff(yy,cast(a.birthday as datetime),getdate()) between 20 and 30
then '20-30'
else
'20以下'
end,
b.*
from
Guest_info a inner join
Or_info b
on
a.GuestID = b.GuestID) c
group by c.age
- Sql Server分组查询示例
- 求助!sql server 分组查询
- sql server模糊查询、分组
- SQL Server示例查询
- SQL Server示例查询
- sql server ROW_NUMBER (Transact-SQL) 分组查询
- Sql Server子查询、GROUP BY分组、having查询
- Sql Server子查询、GROUP BY分组、having查询
- sql server 2005 的递归查询示例
- SQL Server跨实例查询示例
- Sql Server 参数化查询示例
- SQL Server学习笔记(一)【认识SQL Server查询及分组嵌套查询】
- sql分组查询问题
- sql查询-分组
- sql分组查询
- SQL中的分组查询
- SQL分组查询
- sql 分组查询实例
- 数据库sql的查询统计问题 用存储过程实现;
- wget 的使用
- 关于分组统计
- sql分组统计语句
- 学习 REST
- Sql Server分组查询示例
- 实现按部门月卡余额总额分组统计的SQL查询代码
- group by分组的应用
- sql server数据分组统计
- 动态分组查询
- 提供一个不错的免费日历控件
- 2006-12-12 了解知识ip地址与子网掩码
- [ASP.NET2.0]無法開啟報表12/12
- SQL中char、varchar、text和nchar、nvarchar、ntext的区别