销售表按年分隔为
shopping_07
shopping_08
shopping_09
有字段
orderid,username,addtime
订单ID,用户名,下订单时间销售明细表为
shopping_book_07
shopping_book_08
shopping_book_09
字段
orderid,shuming
订单ID,书名会员表为
users会员表中
有username,useremailusername与销售表中的username关联
销售表中的orderid与销售明细表中的orderid关联销售明细表中的书名为 "(二手)"开头的为二手书.(shuming like '(二手)%')现求:所有购买过二手书的会员的邮箱及会员最后一次购买时间,各年度消费额
shopping_07
shopping_08
shopping_09
有字段
orderid,username,addtime
订单ID,用户名,下订单时间销售明细表为
shopping_book_07
shopping_book_08
shopping_book_09
字段
orderid,shuming
订单ID,书名会员表为
users会员表中
有username,useremailusername与销售表中的username关联
销售表中的orderid与销售明细表中的orderid关联销售明细表中的书名为 "(二手)"开头的为二手书.(shuming like '(二手)%')现求:所有购买过二手书的会员的邮箱及会员最后一次购买时间,各年度消费额
解决方案 »
- 求一sql分类汇总语句。
- 拼接出现的问题!
- sqlserver中uvarchar用cast转换为varchar时无法显示中文
- 用户定义函数只返回单值,那我想返回多值怎么办?
- 请教一个练习中的存储过程
- 求一条sql语句
- Sql Server2005与Microsoft Visual studio的连接 太难了
- SQL 聚合语句问题 AVG
- SOS 函数sp_dropmessage sp_addmessage只能在英文版的SQL Server20000上用 ,我的服务器上装的是简体中文版的,怎么办啊
- 急!!!!!!!!!!!!!!!!!!!!!!!!!!!!
- 求一分类排序sql语句(group by, order by)
- 简单SQL的语句执行问题
shopping_07 对应 shopping_book_07
依次类推.
select username,useremail,下订单时间,
(select sum(金额好象没给) from shopping_07 z where a.用户名=z.用户名) shopping_07,
(select sum(金额好象没给) from shopping_08 z where a.用户名=z.用户名) shopping_08,
(select sum(金额好象没给) from shopping_09 z where a.用户名=z.用户名) shopping_09
from 会员表 a,(select * from shopping_07 union all select * from shopping_08 union all select * from shopping_09) b
where a.用户名=b.用户名
and exists (select 1 from (select * from shopping_book_07 union all select * from shopping_book_08 union all select * from shopping_book_09) c where b.订单ID=c.订单ID and c.shuming like '(二手)%')
order by 下订单时间 desc
join shoping_book7 b on a.orderid=a.orderid
join users c on a.username=c.usernamewhere b.shuming like '(二手)%'这是07 年度的消费额 其他年度的你自己组合一下
金额price,图书bookid二楼的虽然可以实现,但每个表都在2G以上,用union效率太低了吧..
(
select c.username,c.usermail from shopping_07 a join shopping_book_07 b on a.orderid = b.orderid join 会员表 c on a.username = c.username
where shuming like '(二手)%'
) t join shopping_07 tt on t.username = tt.username join 消费表 tt2 on tt.bookid = tt2.bookid
union all
select t.*,max(addtime),sum(tt2.price) from
(
select c.username,c.usermail from shopping_08 a join shopping_book_08 b on a.orderid = b.orderid join 会员表 c on a.username = c.username
where shuming like '(二手)%'
) t join shopping_08 tt on t.username = tt.username join 消费表 tt2 on tt.bookid = tt2.bookid
union all
select t.*,max(addtime),sum(tt2.price) from
(
select c.username,c.usermail from shopping_09 a join shopping_book_09 b on a.orderid = b.orderid join 会员表 c on a.username = c.username
where shuming like '(二手)%'
) t join shopping_09 tt on t.username = tt.username join 消费表 tt2 on tt.bookid = tt2.bookid