V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
• 请不要在回答技术问题时复制粘贴 AI 生成的内容
kstsca
V2EX  ›  程序员

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

  •  
  •   kstsca · 2013-03-13 17:10:57 +08:00 · 2998 次点击
    这是一个创建于 4303 天前的主题,其中的信息可能已经有所发展或是发生改变。
    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   ·   实用小工具   ·   1925 人在线   最高记录 6679   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 25ms · UTC 00:01 · PVG 08:01 · LAX 16:01 · JFK 19:01
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.