表t1 列:ID,Time(格式:2011-08-01),FL(分类:省内,省外),PZ(货物品种),JID .
表t2 列:JID,JE(金额),ZL(重量),DJ(单价)
我现在 要得到 一个月中每天 各个分类 每种货物品种 的总重,总金额,平均单价.
sql 如何写?
表t2 列:JID,JE(金额),ZL(重量),DJ(单价)
我现在 要得到 一个月中每天 各个分类 每种货物品种 的总重,总金额,平均单价.
sql 如何写?
select
t1.PZ,
convert(varchar(10),t1.[Time],120),
sum(JE) as JE,
sum(ZL) as ZL,
avg(DJ) as DJ
from t1
inner join t2 on t1.ID=t2.JID
group by t1.PZ,convert(varchar(10),t1.[Time],120)
t1.PZ,
convert(varchar(10),t1.[Time],120),
sum(JE) as JE,
sum(ZL) as ZL,
avg(DJ) as DJ
from t1
inner join t2 on t1.ID=t2.JID
where convert(varchar(7),[Time],120)='2011-05'
group by t1.PZ,convert(varchar(10),t1.[Time],120)
t1.PZ,
convert(varchar(10),t1.[Time],120) as time,
sum(JE) as JE,
sum(ZL) as ZL,
avg(DJ) as DJ
from
t1 , t2
where
t1.ID=t2.JID
and
datediff(mm,time,传入的时间)=0
group by
t1.PZ,convert(varchar(10),t1.[Time],120)
t1.PZ,
convert(varchar(10),t1.[Time],120) as time,
sum(JE) as JE,
sum(ZL) as ZL,
t1.FL
avg(DJ) as DJ
from
t1 , t2
where
t1.ID=t2.JID
and
datediff(mm,time,传入的时间)=0
group by
t1.PZ,convert(varchar(10),t1.[Time],120),t1.FL
t1.PZ,
convert(varchar(10),t1.[Time],120) as time,
sum(JE) as JE,
sum(ZL) as ZL,
t1.FL
avg(DJ) as DJ
from
t1 , t2
where
t1.ID=t2.JID
and
datediff(mm,time,传入的时间)=0
group by
t1.PZ,convert(varchar(10),t1.[Time],120),t1.FL