select sum(MONEY_PAY),count(1) from SALE group by roll_up(vip_code)不知道符合你的需求不
select sum(money_pay),sum(1) from sale t where s_date>='01-nov-11' and s_date<='30-nov-11';这个是求总的销售金额和笔数; SUM(MONEY_PAY) SUM(1) -------------- ---------- 19420581.2 28657 select sum(money_pay),sum(1) from sale t where s_date>='01-nov-11' and s_date<='30-nov-11' and t.vip_code is not null;(这个是会员的销售和笔数) SUM(MONEY_PAY) SUM(1) -------------- ---------- 9289248.9 10766select sum(money_pay),sum(1) from sale t where s_date>='01-nov-11' and s_date<='30-nov-11' and t.vip_code is null;(这个是非会员的销售和笔数) SUM(MONEY_PAY) SUM(1) -------------- ---------- 10131332.3 17891 需要用1条语句吧这6个查询结果都表示出来
select sum(decode(vip_code, null, 0, money_pay)) as 非会员收入, sum(decode(vip_code, null, 0, 1)) as 非会员人数, sum(decode(vip_code, null, money_pay, 0)) as 会员收入, sum(decode(vip_code, null, 1, 0)) as 会员人数, sum(money_pay) as 总收入, count(1) as 总人数 from sale t where s_date >= '01-nov-11' and s_date <= '30-nov-11'
select a.rs 总人数,a.pay 总支付,b.rs 会员人数,b.pay 会员支付,c.rs 非会员,c.pay 非会员支付 from (select sum(money_pay)pay,sum(1)rs from sale t where s_date>='01-nov-11' and s_date<='30-nov-11')a, (select sum(money_pay)pay,sum(1)rs from sale t where s_date>='01-nov-11' and s_date<='30-nov-11' and t.vip_code is not null)b, (select sum(money_pay)pay,sum(1)rs from sale t where s_date>='01-nov-11' and s_date<='30-nov-11' and t.vip_code is null)c注:三个子句都是单条记录,所以可以放心连接,不用加任何条件,只要把三条记录合在一起就行了。 另:加中文列名,是为了方便看,其实在实际中还是少用为好.如果还有什么问题,可以找我。
S_DATE DATE, 销售日期
S_TIME NUMBER(4), 销售时间(以小时分钟计)
E_CODE CHAR(6), 收银员编号
MONEY_TOTAL NUMBER(12,4), 商品总金额
DISC_RATE NUMBER(3), 折扣率
DISC_NET NUMBER(10,4), 折让额
DISCOUNT_PZR CHAR(6), 折扣批准人
MONEY_PAY NUMBER(12,4), 实付总金额
VIP_CODE CHAR(20), VIP编码
FLAG CHAR(1), 记帐标志 '1'
TRANS_FLAG CHAR(1), 记帐标志
v_flag char(1), 记vipmxz 标志
select ....from sale
UNION ALL
select ....from sale
UNION ALL
select ....from sale;
SELECT SUM(MONEY_PAY) "销售金额",
SUM(1) " 销售笔数",
SUM(DECODE(V_FLAG, '1', MONEY_PAY, 0)) "VIP销售金额",
SUM(DECODE(V_FLAG, '1', 1, 0)) "VIP销售笔数",
SUM(DECODE(V_FLAG, '1', 0, MONEY_PAY)) "非会员销售金额",
SUM(DECODE(V_FLAG, '1', 0, 1)) "非会员销售笔额",
FROM SALE
WHERE S_DATE >= TO_DATE('2011-11-01 00:00:00','YYYY-MM-DD HH24:MI:SS')
AND S_DATE < TO_DATE('2011-12-01 00:00:00','YYYY-MM-DD HH24:MI:SS') ;
INDATE DATE 消费日期
INDATE_JZ DATE 记账日期
SPNM CHAR(6) 商品编码
RULE_CODE CHAR(4) 积分规则代码
MD_CODE CHAR(4) 门店代码
S_NO NUMBER(4)
VIP_CODE CHAR(16) 会员卡号
VALUE_BQ NUMBER(12,2) 本期积分
MONEY_BQ NUMBER(12,2) 本期消费
SUM(MONEY_PAY) SUM(1)
-------------- ----------
19420581.2 28657 select sum(money_pay),sum(1) from sale t where s_date>='01-nov-11' and s_date<='30-nov-11' and t.vip_code is not null;(这个是会员的销售和笔数)
SUM(MONEY_PAY) SUM(1)
-------------- ----------
9289248.9 10766select sum(money_pay),sum(1) from sale t where s_date>='01-nov-11' and s_date<='30-nov-11' and t.vip_code is null;(这个是非会员的销售和笔数)
SUM(MONEY_PAY) SUM(1)
-------------- ----------
10131332.3 17891
需要用1条语句吧这6个查询结果都表示出来
sum(decode(vip_code, null, 0, 1)) as 非会员人数,
sum(decode(vip_code, null, money_pay, 0)) as 会员收入,
sum(decode(vip_code, null, 1, 0)) as 会员人数,
sum(money_pay) as 总收入,
count(1) as 总人数
from sale t
where s_date >= '01-nov-11'
and s_date <= '30-nov-11'
select a.rs 总人数,a.pay 总支付,b.rs 会员人数,b.pay 会员支付,c.rs 非会员,c.pay 非会员支付 from
(select sum(money_pay)pay,sum(1)rs from sale t where s_date>='01-nov-11' and s_date<='30-nov-11')a,
(select sum(money_pay)pay,sum(1)rs from sale t where s_date>='01-nov-11' and s_date<='30-nov-11' and t.vip_code is not null)b,
(select sum(money_pay)pay,sum(1)rs from sale t where s_date>='01-nov-11' and s_date<='30-nov-11' and t.vip_code is null)c注:三个子句都是单条记录,所以可以放心连接,不用加任何条件,只要把三条记录合在一起就行了。
另:加中文列名,是为了方便看,其实在实际中还是少用为好.如果还有什么问题,可以找我。