解决问题集锦：）

Q5: 通过查询结果更新数据：

如果像这样写在一个SQL中会报错：

DELETE
FROM
	users_project_engineering_nodes
WHERE
	project_engineer_node_id IN (
		SELECT
			users_project_engineering_nodes.project_engineer_node_id
		FROM
			users_project_engineering_nodes
		INNER JOIN project_engineerings_nodes ON users_project_engineering_nodes.project_engineer_node_id = project_engineerings_nodes.id
		WHERE
			project_engineerings_nodes.is_complete = 1
	)

错误：You can't specify target table 'users_project_engineering_nodes' for update

解决：

CREATE TABLE tmp AS SELECT
	users_project_engineering_nodes.project_engineer_node_id
FROM
	users_project_engineering_nodes
INNER JOIN project_engineerings_nodes ON users_project_engineering_nodes.project_engineer_node_id = project_engineerings_nodes.id
WHERE
	project_engineerings_nodes.is_complete = 1;


DELETE
FROM
	users_project_engineering_nodes
WHERE
	project_engineer_node_id IN (
		SELECT
			tmp.project_engineer_node_id
		FROM
			tmp
	);

更复杂的：

UPDATE project_engineerings_nodes
LEFT JOIN project_engineerings ON project_engineerings.id = project_engineerings_nodes.project_engineerings_id
INNER JOIN project_contract_subs ON project_engineerings.project_contract_sub_id = project_contract_subs.id
SET project_engineerings_nodes.complete_start_date = project_contract_subs.signed,
 project_engineerings_nodes.complete_end_date = project_contract_subs.signed,
 project_engineerings_nodes.is_complete = 1
WHERE
	project_engineerings_nodes.project_engineerings_id IN (
		SELECT
			project_engineerings.id
		FROM
			project_engineerings
		INNER JOIN project_contract_subs ON project_contract_subs.id = project_engineerings.project_contract_sub_id
		WHERE
			project_contract_subs.signed != ''
	)
AND project_engineerings_nodes.is_complete != 1
AND (
	project_engineerings_nodes.project_node_id = 3
	OR project_engineerings_nodes.project_node_id = 23
)

Q4: Mysql升级后遇到错误：

this is incompatible with sql_mode=only_full_group_by

Incorrect datetime value: '0000-00-00 00:00:00'

Mysql命令行查看GLOBAL参数：

select @@global.sql_mode;

去掉其中的only_full_group_by和no_zero_in_date

set global sql_mode=‘STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION’

Q3: Mac最近升级，然后Apache启动不起来了

首先调试模式启动apache

apachectl -e DEBUG

看到命令行提示错误信息：

到icu4c的目录下，会看到已经升级到了libicui18n.62.dylib

需要升级php到最新版本，新版本的php是依赖libicui18n.62.dylib的：

brew upgrade php

然后就OK了

sudo apachectl restart

Q2: Group By

————————————————————————————————————————————————————————

统计某个表中的分类数据时，用组合的GroupBy。

比如我要统计某个城市员工用工类别，城市是关联表，用工类别也是关联表，就可以如下查询：

SELECT
	oa_city_id, oa_employee_type_id, count(*)
FROM
	oa_employees
GROUP BY
	oa_city_id, oa_employee_type_id
ORDER BY
	oa_city_id, oa_employee_type_id

查询结果：

使用框架构造响应sql就可以了，比如CakePHP中：

$query1 = $this->OaEmployees->find();
$oaEmployeeOnTypeAndCity = $query1
		->select(['oa_city_id', 'oa_employee_type_id',  'count' => $query1->func()->count('id')])
		->group(['oa_employee_type_id', 'oa_city_id'])
		->order(['oa_city_id', 'oa_employee_type_id']);

页面遍历：

<?php
$len = count($oaEmployeeTypes);
for ($i = 0; $i < $len; $i++) {
    $tag[] = 0;
}
foreach ($cities as $id => $name) :
?>
<tr>
    <th><?= $name ?></th>
    <?php
    $tag = '';
    foreach ($oaEmployeeTypes as $oaEmployeeType) {
        $haveCount = false;
        foreach ($oaEmployeeOnTypeAndCity as $eachOaEmployeeOnTypeAndCity) {
            if ($eachOaEmployeeOnTypeAndCity->oa_city_id === $id && $eachOaEmployeeOnTypeAndCity->oa_employee_type_id === $oaEmployeeType->id) {
            $tag .= "<td>$eachOaEmployeeOnTypeAndCity->count</td>";
            $haveCount = true;
            break;
            }
        }
        if (!$haveCount) {
            $tag .= '<td>0</td>';
        }
    }
    echo $tag;
    ?>
</tr>
<?php endforeach; ?>

Q1: 查询结果出现重复数据，数据库里只有一条数据。

————————————————————————————————————————————————————————

小伙伴直接利用distinct，效果是对了，但是结果是牺牲了性能，增加了一倍的查询工作量。
最后发现问题是LEFT JOIN的表中有重复数据导致的，要先查询LEFT JOIN表中的重复数据，然后删除即可。
查询重复数据：

SELECT
    *
FROM
    project_engineering_times
WHERE
    id IN (
        SELECT
            id
        FROM
            project_engineering_times
        GROUP BY
            project_engineering_id
        HAVING
            count(id) > 1
    )

删除：

SELECT
	*
FROM
	project_engineering_times
WHERE
	project_engineering_id = 4064;

DELETE
FROM
	project_engineering_times
WHERE
	id = 1411;#假设上面一句查询到的一条重复数据id是1411