如何用联合查询替换子查询？

问题描述：

下面给出的是一个正在运行的查询。我想用直接联合查询替换所有子查询。请建议我一个解决方案。如何用联合查询替换子查询？

SELECT 
    id, 
    startTime, 
    endTime, 
    drawingTime, 
    result, 
    wpAmount, 
    lpAmount, 
    prize, 
    cntWinTickets, 
    cntLoosTickets 
FROM (SELECT 
    i.id   AS id, 
    i.start_time AS startTime, 
    i.end_time  AS endTime, 
    i.drawing_time AS drawingTime, 
    i.result  AS result, 
    SUM(t.amount) AS wpAmount, 
    SUM(t.prize) AS prize, 
    COUNT(t.id) AS cntWinTickets 
     FROM issues i 
    LEFT JOIN orders o 
     ON o.issue_id = i.id 
    LEFT JOIN tickets t 
     ON t.order_id = o.id 
     AND t.has_prize = 1 
     GROUP BY i.id) AS A 
    INNER JOIN (SELECT 
     i.id   AS lid, 
     SUM(t.amount) AS lpAmount, 
     COUNT(t.id) AS cntLoosTickets 
      FROM issues i 
     LEFT JOIN orders o 
      ON o.issue_id = i.id 
     LEFT JOIN tickets t 
      ON (t.order_id = o.id 
       AND (t.has_prize = 0 
       OR t.has_prize IS NULL)) 
      GROUP BY i.id) AS B 
    ON A.id = B.lid

如果您创建** VIEW ** – 2013-04-26 09:23:32

，您可以发布架构和所需的输出，这可能是最好的选择。以及一些示例数据，它会更好地解决 – 2013-04-26 09:25:24

答

这是一个解决方案，在SUM运算符中有一个奇特的交叉乘积。我认为最好不要这样做，但它很有趣！

SELECT i.id AS id, 
     i.start_time AS startTime, 
     i.end_time AS endTime, 
     i.drawing_time AS drawingTime, 
     i.result AS result, 
     SUM(t.amount*COALESCE(t.has_prize,0)) AS wpAmount, 
     SUM(t.prize*COALESCE(t.has_prize,0)) AS prize, 
     COUNT(case when t.has_prize=1 then t.id end) AS cntWinTickets, 
     SUM(t.amount*(1-COALESCE(t.has_prize,0))) AS lpAmount, 
     COUNT(case when COALESCE(t.has_prize,0)=0 then t.id end) AS cntLoosTickets 
FROM issues i 
LEFT JOIN orders o ON o.issue_id = i.id 
LEFT JOIN tickets t ON t.order_id = o.id AND t.has_prize in (null,0,1) 
GROUP BY i.id

这是什么？为什么这里有多个't.amount * COALESCE（t.has_prize，0）' – 2013-04-26 09:37:38

't.has_prize'要么是'1'，'0'或'NULL'。如果你想在't.has_prize'等于'1'时求和，你可以做一个交叉产品。相反，'t.amount *（1-COALESCE（t.has_prize，0））'适用于''。 – 2013-04-26 09:39:53

答

尝试：

SELECT i.id AS id, 
     i.start_time AS startTime, 
     i.end_time AS endTime, 
     i.drawing_time AS drawingTime, 
     i.result, 
     SUM(case when t.has_prize=1 then t.amount end) AS wpAmount, 
     SUM(case when t.has_prize=1 then t.prize end) AS prize, 
     COUNT(case when t.has_prize=1 then t.id end) AS cntWinTickets, 
     SUM(case when coalesce(t.has_prize,0)=0 then t.amount end) AS lpAmount, 
     COUNT(case when coalesce(t.has_prize,0)=0 then t.id end) AS cntLoosTickets 
FROM issues i 
LEFT JOIN orders o ON o.issue_id = i.id 
LEFT JOIN tickets t ON t.order_id = o.id AND t.has_prize in (null,0,1) 
GROUP BY i.id

答

这里是如何结合这

SELECT 
    i.id   AS id, 
    i.start_time AS startTime, 
    i.end_time  AS endTime, 
    i.drawing_time AS drawingTime, 
    i.result  AS result, 
    SUM(IF t.has_prize = 1,t.amount,0) AS wpAmount, 
    SUM(IF t.has_prize = 1,t.prize,0) AS prize, 
    COUNT(IF t.has_prize = 1,t.id,0) AS cntWinTickets, 
    SUM(IF t.has_prize = 0,t.amount,0) AS lpAmount, 
    COUNT(IF t.has_prize = 0,1,0) AS cntLoosTickets 
FROM issues i 
    LEFT JOIN orders o ON o.issue_id = i.id 
    LEFT JOIN tickets t ON t.order_id = o.id 
GROUP BY i.id

IF t.has_prize = 1,t.amount,0这意味着如果t.has_prize = 1不是采取t.amount别人拿0。备用是马克使用的CASE。

如何用联合查询替换子查询？

相关推荐