【讨论】多字段or连接查询如何优化！

fuiou_cary

mchdba 发表于 2014-8-26 12:55
如果要去重的功效，就直接用union 不要用union all就可以了。

我用的就是union啊，不知道为什么没去重呢，我写的是
select sum(t.counts),sum(t.txn_amount) from (select count(1) as counts, sum(src_amt) as txn_amount from T_06
where fas_settle_dt>= '20140601' and fas_settle_dt <= '20140630' and tm_agent_1 ='08A000S000'
union
select count(1) as counts, sum(src_amt) as txn_amount from T_06
where fas_settle_dt>= '20140601' and fas_settle_dt <= '20140630' and tm_agent_2 ='08A000S000'
union
select count(1) as counts, sum(src_amt) as txn_amount from T_06
where fas_settle_dt>= '20140601' and fas_settle_dt <= '20140630' and tm_agent_3 = '08A000S000'
union
select count(1) as counts, sum(src_amt) as txn_amount from T_06
where fas_settle_dt>= '20140601' and fas_settle_dt <= '20140630' and agent_cd_1 = '08A000S000'
union
select count(1) as counts, sum(src_amt) as txn_amount from T_06
where fas_settle_dt>= '20140601' and fas_settle_dt <= '20140630' and agent_cd_2 = '08A000S000'
union
select count(1) as counts, sum(src_amt) as txn_amount from T_06
where fas_settle_dt>= '20140601' and fas_settle_dt <= '20140630' and agent_cd_3='08A000S000'
union
select count(1) as counts, sum(src_amt) as txn_amount from T_06
where fas_settle_dt>= '20140601' and fas_settle_dt <= '20140630' and tm_ins='08A000S000') t;
说明一下啊，tm_agent_1，tm_agent_2，tm_agent_3，agent_cd_1，agent_cd_2，agent_cd_3，tm_ins这些字段不是只有一个有值，大部分时候有几个字段同时取一个值，少数情况下是只有一个字段取值

fuiou_cary

fuiou_cary 发表于 2014-8-26 15:31
我用的就是union啊，不知道为什么没去重呢，我写的是
select sum(t.counts),sum(t.txn_amount) from (se ...

知道为什么了，每个union取的是count不是具体字段，取明细就会去重了

lzd_7993

可以用IN替代

i2235932666

or 找成 union all

stevendba

表设计不对，(tm_agent_1 ='08M082'
       or tm_agent_2 ='08M082'
       or tm_agent_3 = '08M082'
       or agent_cd_1 = '08M082'
       or agent_cd_2 = '08M082'
       or agent_cd_3='08M082'
       or tm_ins='08M082')
这些字段要单独提取出来，然后加一个标志位，然后用表关联。
http://blog.csdn.net/stevendbaguo/article/details/39472273