MySQL联查及统计问题

1.mysql左连接，where的条件筛选，使得原来带有null值的列消失最终-导致造成数据缺失

解决办法：解决方案是把右表的筛选条件放到前面去，也就是连表的地方去。即on后面，用and连接

https://blog.csdn.net/LJFPHP/article/details/103753929

2.左链接，数据列count()的数量翻倍，解决count(DISTINCT b.id)去重

3.MySQL Left Join左连接后对数据做SUM汇总处理时出现数据倍数增长问题（左连接SUM数据重复问题）

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
select b.id,b.plate_number,SUM( bx.pay_money),SUM(o.money)
from base b
left join baoxian bx
on b.id=bx.plate_number
left join oil o 
on b.id=o.plate_number
GROUP BY b.id

select b.*,bx.bx_money,o.oil_money,jq.jq_money
from base b
left join (select SUM(pay_money) AS bx_money,plate_number from baoxian GROUP BY plate_number) AS bx
on b.id=bx.plate_number
left join (select SUM(money) AS oil_money,plate_number from oil GROUP BY plate_number) AS o
on b.id=o.plate_number
left join (select SUM(money) AS jq_money,plate_number from jiaoqiang GROUP BY plate_number) AS jq
on b.id=jq.plate_number

参考：https://blog.csdn.net/chang100111/article/details/117017988

实际操作一下，以下数据表皆是模拟的数据表和字段，用于示例

1
2
3
4
5
6
7
8
9
SELECT 
a.uid,a.user_name,count(DISTINCT b.id) as task_num,count(DISTINCT d.id) as task_pass_num,count(DISTINCT e.id) as test_pass_num,c.template_total_money,count(DISTINCT f.id) as market_template_num FROM hh_xxx_designer_ranking a 
left join hh_xxx_designer_task_project b on
a.uid=b.uid and b.receive_time > 0 
LEFT JOIN (select sum(money) as template_total_money,uid,create_time,type from hh_xxx_payment_log group by uid ) as c on a.uid=c.uid and c.create_time > 1635945200000 and c.type = 2 
LEFT JOIN hh_xxx_designer_task_project d on a.uid=d.uid and d.complete_time > 0 
LEFT JOIN hh_xxx_designer_task_project e ON a.uid=e.uid and e.test_adopt_time > 0
LEFT JOIN hh_xxx_market_template f ON a.uid=f.uid and f.adopt_time > 0 
where a.uid = 999 GROUP BY a.id;

出现template_total_money=0的状况，与实际并不符合，排除原因，发现是由于分组 在筛选之前的原因，修改为

1
2
3
4
LEFT JOIN (select sum(money) as template_total_money,uid,create_time,type from hh_xxx_payment_log group by uid ) as c on a.uid=c.uid and c.create_time > 16359599900000 and c.type = 2
修改为
LEFT JOIN (select sum(money) as template_total_money,uid,create_time,type from hh_xxx_payment_log where create_time > 16359599900000 and type = 2  group by uid ) as c on a.uid=c.uid
where a.uid = 967 GROUP BY a.id;

4.leftjoin 一对多 只获得一条数据

解决办法，去掉select中id字段

5.如何 distinct 只对一个字段有用，如何同时查出其他字段

distinct只能返回它的目标字段

select distinct name, id from table

1
2
3
4
5
6
7
id name
1 a
2 b
3 c
4 c
5 b
作用是起了的，不过他同时作用了两个字段

select id, distinct name from table

1
报错，distinct必须放在开头，distinct放到where条件里？能，照样报错

解决办法：

在mysql手册里找到一个用法，用group_concat(distinct name)配合group by name可以实现实现了。

select *,group_concat(distinct name) from table group by name;

用count函数试验一下也是可以的

select *, count(distinct name) from table group by name;

1
2
3
4
id name count(distinct name)
1 a 1
2 b 1
3 c 1

6.批量更新 多个字段

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
/**
     * 批量更新 多个字段
     * @param $key  string 条件主键,作用参考switch中的case
     * @param $val  array 修改主键
     * @param $data array $key与$val主键对应的数据载体
     */
    public static function batchUpdateMore($key, $val, $data)
    {
        $table     = self::getTableName();
        $ids       = implode(",", array_column($data, $key));

        $sql = "UPDATE `{$table}` SET ";
        foreach ($val as $field){
            $condition = " ";
            $sql .= "{$field} =  CASE {$key}";
            foreach ($data as $v)
            {
                $condition .=  "  WHEN {$v[$key]} THEN {$v[$field]}";
        }
            $sql .= $condition . " END,";
        }
        $sql=rtrim($sql,',') ." WHERE {$key} in ({$ids})";
        try
        {
            $res = Yii::$app->db->createCommand($sql)->execute();
        }
        catch (\Exception $e)
        {
            return [
                "code"   => 40073,
                "cn_msg" => "操作失败，请重新尝试或联系网站管理员。",
                "en_msg" => $e->getMessage(),
            ];
        }

    }
        return $res;
    }
    
     例子
    UPDATE categories 
    SET display_order = CASE id 
        WHEN 1 THEN 3 
        WHEN 2 THEN 4 
        WHEN 3 THEN 5 
    END, 
    title = CASE id 
        WHEN 1 THEN 'New Title 1'
        WHEN 2 THEN 'New Title 2'
        WHEN 3 THEN 'New Title 3'
    END
WHERE id IN (1,2,3)

7.视图-优缺点

https://blog.csdn.net/weixin_28475533/article/details/116112628

8.相同字段的连个表合并数据，union自动去重，union all 不去重，获取全部数据。

1
select a.user_id,count(a.user_id) as frequency from (select user_id from xxxx_attr_997_24281112  where attr_value='电商羽绒服' union all select user_id  from xxxx_attr_908_24281111  where attr_value='电商羽绒服') as a group by a.user_id order by frequency desc

https://blog.csdn.net/urnot/article/details/80391152