执行语句如下:
create table tmp(rq varchar(10),shengfu nchar(1))
insert into tmp(rq,shengfu) values('2005','win')
insert into tmp(rq,shengfu) values('2005','win')
insert into tmp(rq,shengfu) values('2006','lose')报错是抱在insert语句上的,不知道那里不对啊,谢谢大家
create table tmp(rq varchar(10),shengfu nchar(1))
insert into tmp(rq,shengfu) values('2005','win')
insert into tmp(rq,shengfu) values('2005','win')
insert into tmp(rq,shengfu) values('2006','lose')报错是抱在insert语句上的,不知道那里不对啊,谢谢大家
insert into tmp(rq,shengfu) values('2005','win');
insert into tmp(rq,shengfu) values('2005','win');
insert into tmp(rq,shengfu) values('2006','lose');
楼上的方法不对的,加了“;”会报错的 :invalid character我想问问,选中一条insert语句然后执行,不会报错;但是选中所有的insert语句执行就报错,有什么办法吗?如果是100条连续的insert语句,选中一条,执行一次太烦了
insert into tmp(rq,shengfu) values('2005','win');
insert into tmp(rq,shengfu) values('2006','lose');
end;那样会错是因为你的pl/sql的版本太低,或者是你的表结构有问题,你注意一下,我的语句中,表结果跟你是不一样的,你把你的表删除,再用我的语句执行一下。不行的话,用我这一段,加上begin....end
已经知道原表
year salary
------------------ ---------------------
2000 1000
2001 2000
2002 3000
2003 4000
显示查询结果
year salary
------------------ ---------------------
2000 1000
2001 3000
2002 6000
2003 10000
他的结果是:
select b.year,sum(a.salary)
from tmp a,tmp b
where a.year<=b.year
group by b.year能帮我解释下红色语句的作用,为什么要这样写啊 看不懂。。
b.year = 2002时,表a中 <= b.year的记录有2000,2001,2002年,则薪水总额为1000 + 2000 + 3000
year month amount
1991 1 1.1
1991 2 1.2
1991 3 1.3
1991 4 1.4
1992 1 2.1
1992 2 2.2
1992 3 2.3
1992 4 2.4
查成这样一个结果
year m1 m2 m3 m4
1991 1.1 1.2 1.3 1.4
1992 2.1 2.2 2.3 2.4
然后我month为整型,
SELECT YEAR,
SUM(CASE WHEN MONTH=1 THEN AMOUNT END)M1,
SUM(CASE WHEN MONTH=2 THEN AMOUNT END)M2,
SUM(CASE WHEN MONTH=3 THEN AMOUNT END)M3,
FROM tmp
GROUP BY YEAR
这么写,一直报错,说missing expression,但又不告诉我具体那里missing,怎么会呢?
这个答案是网上看到的,应该对的啊。
SUM(CASE WHEN MONTH=1 THEN AMOUNT END)M1,
SUM(CASE WHEN MONTH=2 THEN AMOUNT END)M2,
SUM(CASE WHEN MONTH=3 THEN AMOUNT END)M3 --,
FROM tmp
GROUP BY YEAR
create table tmp1 as(
select '2000' year,1000 salary from dual union
select '2001' year,2000 salary from dual union
select '2002' year,3000 salary from dual union
select '2003' year,4000 salary from dual );select year,sum(salary)over(order by year)salary from tmp1;YEAR SALARY
2000 1000
2001 3000
2002 6000
2003 10000
SUM(CASE WHEN MONTH=1 THEN AMOUNT END)M1,
SUM(CASE WHEN MONTH=2 THEN AMOUNT END)M2,
SUM(CASE WHEN MONTH=3 THEN AMOUNT END)M3,
FROM tmp
GROUP BY YEAR m3后面有个,却没有字段参数,所以会报错
你这里却了一个SUM(CASE WHEN MONTH=4 THEN AMOUNT END)M4
应该是这样
SELECT YEAR,
SUM(CASE WHEN MONTH=1 THEN AMOUNT END)M1,
SUM(CASE WHEN MONTH=2 THEN AMOUNT END)M2,
SUM(CASE WHEN MONTH=3 THEN AMOUNT END)M3,
SUM(CASE WHEN MONTH=4 THEN AMOUNT END)M4
FROM tmp
GROUP BY YEAR
不能不加sum()因为这是一个分组查询,必须有聚合函数
你先试试没有sum而且没有group by的结果
SELECT YEAR,
CASE WHEN MONTH=1 THEN AMOUNT END M1,
CASE WHEN MONTH=2 THEN AMOUNT END M2,
CASE WHEN MONTH=3 THEN AMOUNT END M3,
CASE WHEN MONTH=4 THEN AMOUNT END M4
FROM tmp 前面的那个查询是这个查询结果中,相同year中,同一列中的值的和
如果不用sum,换成max,取最大,结果也是一样的
http://topic.csdn.net/u/20090805/14/e063e49b-bd9d-4f4b-95c0-c81c2ed20592.html