如何将这两条 SQL 语句合并为一条?

2022-01-15 00:00:00 sql mariadb mysql

我编写并想将这 2 个 sql 结合起来，一个是基于另一个的结果.我检查了 这篇文章，但看起来它不是基于结果的.我怎样才能实现它?

I wrote and would like to combine these 2 sql, one is based on results of another. I checked this post, but looks like its not results based. How could I achieve it ?

第一个sql:

SELECT
    `potential`.*,
    `customer`.`ID` as 'FID_customer'
FROM
    `os_potential` as `potential`,
    `os_customer` as `customer`
WHERE `potential`.`FID_author` = :randomID
      AND `potential`.`converted` = 1
      AND `potential`.`street` = `customer`.`street`
      AND `potential`.`zip` = `customer`.`zip`
      AND `potential`.`city` = `customer`.`city`;

第二条sql:

SELECT
    sum(`order`.`price_customer`) as 'Summe'
FROM
    `os_order` as `order`,
    `RESUTS_FROM_PREVIOUS_SQL_STATEMENT` as `results`
WHERE `order`.`FID_status` = 10
      AND `results`.`FID_customer` = `order`.`FID_customer`;

我想从第一个 sql 中获取所有内容 + 第二个 sql 中的Summe".

I would like to get everything from first sql + the 'Summe' from second sql.

表格

1.潜力:

+----+------------+-----------+--------+-----+------+
| ID | FID_author | converted | street | zip | city |
+----+------------+-----------+--------+-----+------+

2.客户:

+----+--------+-----+------+
| ID | street | zip | city |
+----+--------+-----+------+

3.订单:

+----+--------------+----------------+
| ID | FID_customer | price_customer |
+----+--------------+----------------+

推荐答案

SELECT p.*
     , c.ID FID_customer
     , o.summe
  FROM os_potential p
  JOIN os_customer c
    ON c.street = p.street 
   AND c.zip = p.zip 
   AND c.city = p.city 
  JOIN 
     ( SELECT FID_customer
            , SUM(price_customer) Summe
         FROM os_order 
        WHERE FID_status = 10
        GROUP
           BY FID_customer
     ) o
    ON o.FID_customer = c.ID
 WHERE p.FID_author = :randomID 
   AND p.converted = 1
   ;

相关文章