表1:卡操作记录表(CardOper)
字段:操作类型(OperType)0代表正常,1代表充值,2代表挂失,3代表解挂;
卡号(CardNo);现金收费(Money);银行卡收费(Bank);赠送(Send);备注(Re)表2:消费记录表(GoodsOper)
字段:操作类型(ConsType)1代表购买,2代表退货
卡号(CardNo);现金收费(Money);银行卡收费(Bank);会员卡收费(Card);备注(Re)需要得到查询结果
操作 卡号 现金 银行卡 会员卡 赠送 汇总 备注
充值 1001 10 10 0 10 30
购买 1002 20 0 10 0 30
挂失 1001 0 0 0 0 0
退货 1002 -10 0 -10 0 -20
解挂 1003 0 0 0 0 0
购买 1001 10 10 0 0 20 SQL语句应该怎么写,往指教~
字段:操作类型(OperType)0代表正常,1代表充值,2代表挂失,3代表解挂;
卡号(CardNo);现金收费(Money);银行卡收费(Bank);赠送(Send);备注(Re)表2:消费记录表(GoodsOper)
字段:操作类型(ConsType)1代表购买,2代表退货
卡号(CardNo);现金收费(Money);银行卡收费(Bank);会员卡收费(Card);备注(Re)需要得到查询结果
操作 卡号 现金 银行卡 会员卡 赠送 汇总 备注
充值 1001 10 10 0 10 30
购买 1002 20 0 10 0 30
挂失 1001 0 0 0 0 0
退货 1002 -10 0 -10 0 -20
解挂 1003 0 0 0 0 0
购买 1001 10 10 0 0 20 SQL语句应该怎么写,往指教~
case when 操作类型 ='充值'
case when 操作类型 ='购买'
case when 操作类型 ='挂失'
case when 操作类型 ='退货'
case when 操作类型 ='解挂'
case when 操作类型 ='购买'
(
select OperType,CardNo,sum([Money]) [Money],sum(Bank) Bank,sum([Send]) [Send],sum(Card) Card
from(
select OperType,CardNo,[Money],Bank,[Send],0 as Card
from CardOper
union all
select (case when ConsType = 1 then 4 when ConsType = 2 then 5 end),CardNo,
(case when ConsType = 2 then -[Money] else [Money] end),
(case when ConsType = 2 then -Bank else Bank end),0,
(case when ConsType = 2 then -Card else Card end)
from GoodsOper
) t
group by OperType,CardNo
)select (case OperType when 1 then '充值' when 2 then '挂失' when 3 then '解挂'
when 4 then '购买' when 5 then '退货' end) [操作],
CardNo [卡号],[Money] [现金],Bank [银行卡],CardNo [会员卡],[Send] [赠送],
([Money]+Bank+[Send]+Card) as [汇总]
from ach
union all
select case 操作类型 when 1 then '购买' when 2 then 退货 end as 操作类型,CardNo,Money,Bank,Card,0,Re from GoodsOper
from CardOper
union all
select case 操作类型 when 1 then '购买' when 2 then 退货 end as 操作类型,CardNo,Money,Bank,Card,0,Re,([Money]+Bank+[Send]+Card) as [汇总]
from GoodsOper
select
(case when OperType=1 then '充值'
when OperType=2 then '挂失'
when OperType=3 then '解挂'
when constype=11 then '购买'
when constype=12 then '退货'
end) as 操作,
cardno as 卡号,
money as 现金,
bank as 银行卡,
card as 会员卡,
send as 赠送,
cardno+money+bank+card+send as 汇总
re as 备注
from
(
select opertype,cardno,money,bank,send,0 as card,re from cardoper
union all
select constype+10,cardno,a.money,a.bank,a.send,0 from goodsopen
)t
select
(case when OperType=1 then '充值'
when OperType=2 then '挂失'
when OperType=3 then '解挂'
when constype=11 then '购买'
when constype=12 then '退货'
end) as 操作,
cardno as 卡号,
money as 现金,
bank as 银行卡,
card as 会员卡,
send as 赠送,
cardno+money+bank+card+send as 汇总,
re as 备注
from
(
select opertype,cardno,money,bank,send,0 as card,re from cardoper
union all
select constype+10,cardno,a.money,a.bank,a.send,0 from goodsopen
)t
(
select OperType,CardNo,sum([Money]) [Money],sum(Bank) Bank,sum([Send]) [Send],sum(Card) Card
from(
select OperType,CardNo,[Money],Bank,[Send],0 as Card
from CardOper
union all
select (case when ConsType = 1 then 4 when ConsType = 2 then 5 end),CardNo,
(case when ConsType = 2 then -[Money] else [Money] end),
(case when ConsType = 2 then -Bank else Bank end),0,
(case when ConsType = 2 then -Card else Card end)
from GoodsOper
) t
group by OperType,CardNo
)select (case OperType when 1 then '充值' when 2 then '挂失' when 3 then '解挂'
when 4 then N'购买' when 5 then N'退货' end) [操作],
CardNo [卡号],(Case OperType when 5 then '-'+Cast([Money] as varchar(20)) else [Money] end ) [现金],
(Case OperType when 5 then '-'+Cast([Bank] as varchar(20)) else [Bank] end ) [银行卡],
(Case OperType when 5 then '-'+Cast([Card] as varchar(20)) else [Card] end ) [会员],
(Case OperType when 5 then '-'+Cast([Send] as varchar(20)) else [Send] end ) [赠送],
(Case OperType when 5 then '-'+Cast(([Money]+Bank+[Send]+Card) as varchar(20)) else ([Money]+Bank+[Send]+Card) end ) as [汇总]
from ach改进了下楼上的
select
(case when OperType=1 then '充值'
when OperType=2 then '挂失'
when OperType=3 then '解挂'
when OperType=11 then '购买'
when OperType=12 then '退货'
end) as 操作,
cardno as 卡号,
money as 现金,
bank as 银行卡,
card as 会员卡,
send as 赠送,
cardno+money+bank+card+send as 汇总,
re as 备注
from
(
select opertype,cardno,money,bank,send,0 as card,re from cardoper
union all
select constype+10,cardno,a.money,a.bank,a.send,0 from goodsopen
)t
再增加个表
表3:员工表(employees)
字段:员工编号(empID),员工名(Name)刚才表1和表2增加两个字段 empID,empID2。 当然表1和表2的empID,empID2=表3的empID查询结果
操作 卡号 现金 银行卡 会员卡 赠送 汇总 备注 指导员 操作员
充值 1001 10 10 0 10 30 张三 张三
购买 1002 20 0 10 0 30 李四 张三
挂失 1001 0 0 0 0 0 李四 李四
退货 1002 -10 0 -10 0 -20 张三 李四
解挂 1003 0 0 0 0 0 0 李四 李四这个又该怎么写呢