求一条Sql语句~，再线等~~，解决立即给分

[mnsiii]

有如下两个表

dechead 主表
copentryno, ieflag, ieport ,ddate....

declist 明细表
copentryno, qty, usdprice,assgs...

dechead.copentryno = declist.copentryno
ieflag 只有两种取值‘I’‘E’

现在统计每一天的ieport的 ieflag = 'I' 的 qty*usdprice 和 ieflag = ‘E’的Qty* usdprice
（就是根据ieprot和ddate分组）

要求一条Sql语句搞定。。不能写成两条。。多谢

[wwh999]

你貼點數據出來,方便測試..

[LouisXIV]

--try


select
ieprot,
ddate,
sum(case when ieflag='I' then qty*usdprice else 0 end ) as I,
sum(case when ieflag='E' then qty*usdprice else 0 end ) as E
from dechead a inner join declist b
on a.copentryno=b.copentryno

[fcuandy]

SELECT CONVERT(VARCHAR(10),ddate,120),
ieflagI=SUM(CASE ieflag WHEN 'I' THEN qty*usdprice ELSE 0 END),
ieflagE=SUM(CASE ieflag WHEN 'E' THEN qty*usdprice ELSE 0 END)
FROM dechead a
INNER JOIN declist b
ON a.copentryno = b.copentryno
GROUP BY CONVERT(VARCHAR(10),ddate,120)

这是一天对应两个字段。

如果按ddate,ieflag分组，来统计 I, E的量，那么

SELECT CONVERT(VARCHAR(10),ddate,120),SUM(qty*usdprice),ieflag
FROM dechead a
INNER JOIN declist b
ON a.copentryno = b.copentryno
WHERE ieflag IN ('I','E')
GROUP BY CONVERT(VARCHAR(10),ddate,120),ieflag

可能有手误.

[LouisXIV]

--modified

select
ieprot,
ddate,
sum(case when ieflag='I' then qty*usdprice else 0 end ) as I,
sum(case when ieflag='E' then qty*usdprice else 0 end ) as E
from dechead a inner join declist b
on a.copentryno=b.copentryno
group by
ieprot,
ddate

[wwh999]

靠,樓上的兩位真是搶分搶瘋了...不頂了

[mnsiii]

from dechead a inner join declist b
on a.copentryno=b.copentryno

跟这个

from dechead a ，declist b
where a.copentryno=b.copentryno

有何不同。。。有人回答马上结贴

[feitian_BOBO]

一个连接,一个没有连接....哈哈!!!

[LouisXIV]

from dechead a inner join declist b
on a.copentryno=b.copentryno

跟这个

from dechead a ，declist b
where a.copentryno=b.copentryno
---------------------------------

equal