基于有限制的SELECT查询的MYSQL插入查询

Question

选择查询

SELECT a. * , d.agent_id, COUNT( d.driver_id ) AS `noofdrivers`
                                      FROM ta_agent a, ta_drivers d
                                      WHERE a.agent_id = d.agent_id
                                      AND a.agent_id = ".$agent_id."
                                      GROUP BY a.agent_id

选择查询返回以下记录

agent_id  agent_name company_name  pricing_plan  noofdrivers
--------  ----------  -----------  ------------  -----------
    3         CCC           ZZZ          8499       2
    2         BBB           YYY          3499       3    

驱动数据库表

agent_id  driver_id   driver_name
--------  ----------  -----------
2         1           EEE
2         2           FFF
2         3           GGG
1         4           HHH
3         5           III
3         6           JJJ

如何根据上述选择查询结果插入记录。如果pricing_plan为3499，agent_id 2只能添加5个驱动程序。现在，agent_id 2有3个驱动程序。因此，他仍然可以增加两个司机只。如何在mysql查询中执行此限制？

Answer 1

尝尝这个,

insert into driver(driver_id, agent_id, driver_name)
   select ins.driver_id, ins.agent_id, ins.driver_name
   from (select 7 as driver_id, 2 as agent_id, 'KKK' as driver_name) ins
   where (select count(*) 
      from driver d, agent a
      where d.agent_id=a.agent_id and a.agent_id=ins.agent_id and a.pricing_plan=3499)<5;

在给定的查询中，您必须将变量$driver_id、$agent_id和$driver_name分别用于值7、2和'KKK'。

所以，看起来，

$sql="insert into driver(driver_id, agent_id, driver_name)
   select ins.driver_id, ins.agent_id, ins.driver_name
   from (select $driver_id as driver_id, $agent_id as agent_id, $driver_name as driver_name) ins
   where (select count(*) 
      from driver d, agent a
      where d.agent_id=a.agent_id and a.agent_id=ins.agent_id and a.pricing_plan=3499)<5";

这是演示SQLFiddle

Answer 2

你可以用这样的东西：

insert into ta_drivers(agent_id, driver_id, driver_name) SELECT (agent_id, driver_id, driver_name) FROM dual where ((SELECT a. * , d.agent_id, COUNT( d.driver_id ) AS `noofdrivers`
                                  FROM ta_agent a, ta_drivers d
                                  WHERE a.agent_id = d.agent_id
                                  AND pricing_plan=3499 and a.agent_id = ".$agent_id."
                                  GROUP BY a.agent_id)<5);

您必须检查查询，因为这是不正常的。但理论上：

insert into table select from dual where ((select count(*) condition) <5);