我想知道为什么 CREATE TABLE TEST AS SELECT (SELECT DISTINCT HM FROM A, B WHERE A.ID=B.ID) HM FROM A,B 这样错,错在哪了?
记得这样: CREATE TABLE2 AS SELECT DISTINCT (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 就可以啊 为什么楼上的不行呢? 请赐教,给分咯
SELECT DISTINCT HM FROM A, B WHERE A.ID=B.ID 假如它的结果是一张表tbl 你select distinct 接的列名,不是表名。你可以用 select hm from (SELECT DISTINCT HM FROM A, B WHERE A.ID=B.ID) t
SELECT DISTINCT HM FROM A, B WHERE A.ID=B.ID 它可是有两列呀!
SELECT DISTINCT HM FROM A, B WHERE A.ID=B.ID它的结果的确是一张表tbl但是对“你select distinct 接的列名,不是表名”这句话不理解?
为什么说有两列?HM字段只存在表A里
我知道了 CREATE TABLE2 AS SELECT DISTINCT (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 因为SELECT COUNT(DISTINCT CUST_ID) From TABLE1只有一行。 SELECT SUM(FEE1)+SUM(FEE2) From TABLE1 WHERE TYPE='01'也只有一行。 SELECT SUM(FEE1)+SUM(FEE2) From TABLE1 WHERE TYPE='02'只有一行 。 但是 SELECT DISTINCT HM FROM A, B WHERE A.ID=B.ID它还回多行
就是这样,不用那么麻烦了。 create table test as SELECT DISTINCT HM FROM A, B WHERE A.ID=B.ID
就是要实现类似下面的: CREATE TABLE TEST AS SELECT (SELECT DISTINCT HM FROM A, B WHERE A.ID=B.ID) HM (SELECT SUM(FEE) FROM A,B WHERE A.ID=B.ID) FEE FROM A,B GROUP BY HM
CREATE TABLE TEST AS select hm,sum(fee) fee from (select hm,fee from A, B WHERE A.ID=B.ID ) t group by hm
简单一点 CREATE TABLE TEST AS select hm,sum(fee) fee from A, B WHERE A.ID=B.ID group by hm
上面是where条件一样的情况,那要是where条件不一样呢 CREATE TABLE TEST AS SELECT (SELECT DISTINCT HM FROM A, B WHERE A.ID=B.ID) HM (SELECT SUM(FEE1) FROM A,B WHERE A.ID=B.ID AND TYPE=01) FEE1, (SELECT SUM(FEE2) FROM A,B WHERE A.ID=B.ID AND TYPE=02) FEE2, (SELECT SUM(FEE3) FROM A,B WHERE A.ID=B.ID AND TYPE=03) FEE3, FROM A,B GROUP BY HM
CREATE TABLE TEST AS SELECT DISTINCT HM, (SELECT SUM(FEE1) FROM A,B WHERE A.ID=B.ID AND TYPE=01) fee1, (SELECT SUM(FEE2) FROM A,B WHERE A.ID=B.ID AND TYPE=02) fee2, (SELECT SUM(FEE3) FROM A,B WHERE A.ID=B.ID AND TYPE=03) FEE3 FROM A, B WHERE A.ID=B.ID
按照上面会报错:not a GROUP BY expression
CREATE TABLE TEST AS SELECT DISTINCT HM, (SELECT SUM(FEE1) FROM A,B WHERE A.ID=B.ID AND TYPE=01) fee1, (SELECT SUM(FEE2) FROM A,B WHERE A.ID=B.ID AND TYPE=02) fee2, (SELECT SUM(FEE3) FROM A,B WHERE A.ID=B.ID AND TYPE=03) FEE3 FROM A, B WHERE A.ID=B.ID GROUP BY HM 会报错:not a GROUP BY expression去掉后 CREATE TABLE TEST AS SELECT DISTINCT HM, (SELECT SUM(FEE1) FROM A,B WHERE A.ID=B.ID AND TYPE=01) fee1, (SELECT SUM(FEE2) FROM A,B WHERE A.ID=B.ID AND TYPE=02) fee2, (SELECT SUM(FEE3) FROM A,B WHERE A.ID=B.ID AND TYPE=03) FEE3 FROM A, B WHERE A.ID=B.ID 报错:single-row subquery return more than one row
要加GROUP BY HM, 但是加了后, CREATE TABLE TEST AS SELECT DISTINCT HM, (SELECT SUM(FEE1) FROM A,B WHERE A.ID=B.ID AND TYPE=01) fee1, (SELECT SUM(FEE2) FROM A,B WHERE A.ID=B.ID AND TYPE=02) fee2, (SELECT SUM(FEE3) FROM A,B WHERE A.ID=B.ID AND TYPE=03) FEE3 FROM A, B WHERE A.ID=B.ID GROUP BY HM 会报错:not a GROUP BY expression
CREATE TABLE TEST AS select hm, max(decode(type,'01',fee)), max(decode(type,'02',fee)), max(decode(type,'03',fee)) from (select hm,sum(fee) fee,type fee from A, B WHERE A.ID=B.ID group by hm,type) t
错了 CREATE TABLE TEST AS select hm, max(decode(type,'01',fee)), max(decode(type,'02',fee)), max(decode(type,'03',fee)) from (select hm,sum(fee) fee,type fee from A, B WHERE A.ID=B.ID group by hm,type) t group by hm关机睡觉去了````````````````````````````````````````````````````
CREATE TABLE TEST
AS SELECT
(SELECT DISTINCT HM
FROM A, B
WHERE A.ID=B.ID) HM
FROM A,B
这样错,错在哪了?
CREATE TABLE2
AS SELECT DISTINCT
(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
就可以啊
为什么楼上的不行呢?
请赐教,给分咯
FROM A, B
WHERE A.ID=B.ID
假如它的结果是一张表tbl
你select distinct 接的列名,不是表名。你可以用
select hm from
(SELECT DISTINCT HM
FROM A, B
WHERE A.ID=B.ID) t
FROM A, B
WHERE A.ID=B.ID
它可是有两列呀!
FROM A, B
WHERE A.ID=B.ID它的结果的确是一张表tbl但是对“你select distinct 接的列名,不是表名”这句话不理解?
CREATE TABLE2
AS SELECT DISTINCT
(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
因为SELECT COUNT(DISTINCT CUST_ID) From TABLE1只有一行。
SELECT SUM(FEE1)+SUM(FEE2) From TABLE1 WHERE TYPE='01'也只有一行。
SELECT SUM(FEE1)+SUM(FEE2) From TABLE1 WHERE TYPE='02'只有一行 。
但是
SELECT DISTINCT HM
FROM A, B
WHERE A.ID=B.ID它还回多行
create table test
as
SELECT DISTINCT HM
FROM A, B
WHERE A.ID=B.ID
CREATE TABLE TEST
AS SELECT
(SELECT DISTINCT HM
FROM A, B
WHERE A.ID=B.ID) HM
(SELECT SUM(FEE) FROM A,B WHERE A.ID=B.ID) FEE
FROM A,B
GROUP BY HM
AS
select hm,sum(fee) fee
from
(select hm,fee
from A, B
WHERE A.ID=B.ID
) t group by hm
CREATE TABLE TEST
AS
select hm,sum(fee) fee
from A, B
WHERE A.ID=B.ID
group by hm
CREATE TABLE TEST
AS SELECT
(SELECT DISTINCT HM FROM A, B WHERE A.ID=B.ID) HM
(SELECT SUM(FEE1) FROM A,B WHERE A.ID=B.ID AND TYPE=01) FEE1,
(SELECT SUM(FEE2) FROM A,B WHERE A.ID=B.ID AND TYPE=02) FEE2,
(SELECT SUM(FEE3) FROM A,B WHERE A.ID=B.ID AND TYPE=03) FEE3,
FROM A,B
GROUP BY HM
AS
SELECT DISTINCT HM,
(SELECT SUM(FEE1) FROM A,B WHERE A.ID=B.ID AND TYPE=01) fee1,
(SELECT SUM(FEE2) FROM A,B WHERE A.ID=B.ID AND TYPE=02) fee2,
(SELECT SUM(FEE3) FROM A,B WHERE A.ID=B.ID AND TYPE=03) FEE3
FROM A, B WHERE A.ID=B.ID
AS
SELECT DISTINCT HM,
(SELECT SUM(FEE1) FROM A,B WHERE A.ID=B.ID AND TYPE=01) fee1,
(SELECT SUM(FEE2) FROM A,B WHERE A.ID=B.ID AND TYPE=02) fee2,
(SELECT SUM(FEE3) FROM A,B WHERE A.ID=B.ID AND TYPE=03) FEE3
FROM A, B WHERE A.ID=B.ID
GROUP BY HM
会报错:not a GROUP BY expression去掉后
CREATE TABLE TEST
AS
SELECT DISTINCT HM,
(SELECT SUM(FEE1) FROM A,B WHERE A.ID=B.ID AND TYPE=01) fee1,
(SELECT SUM(FEE2) FROM A,B WHERE A.ID=B.ID AND TYPE=02) fee2,
(SELECT SUM(FEE3) FROM A,B WHERE A.ID=B.ID AND TYPE=03) FEE3
FROM A, B WHERE A.ID=B.ID
报错:single-row subquery return more than one row
而是type=01所有的费用,
type=02所有的费用,
type=03所有的费用,
但是加了后,
CREATE TABLE TEST
AS
SELECT DISTINCT HM,
(SELECT SUM(FEE1) FROM A,B WHERE A.ID=B.ID AND TYPE=01) fee1,
(SELECT SUM(FEE2) FROM A,B WHERE A.ID=B.ID AND TYPE=02) fee2,
(SELECT SUM(FEE3) FROM A,B WHERE A.ID=B.ID AND TYPE=03) FEE3
FROM A, B WHERE A.ID=B.ID
GROUP BY HM
会报错:not a GROUP BY expression
?
AS
select hm,
max(decode(type,'01',fee)),
max(decode(type,'02',fee)),
max(decode(type,'03',fee))
from
(select hm,sum(fee) fee,type fee
from A, B
WHERE A.ID=B.ID
group by hm,type) t
CREATE TABLE TEST
AS
select hm,
max(decode(type,'01',fee)),
max(decode(type,'02',fee)),
max(decode(type,'03',fee))
from
(select hm,sum(fee) fee,type fee
from A, B
WHERE A.ID=B.ID
group by hm,type) t
group by hm关机睡觉去了````````````````````````````````````````````````````