user表userid nick
-----------------------------------------
item表:(商品表)item_id item_title(商品名) nick(用户的昵称,关联字段)
-----------------------------------------
order表:(销售表)nick item_id price payment(销售额) date
-----------------------------------------
pagecount表:(流量表,统计每件商品的浏览次数)userid item_id pvcount(浏览次数) date
-----------------------------------------将7日内成交次数最多的商品列举出来表内容:ID,商品名,浏览次数,商品价格,成交次数,总成交金额
-----------------------------------------
item表:(商品表)item_id item_title(商品名) nick(用户的昵称,关联字段)
-----------------------------------------
order表:(销售表)nick item_id price payment(销售额) date
-----------------------------------------
pagecount表:(流量表,统计每件商品的浏览次数)userid item_id pvcount(浏览次数) date
-----------------------------------------将7日内成交次数最多的商品列举出来表内容:ID,商品名,浏览次数,商品价格,成交次数,总成交金额
..select i.item_title[商品名], p.pvcount[浏览次数], o.price[商品价格] ,
count(o.payment)[成交次数],sum(o.payment)[成交总金额]
from item i
left join pagecount p
left join order o
on i.item_id = p.item_id
,i.item_id = o.item_id
order by o.payment
123 zs
-----------------------------------------
item表:(商品表) item_id item_title(商品名) nick(用户的昵称,关联字段)
aaa 充值卡 zs
bbb 手机 zs
-----------------------------------------
order表:(销售表) nick item_id price num(销售数) payment(销售额) date
zs aaa 5.00 3 15 2009-9-1
zs bbb 10.00 1 10 2009-8-29 -----------------------------------------
pagecount表:(流量表,统计每件商品的浏览次数) userid item_id pvcount(浏览次数) date
123 aaa 20 2009-9-1
123 bbb 8 2009-8-31
----------------------------------------- 将7日内成交次数最多的商品列举出来 表内容:ID,商品名,浏览次数,商品价格,成交次数,总成交金额
SELECT C.item_title AS 商品名,
B.pvcount AS 浏览次数,
A.price AS 商品价格,
A.paycount AS 成交次数,
A.payment AS 总成交金额
FROM (SELECT item_id,price,SUM(payment) AS payment,COUNT(*) AS paycount
FROM [order]
WHERE date BETWEEN DATEADD(day,-7,GETDATE()) AND GETDATE()) AS A
JOIN (SELECT item_id,SUM(pvcount) AS pvcount FROM pagecount
WHERE date BETWEEN DATEADD(day,-7,GETDATE()) AND GETDATE()
GROUP BY item_id) AS B
ON A.item_id = B.itemid
JOIN item AS C
ON A.item_id = C.item_id
ORDER BY A.payment DESC;
(select
a.userid,
b.item_title,
d.pvcount,
c.price,
成交次数=sum(c.payment)/c.price,
sum(payment) as 总成交金额
from
user表 a,item表 b,order表 c,pagecount表 d
where
a.userid=d.userid
and
b.nick=c.nick
and
b.item_id=d.item_id
group by
a.userid,
b.item_title,
d.pvcount,
c.price)t
where
datediff(dd,开始时间,结束时间)=7
我汗
select max(t.成交次数) from
(select
a.userid,
b.item_title,
d.pvcount,
c.price,
c.成交次数
sum(payment) as 总成交金额
from
user表 a,item表 b,order表 c,pagecount表 d
where
a.userid=d.userid
and
b.nick=c.nick
and
b.item_id=d.item_id
group by
a.userid,
b.item_title,
d.pvcount,
c.price
c.成交次数)t
where
datediff(dd,开始时间,结束时间)=7
虽然能出来数据 id title pv price num payment
5906168 促销-09主流款热卖软PU篮球送球针、网袋,更多搭配请自选 17 39.00 2 96.00但是我要的是交易次数最多的数据,num应该是6
declare @user table( userid varchar(20) , nick varchar(20))insert into @user values( 123,'nick')declare @item table( item_id varchar(20), item_title varchar(20), nick varchar(20))insert into @item values( 'aaa','充值卡','zs')
insert into @item values( 'bbb','手机','zs')declare @order table(nick varchar(20), item_id varchar(20),price decimal(10,2),num int,payment decimal(10,2),[date] datetime)
insert into @order values( 'ZS','aaa',5.00,3,15,'2009-9-1')
insert into @order values( 'ZS','bbb',10.00,1,10,'2009-8-29')declare @pagecount table( userid varchar(20), item_id varchar(20), pvcount int ,[date] datetime)insert into @pagecount values( '123','aaa',20,'2009-9-1')
insert into @pagecount values( '123','bbb',20,'2009-8-29')
select A.item_id, B.item_title,
(select sum(pvcount) from @pagecount where DATEDIFF( day , [date] ,getDate()) between 0 and 7 and item_id=A.item_id) as pvcount,
A.price,A.countn,A.payment
from(
select top 1 item_id,sum(payment) as payment, count(*) as countn,avg(price) as price from @order where DATEDIFF( day , [date] ,getDate()) between 0 and 7
group by item_id
order by countn desc )A
inner join @item B on B.item_id=A.item_id