思路是这样的。 CREATE TABLE OILS ( Station varchar(100), Fuel Varchar(100), ACount INT ) SELECT pStation=A.Station, pOil1=(SELECT b.ACount FROM OILS b WHERE b.Station=A.Statios AND b.Fuel='材油') pOil2=(SELECT b.ACount FROM OILS b WHERE b.Station=A.Statios AND b.Fuel='汽油') ... FROM OILS A
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
select A1 as 站台, sum(case when A2='柴油' then A3 else 0 end) as 柴油,sum(case when A2='汽油' then A3 else 0 end) as 汽油 From Table Group by A1
用kals这个语句就可以啦!还要什么方法呢?
這是交叉表的典型﹐如果是ACCESS等支持交叉表的資料庫﹐可以用Transfer 語句實現
用自身连接查询 eg; create table T (A1, A2, A3 ) select first.A1,first.A3,second.A3 from T first,T second where first.A1 = second.A2
在oracle(access)中,可以用decode()函数实现,方法同 kals(卡斯)
我建议你使用Access的交叉表查询向导,应该很快解决
qflxh的查询是否应该这样写select first.A1,first.A3,second.A3 from T first,T second where first.A1 = second.A1 and first.a2=" 柴油" and second.a2="汽油"
用kals(卡斯)的方法吧,易理解和维护。
select A1 , sum(case when A2 = 柴油 then A3 else 0 end ) as 柴油, sum(case when A2 = 汽油 then A3 else 0 end ) as 汽油 from temp group by A1,A3:)看看吧,只要你能列举 油的种类就可以了
gongjan(gong)的办法不错, 这是SQL Server数据库生成交叉透视表的经典方法。
select (tableA.a+table.B)as c from ....
交叉表,Access中有现成的command,其他的只能通过建表实现了。
select a1, Sum(a2) as a2cou , sum(a3) as a3cou from tablename where a1='站1'
select 站名,SUM(柴油)AS 柴油,SUM(汽油) AS 汽油 FROM (SELECT A1 AS 站名, (CASE WHEN A2 = '柴油' THEN A3 ELSE 0 END) AS 柴油, (CASE WHEN A2 = '汽油' THEN A3 ELSE 0 END) AS 汽油 FROM table1) derivedtbl GROUP BY 站名--Table1为原始表名;在SQL2000中调试通过
SELECT A1, SUM(CASE WHEN A2 = '柴油' THEN A3 ELSE 0 END) AS 柴油, SUM(CASE WHEN A2 = '汽油' THEN A3 ELSE 0 END) AS 汽油 FROM TABLE1 GROUP BY A1
select A1 , 柴油=when A2 = 柴油 then A3 else 0 end , 汽油=when A2 = 汽油 then A3 else 0 end from temp
我建议你可以在sql写存储过程,使用分组,提取数据,再合并数据,这样就分成灵活,可以实现你要的功能
如: A1 A2 A3 站1 柴油 200 站1 汽油 300 通过sql语句把站1的柴油和汽油的销售量并为一行。 上例并为 站1 200 300太简单不过的一个问题:select a.A1, a.A3 as '才油价格', b.a3 as '汽油价格' FROM SOURCETABLE a, SOURCETABLE b WHERE a.A1 = b.A1 and a.A2 = '柴油' and b.A2 = '汽油'如果有其他的条件,都写到WHERE条件中,防止产生交叉联接结果集。
我认为正要使用交叉联接结果集,这样栏目中才能出现不定数量的产品名称 sql语句为 TRANSFORM Avg(A3) AS AvgA3 SELECT A1 FROM YourTable GROUP BY A1 PIVOT A2;当然函数Avg可以根据需要选择Max,Min,Sum等
union连接不行吗??
CREATE TABLE OILS
( Station varchar(100),
Fuel Varchar(100),
ACount INT
)
SELECT
pStation=A.Station,
pOil1=(SELECT b.ACount FROM OILS b WHERE b.Station=A.Statios AND b.Fuel='材油')
pOil2=(SELECT b.ACount FROM OILS b WHERE b.Station=A.Statios AND b.Fuel='汽油')
...
FROM
OILS A
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
过会儿送分感谢。
[email protected]
我现在要走了。中午我查收邮件,晚上在给你答案。
eg;
create table T
(A1,
A2,
A3
)
select first.A1,first.A3,second.A3
from T first,T second
where first.A1 = second.A2
from T first,T second
where first.A1 = second.A1 and first.a2=" 柴油" and second.a2="汽油"
sum(case when A2 = 柴油 then A3 else 0 end ) as 柴油,
sum(case when A2 = 汽油 then A3 else 0 end ) as 汽油
from temp
group by A1,A3:)看看吧,只要你能列举 油的种类就可以了
(CASE WHEN A2 = '汽油' THEN A3 ELSE 0 END) AS 汽油
FROM table1) derivedtbl GROUP BY 站名--Table1为原始表名;在SQL2000中调试通过
SUM(CASE WHEN A2 = '汽油' THEN A3 ELSE 0 END) AS 汽油
FROM TABLE1
GROUP BY A1
柴油=when A2 = 柴油 then A3 else 0 end ,
汽油=when A2 = 汽油 then A3 else 0 end
from temp
站1 柴油 200
站1 汽油 300
通过sql语句把站1的柴油和汽油的销售量并为一行。
上例并为
站1 200 300太简单不过的一个问题:select a.A1, a.A3 as '才油价格', b.a3 as '汽油价格' FROM SOURCETABLE a, SOURCETABLE b WHERE a.A1 = b.A1 and a.A2 = '柴油' and b.A2 = '汽油'如果有其他的条件,都写到WHERE条件中,防止产生交叉联接结果集。
sql语句为
TRANSFORM Avg(A3) AS AvgA3
SELECT A1
FROM YourTable
GROUP BY A1
PIVOT A2;当然函数Avg可以根据需要选择Max,Min,Sum等