就是关联两张表查询,为了分别查处 一张的不同字段 且为了不出现重复记录用了以下语句是union,但查询速度巨慢,上来求助,散分求助
select count(*) as num from (select char(F1.createTime) as datatime,
FP.TCODE,
F1.enddate date,
'01',
'CNY',
F1.datarange,
F1.startdate,
F1.enddate,
round(F1.growth2/100, 4), --不同
'' as endflag
from lippervalue_his_1M F1
LEFT JOIN
(
SELECT TRADE_CODE, TRADE_CODE AS TCODE FROM FUND_PRODUCT WHERE DELETETIME IS NULL
UNION
SELECT TRADE_CODE, FUND_SECOND_CODE AS TCODE FROM FUND_PRODUCT WHERE DELETETIME IS NULL AND FUND_SECOND_CODE IS NOT NULL AND FUND_SECOND_CODE<>''
) AS FP
ON F1.trade_code=FP.TRADE_CODE
where F1.DELETETIME IS NULL AND FP.TCODE IS NOT NULL
AND F1.datarange in('1M','3M','6M','1Y','3Y','5Y','BY')
and F1.createtime>'2011-01-02-00.16.14.609000'
union
select char(F1.createTime) as datatime,
FP.TCODE, F1.enddate date,
'02',
'CNY',
F1.datarange,
F1.startdate,
F1.enddate,
F1.STDDEV2, --不同
'' as endflag
from lippervalue_his_1M F1
LEFT JOIN
(
SELECT TRADE_CODE, TRADE_CODE AS TCODE FROM FUND_PRODUCT WHERE DELETETIME IS NULL
union
SELECT TRADE_CODE, FUND_SECOND_CODE AS TCODE FROM FUND_PRODUCT WHERE DELETETIME IS NULL AND FUND_SECOND_CODE IS NOT NULL AND FUND_SECOND_CODE<>''
) AS FP
ON F1.trade_code=FP.TRADE_CODE where F1.DELETETIME IS NULL
AND FP.TCODE IS NOT NULL
AND F1.datarange in('1M','3M','6M','1Y','3Y','5Y','BY')
and F1.createtime>'2011-01-02-00.16.14.609000'
union
select
char(F1.createTime) as datatime,
FP.TCODE,
F1.enddate date,
'03', 'CNY', F1.datarange, F1.startdate, F1.enddate,
F1.YIELD2, --不同
'' as endflag from lippervalue_his_1M F1
LEFT JOIN
(
SELECT TRADE_CODE, TRADE_CODE AS TCODE FROM FUND_PRODUCT WHERE DELETETIME IS NULL union SELECT TRADE_CODE, FUND_SECOND_CODE AS TCODE FROM FUND_PRODUCT WHERE DELETETIME IS NULL AND FUND_SECOND_CODE IS NOT NULL AND FUND_SECOND_CODE<>'') AS FP ON F1.trade_code=FP.TRADE_CODE where F1.DELETETIME IS NULL AND FP.TCODE IS NOT NULL AND F1.datarange in('1M','3M','6M','1Y','3Y','5Y','BY'
) and F1.createtime>'2011-01-02-00.16.14.609000'
union
select char(F1.createTime) as datatime, FP.TCODE, F1.enddate date, '05', 'CNY', F1.datarange, F1.startdate, F1.enddate, F1.riskgrowth2, '' as endflag from lippervalue_his_1M F1 LEFT JOIN (SELECT TRADE_CODE, TRADE_CODE AS TCODE FROM FUND_PRODUCT WHERE DELETETIME IS NULL union SELECT TRADE_CODE, FUND_SECOND_CODE AS TCODE FROM FUND_PRODUCT WHERE DELETETIME IS NULL AND FUND_SECOND_CODE IS NOT NULL AND FUND_SECOND_CODE<>'') AS FP ON F1.trade_code=FP.TRADE_CODE where F1.DELETETIME IS NULL AND FP.TCODE IS NOT NULL AND F1.datarange in('1M','3M','6M','1Y','3Y','5Y','BY') and F1.createtime>'2011-01-02-00.16.14.609000'
union
select char(F1.createTime) as datatime, FP.TCODE, F1.enddate date, '06', 'CNY', F1.datarange, F1.startdate, F1.enddate,
F1.sharpe2, --不同
'' as endflag from lippervalue_his_1M F1 LEFT JOIN (SELECT TRADE_CODE, TRADE_CODE AS TCODE FROM FUND_PRODUCT WHERE DELETETIME IS NULL union SELECT TRADE_CODE, FUND_SECOND_CODE AS TCODE FROM FUND_PRODUCT WHERE DELETETIME IS NULL AND FUND_SECOND_CODE IS NOT NULL AND FUND_SECOND_CODE<>'') AS FP ON F1.trade_code=FP.TRADE_CODE where F1.DELETETIME IS NULL AND FP.TCODE IS NOT NULL AND F1.datarange in('1M','3M','6M','1Y','3Y','5Y','BY') and F1.createtime>'2011-01-02-00.16.14.609000'
)a
select count(*) as num from (select char(F1.createTime) as datatime,
FP.TCODE,
F1.enddate date,
'01',
'CNY',
F1.datarange,
F1.startdate,
F1.enddate,
round(F1.growth2/100, 4), --不同
'' as endflag
from lippervalue_his_1M F1
LEFT JOIN
(
SELECT TRADE_CODE, TRADE_CODE AS TCODE FROM FUND_PRODUCT WHERE DELETETIME IS NULL
UNION
SELECT TRADE_CODE, FUND_SECOND_CODE AS TCODE FROM FUND_PRODUCT WHERE DELETETIME IS NULL AND FUND_SECOND_CODE IS NOT NULL AND FUND_SECOND_CODE<>''
) AS FP
ON F1.trade_code=FP.TRADE_CODE
where F1.DELETETIME IS NULL AND FP.TCODE IS NOT NULL
AND F1.datarange in('1M','3M','6M','1Y','3Y','5Y','BY')
and F1.createtime>'2011-01-02-00.16.14.609000'
union
select char(F1.createTime) as datatime,
FP.TCODE, F1.enddate date,
'02',
'CNY',
F1.datarange,
F1.startdate,
F1.enddate,
F1.STDDEV2, --不同
'' as endflag
from lippervalue_his_1M F1
LEFT JOIN
(
SELECT TRADE_CODE, TRADE_CODE AS TCODE FROM FUND_PRODUCT WHERE DELETETIME IS NULL
union
SELECT TRADE_CODE, FUND_SECOND_CODE AS TCODE FROM FUND_PRODUCT WHERE DELETETIME IS NULL AND FUND_SECOND_CODE IS NOT NULL AND FUND_SECOND_CODE<>''
) AS FP
ON F1.trade_code=FP.TRADE_CODE where F1.DELETETIME IS NULL
AND FP.TCODE IS NOT NULL
AND F1.datarange in('1M','3M','6M','1Y','3Y','5Y','BY')
and F1.createtime>'2011-01-02-00.16.14.609000'
union
select
char(F1.createTime) as datatime,
FP.TCODE,
F1.enddate date,
'03', 'CNY', F1.datarange, F1.startdate, F1.enddate,
F1.YIELD2, --不同
'' as endflag from lippervalue_his_1M F1
LEFT JOIN
(
SELECT TRADE_CODE, TRADE_CODE AS TCODE FROM FUND_PRODUCT WHERE DELETETIME IS NULL union SELECT TRADE_CODE, FUND_SECOND_CODE AS TCODE FROM FUND_PRODUCT WHERE DELETETIME IS NULL AND FUND_SECOND_CODE IS NOT NULL AND FUND_SECOND_CODE<>'') AS FP ON F1.trade_code=FP.TRADE_CODE where F1.DELETETIME IS NULL AND FP.TCODE IS NOT NULL AND F1.datarange in('1M','3M','6M','1Y','3Y','5Y','BY'
) and F1.createtime>'2011-01-02-00.16.14.609000'
union
select char(F1.createTime) as datatime, FP.TCODE, F1.enddate date, '05', 'CNY', F1.datarange, F1.startdate, F1.enddate, F1.riskgrowth2, '' as endflag from lippervalue_his_1M F1 LEFT JOIN (SELECT TRADE_CODE, TRADE_CODE AS TCODE FROM FUND_PRODUCT WHERE DELETETIME IS NULL union SELECT TRADE_CODE, FUND_SECOND_CODE AS TCODE FROM FUND_PRODUCT WHERE DELETETIME IS NULL AND FUND_SECOND_CODE IS NOT NULL AND FUND_SECOND_CODE<>'') AS FP ON F1.trade_code=FP.TRADE_CODE where F1.DELETETIME IS NULL AND FP.TCODE IS NOT NULL AND F1.datarange in('1M','3M','6M','1Y','3Y','5Y','BY') and F1.createtime>'2011-01-02-00.16.14.609000'
union
select char(F1.createTime) as datatime, FP.TCODE, F1.enddate date, '06', 'CNY', F1.datarange, F1.startdate, F1.enddate,
F1.sharpe2, --不同
'' as endflag from lippervalue_his_1M F1 LEFT JOIN (SELECT TRADE_CODE, TRADE_CODE AS TCODE FROM FUND_PRODUCT WHERE DELETETIME IS NULL union SELECT TRADE_CODE, FUND_SECOND_CODE AS TCODE FROM FUND_PRODUCT WHERE DELETETIME IS NULL AND FUND_SECOND_CODE IS NOT NULL AND FUND_SECOND_CODE<>'') AS FP ON F1.trade_code=FP.TRADE_CODE where F1.DELETETIME IS NULL AND FP.TCODE IS NOT NULL AND F1.datarange in('1M','3M','6M','1Y','3Y','5Y','BY') and F1.createtime>'2011-01-02-00.16.14.609000'
)a
UNION
SELECT TRADE_CODE, FUND_SECOND_CODE AS TCODE FROM FUND_PRODUCT WHERE DELETETIME IS NULL AND FUND_SECOND_CODE IS NOT NULL AND FUND_SECOND_CODE<>''
先放在临时表中,后面再使用。另外union可以用union all代替,如果没有重复数据的话,有重复数据可以select (distinct *).
--dafasd
--adsfdasf--adsfdasf
再多问句,如果把外围的
select count(*) as num from ( )
这个计数的去掉,需要显示字段的该如何优化呢,谢谢了
UNION
SELECT TRADE_CODE, FUND_SECOND_CODE AS TCODE FROM FUND_PRODUCT WHERE DELETETIME IS NULL AND FUND_SECOND_CODE IS NOT NULL AND FUND_SECOND_CODE <>''
这句查询和SELECT TRADE_CODE, TRADE_CODE AS TCODE FROM FUND_PRODUCT WHERE DELETETIME IS NULL
有什么区别呢?
union
SELECT TRADE_CODE, FUND_SECOND_CODE AS TCODE FROM FUND_PRODUCT WHERE DELETETIME IS NULL AND FUND_SECOND_CODE IS NOT NULL AND FUND_SECOND_CODE<>''
这段 用 with table那种格式写一下试试,