求高手支招SQLSERVER查询优化

deng2121083 2014-09-10 01:50:19

PmProject
projectId projectName
1 新理想国际大厦

PmTask
taskId title planEnd realBegin realEnd projectId
1 立柱制作及自检 2013-11-18 2013-11-11 2013-11-18 1
2 立柱点焊装配 2013-11-19 2013-11-18 2013-11-18 1
3 立柱焊接 2013-11-20 2013-11-18 2013-11-18 1

项目于任务中间表(项目数据中显示指定任务的计划结束日期)
PmKanbanTask
id detailId taskId projectId
1 1 1 1
1 2 2 1
1 3 3 1

看板表
id title
1. 已下批令
2 设计中
3 审批中

要求使用SQL查出结果

项目名称已下批令(任务表的计划结束) 设计中(任务表的计划结束) 审批中 (任务表的计划结束)
新理想国际大厦 2013-11-18 2013-11-19 2013-11-20

我现在的SQL

SELECT a.
FROM
PmProject a.projectName,diyTable1.planEnd,diyTable2.planEnd,diyTable3.planEnd
left join
(
select
c.projectId,d.planEnd
from
PmKanbanDetail b
left join
PmKanbanTask c
on b.detailId=c.detailId
left join
PmTask d
on c.taskId=d.taskId
where
b.title=' 已下批令'
) diyTable1
on a.projectId=diyTable1.projectId
left join
(
select
c.projectId,d.planEnd
from
PmKanbanDetail b
left join
PmKanbanTask c
on b.detailId=c.detailId
left join
PmTask d
on c.taskId=d.taskId
where
b.title='设计中'
and b.taskType='status4'
) diyTable2
on a.projectId=diyTable2.projectId
left join
(
select
c.projectId,d.planEnd
from
PmKanbanDetail b
left join
PmKanbanTask c
on b.detailId=c.detailId
left join
PmTask d
on c.taskId=d.taskId
where
b.title='审批中'
) diyTable2
on a.projectId=diyTable2.projectId

我现在准备把 planEnd 放入PmKanbanTask 这样减少对任务表的查询，不知道有没有更好的办法，请各路大神支招，非常感谢！

...全文

159 10 打赏收藏转发到动态举报

写回复

用AI写文章

10 条回复

切换为时间正序

请发表友善的回复…

发表回复

Tiger_Zhao 2014-09-10

打赏
举报

做三份行转列，然后拼起来。

deng2121083 2014-09-10

打赏
举报

引用 8 楼 deng2121083 的回复:

[quote=引用 7 楼 Tiger_Zhao 的回复:] WITH PmProject(...),PmTask(...), PmKanbanTask(...) 仅供测试，用于代替实际存在的表。真实环境只需要

WITH T AS (
   SELECT b.projectName,
          a.detailId,
          d.planEnd
     FROM PmKanbanTask a
     JOIN PmProject b 
       ON a.projectId=b.projectId 
     JOIN PmTask d  
       ON a.taskId=d.taskId 
)
SELECT projectName,
       [1] AS [已下批令],
       [2] AS [设计中],
       [3] AS [审批中]
  FROM T
 PIVOT (
        MAX(planEnd)
        FOR detailId IN ([1], [2], [3]
       ) p

对于大神只能45°膜拜，原来SQL就有行转列的方法，让我用那么多left join 受教了，谢谢！[/quote] 高手如果有多列该怎么办呢？ WITH T AS ( SELECT a.projectName, b.detailId, d.planEnd,d.realBegin,d.realEnd FROM PmProject a JOIN PmKanbanTask b ON a.projectId=b.projectId JOIN PmTask d ON b.taskId=d.taskId ) SELECT projectName, [1] AS [已下批令], [2] AS [设计中], [3] AS [审批中] FROM T PIVOT ( MAX(planEnd) FOR detailId IN ([1], [2], [3]) ) p

deng2121083 2014-09-10

打赏
举报

引用 7 楼 Tiger_Zhao 的回复:

WITH PmProject(...),PmTask(...), PmKanbanTask(...) 仅供测试，用于代替实际存在的表。真实环境只需要

WITH T AS (
   SELECT b.projectName,
          a.detailId,
          d.planEnd
     FROM PmKanbanTask a
     JOIN PmProject b 
       ON a.projectId=b.projectId 
     JOIN PmTask d  
       ON a.taskId=d.taskId 
)
SELECT projectName,
       [1] AS [已下批令],
       [2] AS [设计中],
       [3] AS [审批中]
  FROM T
 PIVOT (
        MAX(planEnd)
        FOR detailId IN ([1], [2], [3]
       ) p

对于大神只能45°膜拜，原来SQL就有行转列的方法，让我用那么多left join 受教了，谢谢！

Tiger_Zhao 2014-09-10

打赏
举报

WITH PmProject(...),PmTask(...), PmKanbanTask(...) 仅供测试，用于代替实际存在的表。
真实环境只需要

WITH T AS (

   SELECT b.projectName,

          a.detailId,

          d.planEnd

     FROM PmKanbanTask a

     JOIN PmProject b 

       ON a.projectId=b.projectId 

     JOIN PmTask d  

       ON a.taskId=d.taskId 

)

SELECT projectName,

       [1] AS [已下批令],

       [2] AS [设计中],

       [3] AS [审批中]

  FROM T

 PIVOT (

        MAX(planEnd)

        FOR detailId IN ([1], [2], [3]

       ) p

deng2121083 2014-09-10

打赏
举报

引用 5 楼 Tiger_Zhao 的回复:

三表关联就够了

WITH PmProject(projectId,projectName) AS (
    SELECT 1,N'新理想国际大厦'
),
PmTask(taskId,title,planEnd,realBegin,realEnd,projectId) AS (
    SELECT 1,N'立柱制作及自检','2013-11-18','2013-11-11','2013-11-18',1 union all
    SELECT 2,N'立柱点焊装配','2013-11-19','2013-11-18','2013-11-18',1 union all
    SELECT 3,N'立柱焊接','2013-11-20','2013-11-18','2013-11-18',1
),
PmKanbanTask(id,detailId,taskId,projectId) AS (
    SELECT 1,1,1,1 UNION ALL
    SELECT 1,2,2,1 UNION ALL
    SELECT 1,3,3,1
),
T AS (
   SELECT b.projectName,
          a.detailId,
          d.planEnd
     FROM PmKanbanTask a
     JOIN PmProject b 
       ON a.projectId=b.projectId 
     JOIN PmTask d  
       ON a.taskId=d.taskId 
)
SELECT projectName,
       [1] AS [已下批令],
       [2] AS [设计中],
       [3] AS [审批中]
  FROM T
 PIVOT (
        MAX(planEnd)
        FOR detailId IN ([1], [2], [3])
       ) p

引用 5 楼 Tiger_Zhao 的回复:

三表关联就够了

WITH PmProject(projectId,projectName) AS (
    SELECT 1,N'新理想国际大厦'
),
PmTask(taskId,title,planEnd,realBegin,realEnd,projectId) AS (
    SELECT 1,N'立柱制作及自检','2013-11-18','2013-11-11','2013-11-18',1 union all
    SELECT 2,N'立柱点焊装配','2013-11-19','2013-11-18','2013-11-18',1 union all
    SELECT 3,N'立柱焊接','2013-11-20','2013-11-18','2013-11-18',1
),
PmKanbanTask(id,detailId,taskId,projectId) AS (
    SELECT 1,1,1,1 UNION ALL
    SELECT 1,2,2,1 UNION ALL
    SELECT 1,3,3,1
),
T AS (
   SELECT b.projectName,
          a.detailId,
          d.planEnd
     FROM PmKanbanTask a
     JOIN PmProject b 
       ON a.projectId=b.projectId 
     JOIN PmTask d  
       ON a.taskId=d.taskId 
)
SELECT projectName,
       [1] AS [已下批令],
       [2] AS [设计中],
       [3] AS [审批中]
  FROM T
 PIVOT (
        MAX(planEnd)
        FOR detailId IN ([1], [2], [3]
       ) p

写的很厉害，就是我看不太懂,我见你写的 AS后边都要把数据全部select 拼出来，这边数据量比较大，只能通过查询去完，project,task里边都有很多数据，我不可能一个一个union拼数据,非常感谢哦

Tiger_Zhao 2014-09-10

打赏
举报

三表关联就够了

WITH PmProject(projectId,projectName) AS (

    SELECT 1,N'新理想国际大厦'

),

PmTask(taskId,title,planEnd,realBegin,realEnd,projectId) AS (

    SELECT 1,N'立柱制作及自检','2013-11-18','2013-11-11','2013-11-18',1 union all

    SELECT 2,N'立柱点焊装配','2013-11-19','2013-11-18','2013-11-18',1 union all

    SELECT 3,N'立柱焊接','2013-11-20','2013-11-18','2013-11-18',1

),

PmKanbanTask(id,detailId,taskId,projectId) AS (

    SELECT 1,1,1,1 UNION ALL

    SELECT 1,2,2,1 UNION ALL

    SELECT 1,3,3,1

),

T AS (

   SELECT b.projectName,

          a.detailId,

          d.planEnd

     FROM PmKanbanTask a

     JOIN PmProject b 

       ON a.projectId=b.projectId 

     JOIN PmTask d  

       ON a.taskId=d.taskId 

)

SELECT projectName,

       [1] AS [已下批令],

       [2] AS [设计中],

       [3] AS [审批中]

  FROM T

 PIVOT (

        MAX(planEnd)

        FOR detailId IN ([1], [2], [3])

       ) p

Tiger_Zhao 2014-09-10

打赏
举报

WITH PmProject(projectId,projectName) AS (

    SELECT 1,N'新理想国际大厦'

),

PmTask(taskId,title,planEnd,realBegin,realEnd,projectId) AS (

    SELECT 1,N'立柱制作及自检','2013-11-18','2013-11-11','2013-11-18',1 union all

    SELECT 2,N'立柱点焊装配','2013-11-19','2013-11-18','2013-11-18',1 union all

    SELECT 3,N'立柱焊接','2013-11-20','2013-11-18','2013-11-18',1

),

PmKanbanTask(id,detailId,taskId,projectId) AS (

    SELECT 1,1,1,1 UNION ALL

    SELECT 1,2,2,1 UNION ALL

    SELECT 1,3,3,1

),

PmKanbanDetail(detailId,title) AS (

    SELECT 1,N'已下批令' UNION ALL

    SELECT 2,N'设计中' UNION ALL

    SELECT 3,N'审批中'

),

T AS (

   SELECT b.projectName,

          c.title,

          d.planEnd

     FROM PmKanbanTask a

     JOIN PmProject b 

       ON a.projectId=b.projectId 

     JOIN PmKanbanDetail c 

       ON a.detailId=c.detailId 

     JOIN PmTask d  

       ON a.taskId=d.taskId 

)

SELECT *

  FROM T

 PIVOT (

        MAX(planEnd)

        FOR title IN ([已下批令], [设计中], [审批中])

       ) p

projectName     已下批令   设计中     审批中

--------------- ---------- ---------- ----------

新理想国际大厦  2013-11-18 2013-11-19 2013-11-20

deng2121083 2014-09-10

打赏
举报

稍等，我闹个完整的

IFocusYou 2014-09-10

打赏
举报

语句正确吗？这么长，把建表语句也给出来吧。

deng2121083 2014-09-10

打赏
举报

写错了，最后一个left join 是diyTable3

C语言高效编程--高手支招

高手支招防止U盘中毒的五大攻略.docx

高手支招解决新补丁引发Windows 7黑屏问题.docx

大家都来看呀，这是个间接用USB口转串口的东西看看，明白都来

学习嵌入式需要的基本知识，学习嵌入式之linux的学习，嵌入式学习之零基础学linux，学习嵌入式之学习linux命令，选择一本工具书学linux，选择适合你学习嵌入式的linux版本，养成在命令行下工作，多进社区学习，勤于...

应用实例

27,580

社区成员

68,556

社区内容

发帖

与我相关

我的任务

社区管理员

加入社区

近7日
近30日
至今

加载中

查看更多榜单

社区公告

暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章