34,837
社区成员




CREATE table Card(日期 DATETIME, 卡号 CHAR(10), 姓名 CHAR(20))
CREATE table chuqiao(日期 DATETIME, 卡号 CHAR(10), 存款金额 INT)
CREATE table xf(日期 DATETIME, 卡号 CHAR(10), 消费产品 CHAR(10), 结帐方式 CHAR(10), 数量 INT, 单价 INT, 金额 INT)
INSERT INTO Card VALUES('2008-2-9','392332', 'Wangxiaoyan')
INSERT INTO Card VALUES('2008-2-11','444330', 'Wangmss')
INSERT INTO Card VALUES('2008-2-15','392411', 'Guomoruo')
INSERT INTO Card VALUES('2008-2-18','392833', 'Lidake')
INSERT INTO Card VALUES('2008-2-27','492129', 'Wangyiguang')
INSERT INTO Card VALUES('2008-2-27','623356', 'Dengzhuzhu')
INSERT INTO chuqiao VALUES('2008-2-11','392332', 300)
INSERT INTO chuqiao VALUES('2008-2-15','392332', 300)
INSERT INTO chuqiao VALUES('2008-2-15','392411', 150)
INSERT INTO chuqiao VALUES('2008-2-16','392332', 100)
INSERT INTO chuqiao VALUES('2008-2-20','392411', 450)
INSERT INTO chuqiao VALUES('2008-2-21','392823', 100)
INSERT INTO chuqiao VALUES('2008-2-27','392129', 400)
INSERT INTO chuqiao VALUES('2008-2-27','392332', 120)
INSERT INTO xf VALUES('2008-2-9', '392233', 'AAA','现金', 1,12,12)
INSERT INTO xf VALUES('2008-2-11', '392233', 'BBB','现金', 1,12,12)
INSERT INTO xf VALUES('2008-2-15', '392233', 'AAA','扣存款', 1,12,12)
INSERT INTO xf VALUES('2008-2-15', '------', 'BBB','现金', 1,30,30)
INSERT INTO xf VALUES('2008-2-16', '392411', 'BBB','扣存款', 2,5,10)
INSERT INTO xf VALUES('2008-2-16', '392332', 'AAA','扣存款', 2,12,24)
INSERT INTO xf VALUES('2008-2-20', '------', 'DDD','现金', 1,30,30)
INSERT INTO xf VALUES('2008-2-23', '------', 'DDD','现金', 1,30,30)
INSERT INTO xf VALUES('2008-2-23', '392233', 'CCC','信用卡', 3,10,30)
INSERT INTO xf VALUES('2008-2-25', '392233', 'CCC','扣存款', 3,10,30)
INSERT INTO xf VALUES('2008-2-27', '392233', 'EEE','现金', 9,1,9)
INSERT INTO xf VALUES('2008-2-27', '392233', 'AAA','现金', 1,11,11)
INSERT INTO xf VALUES('2008-2-27', '392233', 'AAA','扣存款', 1,11,11)
INSERT INTO xf VALUES('2008-2-27', '392233', 'BBB','现金', 3,5,18)
INSERT INTO xf VALUES('2008-2-27', '392233', 'BBB','信用卡', 2,5,10)
select t0.dt, t1.开卡数 ,t2.存款数 ,t2.存款金额 ,
t3.消费单数,t3.消费金额,(t2.存款金额+t3.现金结帐金额) as 当天全部现金,
t3.现金结帐数,t3.现金结帐金额,t3.扣存款结帐数,t3.扣存款结帐金额,t3.信用卡结帐数,t3.信用卡结帐金额,
t3.AAA消费数量,t3.AAA消费金额,t3.BBB消费数量,t3.BBB消费金额,t3.全部产品数,t3.全部消费金额
from --这里的t1.*,t2.*,t3.*要去掉dt,自己把他写全。
(
select convert(varchar(10),日期,120) dt from card
union
select convert(varchar(10),日期,120) from chuqiao
union
select convert(varchar(10),日期,120) from xf
) t0
left join
(
select dt = convert(varchar(10),日期,120) , 开卡数 = count(*) from card group by convert(varchar(10),日期,120)
) t1 on t0.dt=t1.dt
left join
(
select dt = convert(varchar(10),日期,120) , 存款数 = count(*) , 存款金额 = sum(存款金额) from chuqiao group by convert(varchar(10),日期,120)
) t2 on t0.dt = t2.dt
left join
(
select dt = convert(varchar(10),日期,120) , 消费单数 = count(*) , 消费金额 = sum(金额) ,
现金结帐数 = sum(case 结帐方式 when '现金' then 1 else 0 end),
现金结帐金额 = sum(case 结帐方式 when '现金' then 金额 else 0 end),
扣存款结帐数 = sum(case 结帐方式 when '扣存款' then 1 else 0 end),
扣存款结帐金额 = sum(case 结帐方式 when '扣存款' then 金额 else 0 end),
信用卡结帐数 = sum(case 结帐方式 when '信用卡' then 1 else 0 end),
信用卡结帐金额 = sum(case 结帐方式 when '信用卡' then 金额 else 0 end),
AAA消费数量 = sum(case 消费产品 when 'AAA' then 1 else 0 end),
AAA消费金额 = sum(case 结帐方式 when 'AAA' then 金额 else 0 end),
BBB消费数量 = sum(case 消费产品 when 'BBB' then 1 else 0 end),
BBB消费金额 = sum(case 结帐方式 when 'BBB' then 金额 else 0 end),
全部产品数 = sum(数量),
全部消费金额 = sum(金额)
from xf group by convert(varchar(10),日期,120)
) t3 on t0.dt = t3.dt ORDER BY t0.dt
/*
dt 开卡数 存款数 存款金额 消费单数 消费金额 当天全部现金 现金结帐数 现金结帐金额 扣存款结帐数 扣存款结帐金额 信用卡结帐数 信用卡结帐金额 AAA消费数量 AAA消费金额 BBB消费数量 BBB消费金额 全部产品数 全部消费金额
---------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
2008-02-09 1 NULL NULL 1 12 NULL 1 12 0 0 0 0 1 0 0 0 1 12
2008-02-11 1 1 300 1 12 312 1 12 0 0 0 0 0 0 1 0 1 12
2008-02-15 1 2 450 2 42 480 1 30 1 12 0 0 1 0 1 0 2 42
2008-02-16 NULL 1 100 2 34 100 0 0 2 34 0 0 1 0 1 0 4 34
2008-02-18 1 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
2008-02-20 NULL 1 450 1 30 480 1 30 0 0 0 0 0 0 0 0 1 30
2008-02-21 NULL 1 100 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
2008-02-23 NULL NULL NULL 2 60 NULL 1 30 0 0 1 30 0 0 0 0 4 60
2008-02-25 NULL NULL NULL 1 30 NULL 0 0 1 30 0 0 0 0 0 0 3 30
2008-02-27 2 2 520 5 59 558 3 38 1 11 1 10 2 0 2 0 16 59
*/
drop table card,chuqiao,xf
CREATE table Card(日期 DATETIME, 卡号 CHAR(10), 姓名 CHAR(20))
CREATE table chuqiao(日期 DATETIME, 卡号 CHAR(10), 存款金额 INT)
CREATE table xf(日期 DATETIME, 卡号 CHAR(10), 消费产品 CHAR(10), 结帐方式 CHAR(10), 数量 INT, 单价 INT, 金额 INT)
INSERT INTO Card VALUES('2008-2-9','392332', 'Wangxiaoyan')
INSERT INTO Card VALUES('2008-2-11','444330', 'Wangmss')
INSERT INTO Card VALUES('2008-2-15','392411', 'Guomoruo')
INSERT INTO Card VALUES('2008-2-18','392833', 'Lidake')
INSERT INTO Card VALUES('2008-2-27','492129', 'Wangyiguang')
INSERT INTO Card VALUES('2008-2-27','623356', 'Dengzhuzhu')
INSERT INTO chuqiao VALUES('2008-2-11','392332', 300)
INSERT INTO chuqiao VALUES('2008-2-15','392332', 300)
INSERT INTO chuqiao VALUES('2008-2-15','392411', 150)
INSERT INTO chuqiao VALUES('2008-2-16','392332', 100)
INSERT INTO chuqiao VALUES('2008-2-20','392411', 450)
INSERT INTO chuqiao VALUES('2008-2-21','392823', 100)
INSERT INTO chuqiao VALUES('2008-2-27','392129', 400)
INSERT INTO chuqiao VALUES('2008-2-27','392332', 120)
INSERT INTO xf VALUES('2008-2-9', '392233', 'AAA','现金', 1,12,12)
INSERT INTO xf VALUES('2008-2-11', '392233', 'BBB','现金', 1,12,12)
INSERT INTO xf VALUES('2008-2-15', '392233', 'AAA','扣存款', 1,12,12)
INSERT INTO xf VALUES('2008-2-15', '------', 'BBB','现金', 1,30,30)
INSERT INTO xf VALUES('2008-2-16', '392411', 'BBB','扣存款', 2,5,10)
INSERT INTO xf VALUES('2008-2-16', '392332', 'AAA','扣存款', 2,12,24)
INSERT INTO xf VALUES('2008-2-20', '------', 'DDD','现金', 1,30,30)
INSERT INTO xf VALUES('2008-2-23', '------', 'DDD','现金', 1,30,30)
INSERT INTO xf VALUES('2008-2-23', '392233', 'CCC','信用卡', 3,10,30)
INSERT INTO xf VALUES('2008-2-25', '392233', 'CCC','扣存款', 3,10,30)
INSERT INTO xf VALUES('2008-2-27', '392233', 'EEE','现金', 9,1,9)
INSERT INTO xf VALUES('2008-2-27', '392233', 'AAA','现金', 1,11,11)
INSERT INTO xf VALUES('2008-2-27', '392233', 'AAA','扣存款', 1,11,11)
INSERT INTO xf VALUES('2008-2-27', '392233', 'BBB','现金', 3,5,18)
INSERT INTO xf VALUES('2008-2-27', '392233', 'BBB','信用卡', 2,5,10)
select isnull(m.dt , t3.dt) dt , m.开卡数 ,m.存款数 ,m.存款金额,
t3.消费单数,t3.消费金额,(m.存款金额+t3.现金结帐金额) as 当天全部现金,
t3.现金结帐数,t3.现金结帐金额,t3.扣存款结帐数,t3.扣存款结帐金额,t3.信用卡结帐数,t3.信用卡结帐金额,
t3.AAA消费数量,t3.AAA消费金额,t3.BBB消费数量,t3.BBB消费金额,t3.全部产品数,t3.全部消费金额
from
(
select isnull(t1.dt,t2.dt) dt , t1.开卡数 ,t2.存款数 ,t2.存款金额 from
(
select dt = convert(varchar(10),日期,120) , 开卡数 = count(*) from card group by convert(varchar(10),日期,120)
) t1
full join
(
select dt = convert(varchar(10),日期,120) , 存款数 = count(*) , 存款金额 = sum(存款金额) from chuqiao group by convert(varchar(10),日期,120)
) t2 on t1.dt = t2.dt
) m
full join
(
select dt = convert(varchar(10),日期,120) , 消费单数 = count(*) , 消费金额 = sum(金额) ,
现金结帐数 = sum(case 结帐方式 when '现金' then 1 else 0 end),
现金结帐金额 = sum(case 结帐方式 when '现金' then 金额 else 0 end),
扣存款结帐数 = sum(case 结帐方式 when '扣存款' then 1 else 0 end),
扣存款结帐金额 = sum(case 结帐方式 when '扣存款' then 金额 else 0 end),
信用卡结帐数 = sum(case 结帐方式 when '信用卡' then 1 else 0 end),
信用卡结帐金额 = sum(case 结帐方式 when '信用卡' then 金额 else 0 end),
AAA消费数量 = sum(case 消费产品 when 'AAA' then 1 else 0 end),
AAA消费金额 = sum(case 结帐方式 when 'AAA' then 金额 else 0 end),
BBB消费数量 = sum(case 消费产品 when 'BBB' then 1 else 0 end),
BBB消费金额 = sum(case 结帐方式 when 'BBB' then 金额 else 0 end),
全部产品数 = sum(数量),
全部消费金额 = sum(金额)
from xf group by convert(varchar(10),日期,120)
) t3
on m.dt = t3.dt ORDER BY dt
drop table card,chuqiao,xf
/*
dt 开卡数 存款数 存款金额 消费单数 消费金额 当天全部现金 现金结帐数 现金结帐金额 扣存款结帐数 扣存款结帐金额 信用卡结帐数 信用卡结帐金额 AAA消费数量 AAA消费金额 BBB消费数量 BBB消费金额 全部产品数 全部消费金额
---------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
2008-02-09 1 NULL NULL 1 12 NULL 1 12 0 0 0 0 1 0 0 0 1 12
2008-02-11 1 1 300 1 12 312 1 12 0 0 0 0 0 0 1 0 1 12
2008-02-15 1 2 450 2 42 480 1 30 1 12 0 0 1 0 1 0 2 42
2008-02-16 NULL 1 100 2 34 100 0 0 2 34 0 0 1 0 1 0 4 34
2008-02-18 1 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
2008-02-20 NULL 1 450 1 30 480 1 30 0 0 0 0 0 0 0 0 1 30
2008-02-21 NULL 1 100 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
2008-02-23 NULL NULL NULL 2 60 NULL 1 30 0 0 1 30 0 0 0 0 4 60
2008-02-25 NULL NULL NULL 1 30 NULL 0 0 1 30 0 0 0 0 0 0 3 30
2008-02-27 2 2 520 5 59 558 3 38 1 11 1 10 2 0 2 0 16 59
(所影响的行数为 10 行)
*/
--不好意思,把最后的t2.dt = t3.dt 该为 t1.dt = t3.dt.
CREATE table Card(日期 DATETIME, 卡号 CHAR(10), 姓名 CHAR(20))
CREATE table chuqiao(日期 DATETIME, 卡号 CHAR(10), 存款金额 INT)
CREATE table xf(日期 DATETIME, 卡号 CHAR(10), 消费产品 CHAR(10), 结帐方式 CHAR(10), 数量 INT, 单价 INT, 金额 INT)
INSERT INTO Card VALUES('2008-2-9','392332', 'Wangxiaoyan')
INSERT INTO Card VALUES('2008-2-11','444330', 'Wangmss')
INSERT INTO Card VALUES('2008-2-15','392411', 'Guomoruo')
INSERT INTO Card VALUES('2008-2-18','392833', 'Lidake')
INSERT INTO Card VALUES('2008-2-27','492129', 'Wangyiguang')
INSERT INTO Card VALUES('2008-2-27','623356', 'Dengzhuzhu')
INSERT INTO chuqiao VALUES('2008-2-11','392332', 300)
INSERT INTO chuqiao VALUES('2008-2-15','392332', 300)
INSERT INTO chuqiao VALUES('2008-2-15','392411', 150)
INSERT INTO chuqiao VALUES('2008-2-16','392332', 100)
INSERT INTO chuqiao VALUES('2008-2-20','392411', 450)
INSERT INTO chuqiao VALUES('2008-2-21','392823', 100)
INSERT INTO chuqiao VALUES('2008-2-27','392129', 400)
INSERT INTO chuqiao VALUES('2008-2-27','392332', 120)
INSERT INTO xf VALUES('2008-2-9', '392233', 'AAA','现金', 1,12,12)
INSERT INTO xf VALUES('2008-2-11', '392233', 'BBB','现金', 1,12,12)
INSERT INTO xf VALUES('2008-2-15', '392233', 'AAA','扣存款', 1,12,12)
INSERT INTO xf VALUES('2008-2-15', '------', 'BBB','现金', 1,30,30)
INSERT INTO xf VALUES('2008-2-16', '392411', 'BBB','扣存款', 2,5,10)
INSERT INTO xf VALUES('2008-2-16', '392332', 'AAA','扣存款', 2,12,24)
INSERT INTO xf VALUES('2008-2-20', '------', 'DDD','现金', 1,30,30)
INSERT INTO xf VALUES('2008-2-23', '------', 'DDD','现金', 1,30,30)
INSERT INTO xf VALUES('2008-2-23', '392233', 'CCC','信用卡', 3,10,30)
INSERT INTO xf VALUES('2008-2-25', '392233', 'CCC','扣存款', 3,10,30)
INSERT INTO xf VALUES('2008-2-27', '392233', 'EEE','现金', 9,1,9)
INSERT INTO xf VALUES('2008-2-27', '392233', 'AAA','现金', 1,11,11)
INSERT INTO xf VALUES('2008-2-27', '392233', 'AAA','扣存款', 1,11,11)
INSERT INTO xf VALUES('2008-2-27', '392233', 'BBB','现金', 3,5,18)
INSERT INTO xf VALUES('2008-2-27', '392233', 'BBB','信用卡', 2,5,10)
select isnull(isnull(t1.dt,t2.dt),t3.dt) dt , t1.开卡数 ,t2.存款数 ,t2.存款金额 ,
t3.消费单数,t3.消费金额,(t2.存款金额+t3.现金结帐金额) as 当天全部现金,
t3.现金结帐数,t3.现金结帐金额,t3.扣存款结帐数,t3.扣存款结帐金额,t3.信用卡结帐数,t3.信用卡结帐金额,
t3.AAA消费数量,t3.AAA消费金额,t3.BBB消费数量,t3.BBB消费金额,t3.全部产品数,t3.全部消费金额
from --这里的t1.*,t2.*,t3.*要去掉dt,自己把他写全。
(
select dt = convert(varchar(10),日期,120) , 开卡数 = count(*) from card group by convert(varchar(10),日期,120)
) t1
full join
(
select dt = convert(varchar(10),日期,120) , 存款数 = count(*) , 存款金额 = sum(存款金额) from chuqiao group by convert(varchar(10),日期,120)
) t2 on t1.dt = t2.dt
full join
(
select dt = convert(varchar(10),日期,120) , 消费单数 = count(*) , 消费金额 = sum(金额) ,
现金结帐数 = sum(case 结帐方式 when '现金' then 1 else 0 end),
现金结帐金额 = sum(case 结帐方式 when '现金' then 金额 else 0 end),
扣存款结帐数 = sum(case 结帐方式 when '扣存款' then 1 else 0 end),
扣存款结帐金额 = sum(case 结帐方式 when '扣存款' then 金额 else 0 end),
信用卡结帐数 = sum(case 结帐方式 when '信用卡' then 1 else 0 end),
信用卡结帐金额 = sum(case 结帐方式 when '信用卡' then 金额 else 0 end),
AAA消费数量 = sum(case 消费产品 when 'AAA' then 1 else 0 end),
AAA消费金额 = sum(case 结帐方式 when 'AAA' then 金额 else 0 end),
BBB消费数量 = sum(case 消费产品 when 'BBB' then 1 else 0 end),
BBB消费金额 = sum(case 结帐方式 when 'BBB' then 金额 else 0 end),
全部产品数 = sum(数量),
全部消费金额 = sum(金额)
from xf group by convert(varchar(10),日期,120)
) t3 on t1.dt = t3.dt ORDER BY dt
drop table card,chuqiao,xf
/*
dt 开卡数 存款数 存款金额 消费单数 消费金额 当天全部现金 现金结帐数 现金结帐金额 扣存款结帐数 扣存款结帐金额 信用卡结帐数 信用卡结帐金额 AAA消费数量 AAA消费金额 BBB消费数量 BBB消费金额 全部产品数 全部消费金额
---------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
2008-02-09 1 NULL NULL 1 12 NULL 1 12 0 0 0 0 1 0 0 0 1 12
2008-02-11 1 1 300 1 12 312 1 12 0 0 0 0 0 0 1 0 1 12
2008-02-15 1 2 450 2 42 480 1 30 1 12 0 0 1 0 1 0 2 42
2008-02-16 NULL 1 100 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
2008-02-16 NULL NULL NULL 2 34 NULL 0 0 2 34 0 0 1 0 1 0 4 34
2008-02-18 1 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
2008-02-20 NULL 1 450 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
2008-02-20 NULL NULL NULL 1 30 NULL 1 30 0 0 0 0 0 0 0 0 1 30
2008-02-21 NULL 1 100 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
2008-02-23 NULL NULL NULL 2 60 NULL 1 30 0 0 1 30 0 0 0 0 4 60
2008-02-25 NULL NULL NULL 1 30 NULL 0 0 1 30 0 0 0 0 0 0 3 30
2008-02-27 2 2 520 5 59 558 3 38 1 11 1 10 2 0 2 0 16 59
(所影响的行数为 12 行)
*/