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

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

  •  
  •   kstsca · 2013-03-13 17:10:57 +08:00 · 2135 次点击
    这是一个创建于 2410 天前的主题,其中的信息可能已经有所发展或是发生改变。
    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
        1
    dilfish   2013-03-13 17:24:01 +08:00
    每个表中的数据量是多少呢
    数据库软件是什么呢
    关于   ·   FAQ   ·   API   ·   我们的愿景   ·   广告投放   ·   感谢   ·   实用小工具   ·   859 人在线   最高记录 5043   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.3 · 21ms · UTC 20:55 · PVG 04:55 · LAX 13:55 · JFK 16:55
    ♥ Do have faith in what you're doing.