这个select 应该怎么写呀,请大侠指点呀,在线等待,急!!!!

handyworkroom 2003-08-20 08:38:15
guest表
guest_id,open_time,close_time(如果此客人为离开,此值为空),op_id
pay表
pay_id,guest_id,account_type(付款类型,有现金,支票,卡等),money,op_id

我想写一个select,按op_id进行分组,显示如下信息
已离人数,未离人数,现金,支票,卡,。。。
操作员1(op_id) XX XX XX XX XX XX
操作员2(op_id) XX XX XX XX XX XX
...全文
110 4 打赏 收藏 转发到动态 举报
写回复
用AI写文章
4 条回复
切换为时间正序
请发表友善的回复…
发表回复
dreamboy527 2003-08-21
  • 打赏
  • 举报
回复
select a.op_id as 操作员op_id,
(select count(*) from guest where close_time isnull and op_id=a.op_id) as 已离人数,
(select count(*) from guest where close_time not isnull and op_id=a.op_id) as 未离人数,
sum(case a.account_type when '现金' then a.account_type) as 现金,
sum(case a.account_type when '支票' then a.account_type) as 支票,
sum(case a.account_type when '卡' then a.account_type) as 卡,
from pay a group by op_id
Simple945 2003-08-21
  • 打赏
  • 举报
回复
select op_id,
(select count(guest_id) from guest where op_id=pay.op_id and close_time is not null) as 已离人数,
(select count(*) from guest where op_id=pay.op_id and close_time is null)as 未离人数,
sum(case pay.account_type when '现金' then money else 0 end) as 现金,
sum(case pay.account_type when '支票' then money else 0 end) as 支票,
sum(case pay.account_type when '卡' then money else 0 end) as 卡
from pay
group by op_id


lifeforu 2003-08-20
  • 打赏
  • 举报
回复
select a.op_id
,已离=(select count(1) from guest where op_id=a.op_id and close_time is null)
,未离=(select count(1) from guest where op_id=a.op_id and close_time is not null)
,现金=(select sum(money) from pay where op_id = a.op_id and account_type='现金')
,支票=(select sum(money) from pay where op_id = a.op_id and account_type='支票')
,卡=(select sum(money) from pay where op_id = a.op_id and account_type='卡')
from pay a group by a.op_id

基本实现,方法不好
handyworkroom 2003-08-20
  • 打赏
  • 举报
回复
guest表
insert into guest values('00001','2003-8-20 8:30','2003-8-20 8:45','TEST')
insert into guest values('00002','2003-8-20 8:33',null,'TEST')
insert into guest values('00003','2003-8-20 8:56','2003-8-20 9:20','OP')
pay表
insert into pay values('0000001','00001','现金',20,'TEST')
insert into pay values('0000002','00003','支票',100,'TEST')
insert into pay values('0000003','00001','卡',60,'OP')
insert into pay values('0000004','00002','现金',120,'TEST')

34,575

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧