mysql - 向查询添加子查询

Question

我是子查询的新手，我认为在以下情况下我需要它们，但不确定如何进行。

我有以下查询。现在，我有两件事需要添加到查询中 =

• 对于 CA 消费者，他们只能发生 1 次车祸 (car_acc)，而在其他州，这无关紧要。

• 在佛罗里达州、马里兰州、新罕布什尔州和西澳大利亚州，当前受保可以为 0( bool 值)，而在其他州则只能为 1。

  SELECT Count(*),
         Avg(sl.our_cost),
         Avg(sl.purchase_price)
  FROM   sold_leads AS sl
         INNER JOIN leads AS l
                 ON l.id = sl.lead_id
         INNER JOIN contacts AS c
                 ON c.lead_id = l.id
         INNER JOIN drivers AS d
                 ON d.lead_id = l.id
  WHERE  c.state IN( 'AK', 'AL', 'AR', 'AZ',
                     'CA', 'CO', 'CT', 'DC',
                     'DE', 'FL', 'GA', 'HI',
                     'IA', 'ID', 'IL', 'IN',
                     'KS', 'KY', 'LA', 'MD',
                     'ME', 'MI', 'MN', 'MO',
                     'MS', 'MT', 'NC', 'ND',
                     'NE', 'NH', 'NJ', 'NM',
                     'NV', 'NY', 'OH', 'OK',
                     'OR', 'PA', 'RI', 'SC',
                     'SD', 'TN', 'TX', 'UT',
                     'VA', 'VT', 'WA', 'WI',
                     'WV', 'WY' )
         AND l.leg = 0
         AND ( Datediff(CURRENT_DATE, d.date_of_birth) / 365 ) >= 50
         AND l.create_date >= '2012-1-1';  
  

我该如何将这些子查询添加到我的查询中？是否有更好的方法来查找该信息？

编辑:

我应该提一下，它应该读作 1 个“车祸”或“违章”。我有两个不同的车祸和违规表格，因此对于 CA 居民，我只需要那些有 1 次事故或 1 次违规的人。

Answer 1

因为 car_accidents、violations 的数据在单独的表中，您需要使用 left join 来连接它们。

希望这是有道理的:-)

SELECT Count(*),
       Avg(sl.our_cost),
       Avg(sl.purchase_price)
FROM   sold_leads AS sl
       INNER JOIN leads AS l
               ON l.id = sl.lead_id
       INNER JOIN contacts AS c
               ON c.lead_id = l.id
       INNER JOIN drivers AS d
               ON d.lead_id = l.id
       LEFT JOIN 
  (SELECT ca1.driver_id, COUNT(*) AS cnt FROM car_accidents ca1 GROUP BY ca1.driver_id) ca 
               ON (c.state = 'CA' and ca.driver_id = d.id)
       LEFT JOIN 
  (SELECT v1.driver_id, COUNT(*) AS cnt FROM violations v1 GROUP BY v1.driver_id) v
               ON (c.state IN ('CA') and v.driver_id = d.id)
WHERE  c.state IN( 'AK', 'AL', 'AR', 'AZ','CA', 'CO', 'CT', 'DC',
                   'DE', 'FL', 'GA', 'HI','IA', 'ID', 'IL', 'IN',
                   'KS', 'KY', 'LA', 'MD','ME', 'MI', 'MN', 'MO',
                   'MS', 'MT', 'NC', 'ND','NE', 'NH', 'NJ', 'NM',
                   'NV', 'NY', 'OH', 'OK','OR', 'PA', 'RI', 'SC',
                   'SD', 'TN', 'TX', 'UT','VA', 'VT', 'WA', 'WI',
                   'WV', 'WY' )
       AND l.leg = 0
       AND ( Datediff(CURRENT_DATE, d.date_of_birth) / 365 ) >= 50
       AND l.create_date >= '2012-1-1'
       AND IFNULL(v.cnt,0) < 2 AND ifnull(ca.cnt.0) < 2
       AND (c.state IN ('FL','MD','NH','WA') OR c.IsInsured = 1) 

注意将状态放在单独的表中会更有意义。这样您就可以将条款和条件放入该表中，并在您的加入条件中使用它们。

Table state
-----------
id : varchar(2) primary key not null
country : varchar(45) not null
max_accidents : int null
max_violations : int null
must_be_insured : boolean not null

然后您可以将查询重写为:

SELECT Count(*),
       Avg(sl.our_cost),
       Avg(sl.purchase_price)
FROM   sold_leads AS sl
       INNER JOIN leads AS l
               ON l.id = sl.lead_id
       INNER JOIN contacts AS c
               ON c.lead_id = l.id
       INNER JOIN drivers AS d
               ON d.lead_id = l.id
       INNER JOIN state s ON (s.id = c.state)
       LEFT JOIN 
(SELECT ca1.driver_id, COUNT(*) AS cnt FROM car_accidents ca1 GROUP BY ca1.driver_id) ca 
           ON (s.max_accidents IS NOT NULL AND ca.driver_id = d.id)
   LEFT JOIN 
(SELECT v1.driver_id, COUNT(*) AS cnt FROM violations v1 GROUP BY v1.driver_id) v
           ON (s.max_violations IS NOT NULL AND v.driver_id = d.id)
       AND l.leg = 0
       AND (Datediff(CURRENT_DATE, d.date_of_birth) / 365.25 ) >= 50
       AND l.create_date >= '2012-1-1'; 
       AND IFNULL(v.cnt,0) < 2 AND ifnull(ca.cnt.0) < 2
       AND (s.must_be_insured = 0 OR c.IsInsured = 1)