MYSQL INSERT查询基于带LIMIT限制的SELECT查询 - MYSQL INSERT query based on SELECT query with LIMIT restrictions

MYSQL INSERT query based on SELECT query with LIMIT restrictions

SELECT查询

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

SELECT查询返回以下记录

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

如何根据上述SELECT查询结果插入记录。如果CCD_ 1是3499，则CCD_。现在，agent_id 2有3个驱动程序。所以，他仍然只能添加2个驱动程序。如何在mysql查询中进行这个限制？

试试这个，

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;

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

所以，看起来像

$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";

这是DEMO SQLFiddle

您可以使用这样的东西：

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);