V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
kstsca
V2EX  ›  程序员

给大神们看条sql。看看如何优化,在不改变表结构得情况。

  •  
  •   kstsca · 2013-03-13 17:10:57 +08:00 · 2773 次点击
    这是一个创建于 4033 天前的主题,其中的信息可能已经有所发展或是发生改变。
    SELECT c.unit,

    sum(case when (i.deep=1 OR i.deep>1) and i.type=0 then s.deep1 else 0 end) AS deep1,
    sum(case when (i.deep=1 OR i.deep>1) and i.type=1 then s.deep1 else 0 end) AS deep5,
    sum(case when (i.deep=1 OR i.deep>1) and i.type=2 then s.deep1 else 0 end) AS deep9,
    sum(case when (i.deep=1 OR i.deep>1) and i.type=3 then s.deep1 else 0 end) AS deep13,
    sum(case when (i.deep=2 OR i.deep>2) and i.type=0 then s.deep2 else 0 end) AS deep2,
    sum(case when (i.deep=2 OR i.deep>2) and i.type=1 then s.deep2 else 0 end) AS deep6,
    sum(case when (i.deep=2 OR i.deep>2) and i.type=2 then s.deep2 else 0 end) AS deep10,
    sum(case when (i.deep=2 OR i.deep>2) and i.type=3 then s.deep2 else 0 end) AS deep14,
    sum(case when (i.deep=3 OR i.deep>3) and i.type=0 then s.deep3 else 0 end) AS deep3,
    sum(case when (i.deep=3 OR i.deep>3) and i.type=1 then s.deep3 else 0 end) AS deep7,
    sum(case when (i.deep=3 OR i.deep>3) and i.type=2 then s.deep3 else 0 end) AS deep11,
    sum(case when (i.deep=3 OR i.deep>3) and i.type=3 then s.deep3 else 0 end) AS deep15,
    sum(case when i.deep=4 and i.type=0 then s.deep4 else 0 end) AS deep4,
    sum(case when i.deep=4 and i.type=1 then s.deep4 else 0 end) AS deep8,
    sum(case when i.deep=4 and i.type=2 then s.deep4 else 0 end) AS deep12,
    sum(case when i.deep=4 and i.type=3 then s.deep4 else 0 end) AS deep16,
    sum(i.deep) AS zf

    FROM info AS i LEFT JOIN credits AS s ON i.type=s.type LEFT JOIN contact AS c ON i.uid=c.uid $wansql GROUP BY i.uid ORDER BY zf DESC
    1 条回复    1970-01-01 08:00:00 +08:00
    dilfish
        1
    dilfish  
       2013-03-13 17:24:01 +08:00
    每个表中的数据量是多少呢
    数据库软件是什么呢
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   我们的愿景   ·   实用小工具   ·   1009 人在线   最高记录 6543   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 32ms · UTC 19:28 · PVG 03:28 · LAX 12:28 · JFK 15:28
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.