急急急:请教高手关于sql语句的问题.请进...(200分)

小

小鱼儿歪歪

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2001-11-26

• #1

请教：如何把一个表中不同行但相同列的数据并为一条记录。
如： A1 A2 A3
站1 柴油 200
站1 汽油 300
通过sql语句把站1的柴油和汽油的销售量并为一行。
上例并为
站1 200 300[][]

 

小

小鱼儿歪歪

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2001-11-26

• #2

有位网友告诉我如下代码，我执行以后不行，大家来帮忙看看吧。
SELECT
DISTINCT A.Station,
pOil1=(SELECT ACount FROM OILS b WHERE b.Station=A.Statios AND b.Fuel='材油'),
pOil2=(SELECT ACount FROM OILS b WHERE b.Station=A.Statios AND b.Fuel='汽油')
FROM
OILS A

 

Z

zhihuali

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2001-11-26

• #3

SELECT A=A1,B=SUM(A3),C=0 INTO #TABLE1 FROM TABLE WHERE A2=柴油
INSERT INTO #TABLE1 SELECT A=A1,B=0,C=SUM(A3) FROM TABLE WHERE A2=汽油
SELECT * FROM #TABLE1

 

D

dongberlin

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2001-11-26

• #4

SELECT
DISTINCT A.Station,
pOil1=(SELECT sum(a3) FROM OILS b
WHERE b.Station=A.Statios AND b.Fuel='材油'
group by a1, a2),
pOil2=(SELECT sum(a3) FROM OILS b
WHERE b.Station=A.Statios AND b.Fuel='汽油'
group by a1, a2)
FROM
OILS A

 

Y

Yang J.Q.

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2001-11-26

• #5

SELECT A.A1, B.A3, C.A3
FROM YourTable A,
(SELECT A1, SUM(A3) A3 FROM YourTable WHERE A2='材油' GROUP BY A1) B,
(SELECT A1, SUM(A3) A3 FROM YourTable WHERE A2='汽油' GROUP BY A1) C
WHERE (A.A1 = B.A1(+))
AND (A.A1 = C.A1(+))
-- 以上为Oracle写法，若为MS-SQL或Access，则替换为相应的外联结方式即可。

 

小

小鱼儿歪歪

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2001-11-26

• #6

我看不到你们的答复，怎么办？？？？？？？？？？？？？？

 

酷

酷尔贝塔

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2001-11-26

• #7

[blue]SELECT A=A1,B=SUM(A3),C=0 INTO #TABLE1 FROM TABLE WHERE A2=柴油
INSERT INTO #TABLE1 SELECT A=A1,B=0,C=SUM(A3) FROM TABLE WHERE A2=汽油
SELECT * FROM #TABLE1[/blue]

 

酷

酷尔贝塔

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2001-11-26

• #8

[red]为会什么会看不到呢？？？是不是你进的版本不对呀。[/red][]

 

S

skyhorse_zjj

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2001-11-26

• #9

!@#$@$@$@$@

 

S

skeleton

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2001-11-27

• #10

select A1,sum(A2),sum(A3) from
(select A1, A3 = A2,0 = A3 from OILS as A
where A.A2 = '柴油'
union all
select A1, 0 = A2, A3 from OILS as A
where A.A2 = '汽油')
group by A1;

这个办法不用临时表，没有插入操作，肯定很快

 

Z

zh_2001

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2001-11-27

• #11

有收获

 

N

nsj

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2001-11-27

• #12

select station_id,sum(case nam when '汽油' then qy else 0 end) as qycoun,
sum(case nam when '柴油' then qy else 0 end) as cycoun
from tmp_table
group by station_id