create table test
as
SELECT DISTINCT HM
FROM A, B
WHERE A.ID=B.ID
as
SELECT DISTINCT HM
FROM A, B
WHERE A.ID=B.ID
解决方案 »
- DB2 SQLSTATE 57055: 带有足够页大小的临时表空间不可用。
- oracle numer 精度,小数位的关系
- cognos配置问题
- 有谁用过Oracle的OEM工具进行过备份(不是用Exp/IEM命令)
- 存储过程能否实现exp,imp的功能?
- oracle数据库启动问题!!!急急急!
- ORACLE数据库字符集问题,紧急
- 请问Oracle中 DATE 型字段的怎么输入啊?
- 可以在自己的程序中对远程的服务名进行配置么?就相net8那样
- sqlldr中导excel表date格式的数据如何导入
- 求助:有上百万条的数据需导出到另外的数据库里.怎样做最好?
- 紧急求救:修改归档日志目标位置,数据库无法启动了!!!
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关机睡觉去了````````````````````````````````````````````````````