sql正确连接我如何简化它？

wmomyfyw 于 2021-08-09 发布在 Java

关注(0)|答案(1)|浏览(221)

SELECT
    COL.Seniority,
    COL.COLID AS COLCOL,
    MAIN.COLID AS MAINCOL,
    LINES.LINEID AS LINELINE,
    MAIN.LINEID AS MAINLINE,
    case when LineS.line = 1 then MAIN.Rate  end as BasicWage,
    case when LINES.Line = 2 then MAIN.Rate end as FixedOT
FROM PWPSC001_RATES MAIN
LEFT JOIN PWPSC001 POS
    ON POS.ID = MAIN.ID
LEFT JOIN PWPSC_RATES_COLUMNS COL
    ON COL.SEQNO = MAIN.SEQNO
LEFT JOIN PWPSC_RATES_LINES LINES
    ON LINES.LINEID = MAIN.LINEID
WHERE 
    MAIN.SEQNO = 355 AND MAIN.COLID = 6199 AND MAIN.ID = 7477
    AND MAIN.LINEID in(10355,20355) AND LINES.SEQNO =355

其他信息：

如何删除最后两列中的空值并将其连接在一起？

sql sql-server

来源：https://stackoverflow.com/questions/61884526/sql-proper-join-how-can-i-simplify-it

1条答案

按热度按时间

af7jpaap1#

看起来您需要聚合：

SELECT COL.Seniority, COL.COLID AS COLCOL, MAIN.COLID AS MAINCOL, 
       MAX(case when LineS.line = 1 then MAIN.Rate end) as BasicWage,
       MAX(case when LINES.Line = 2 then MAIN.Rate end) as FixedOT
FROM PWPSC001_RATES MAIN LEFT JOIN
     PWPSC001 POS
     ON POS.ID = MAIN.ID LEFT JOIN
     PWPSC_RATES_COLUMNS COL
     ON COL.SEQNO = MAIN.SEQNO LEFT JOIN
     PWPSC_RATES_LINES LINES
     ON LINES.LINEID = MAIN.LINEID
WHERE MAIN.SEQNO = 355 AND MAIN.COLID = 6199 AND MAIN.ID = 7477 AND
      MAIN.LINEID IN (10355, 20355) AND LINES.SEQNO = 355
GROUP BY COL.Seniority, COL.COLID, MAIN.COLID;

注意，我删除了 MAINLINE 以及 LINEID 从 SELECT 以及 GROUP BY . 实际上并不需要它，因为相应的感兴趣的值最终会出现在不同的列中--您不需要分别使用这些值。

赞(0）回复(0）举报 2021-08-09

我来回答

sql正确连接我如何简化它？

1条答案

相关问题

热门标签

最新问答