CREATE TABLE2
AS SELECT
COUNT(DISTINCT CUST_ID) USER_NUM,
SUM(FEE1)+SUM(FEE2) ONE_ALL WHERE TYPE='01',
SUM(FEE1)+SUM(FEE2) TWO_ALL WHERE TYPE='02'--問題出在WHERE上
FROM TABLE1分步插入就可以了
AS SELECT
COUNT(DISTINCT CUST_ID) USER_NUM,
SUM(FEE1)+SUM(FEE2) ONE_ALL WHERE TYPE='01',
SUM(FEE1)+SUM(FEE2) TWO_ALL WHERE TYPE='02'--問題出在WHERE上
FROM TABLE1分步插入就可以了
CREATE TABLE2
AS SELECT
COUNT(DISTINCT CUST_ID) USER_NUM,
SUM(FEE1)+SUM(FEE2) ONE_ALL WHERE TYPE='01'
FROM TABLE1然后
ALTER TABLE2
ADD SELECT SUM(FEE1)+SUM(FEE2) TWO_ALL FROM TABLE1 WHERE TYPE='02'是这样嘛?
AS SELECT
COUNT(DISTINCT CUST_ID) USER_NUM,
(select SUM(FEE1)+SUM(FEE2) ONE_ALL From Table1 WHERE TYPE='01'),
(select SUM(FEE1)+SUM(FEE2) TWO_ALL From Table1 WHERE TYPE='02')
FROM TABLE1好久没用ORACLE了,看看这样可以吗?好像应该可以
AS SELECT
COUNT(DISTINCT CUST_ID) USER_NUM,
(select SUM(FEE1)+SUM(FEE2) From Table1 WHERE TYPE='01') ONE_ALL,
(select SUM(FEE1)+SUM(FEE2) From Table1 WHERE TYPE='02') TWO_ALL
FROM TABLE1
AS SELECT
(SELECT COUNT(DISTINCT CUST_ID) From TABLE1) USER_NUM,
(SELECT SUM(FEE1)+SUM(FEE2) From TABLE1 WHERE TYPE='01') ONE_ALL,
(SELECT SUM(FEE1)+SUM(FEE2) From TABLE1 WHERE TYPE='02') TWO_ALL
FROM TABLE1
这样可以创建表,并且应该只有一条记录才对,但是我看了创建的表,TABLE2的记录数和TABLE1的记录数一样多,这是为什么?
AS SELECT
COUNT(DISTINCT CUST_ID) USER_NUM,
SUM(decode(type,'01',FEE1+FEE2)),
SUM(decode(type,'02',FEE1+FEE2))
FROM TABLE1
AS SELECT
(SELECT COUNT(DISTINCT CUST_ID) From TABLE1) USER_NUM,
(SELECT SUM(FEE1)+SUM(FEE2) From TABLE1 WHERE TYPE='01') ONE_ALL,
(SELECT SUM(FEE1)+SUM(FEE2) From TABLE1 WHERE TYPE='02') TWO_ALL
FROM dual
疏忽了一些东西
CREATE TABLE TABLE2
AS SELECT DIATINCT
(SELECT COUNT(DISTINCT CUST_ID) FROM TABLE1) USER_NUM,
(SELECT SUM(FEE1)+SUM(FEE2) FROM TABLE1 WHERE TYPE='01') ONE_ALL,
(SELECT SUM(FEE1)+SUM(FEE2) FROM TABLE1 WHERE TYPE='02') TWO_ALL
FROM TABLE1;
AS SELECT
(SELECT COUNT(DISTINCT CUST_ID) From TABLE1) USER_NUM, --单纪录
(SELECT SUM(FEE1)+SUM(FEE2) From TABLE1 WHERE TYPE='01') ONE_ALL, --单纪录
(SELECT SUM(FEE1)+SUM(FEE2) From TABLE1 WHERE TYPE='02') TWO_ALL --单纪录
FROM dual 缺省表
应该可以
table2的记录数就和table1的记录数一样多?TO gf8205075() :为什么FROM DUAL 可以?
FROM TABLE1 还是要把所有行都选出来的