sql还能这样写

发表于 2021-06-22 更新于 2023-01-25

记录一些比较奇葩，或者从来没有遇到的sql，记录一下。待持续更新。

查询工号

比如有一张员工姓名表，user，然后想从user表里面查找一些姓名的工号，比如，我将要查找的姓名放到test表里面，test表只有一列，那么，有如下sql。

为了统计姓名的重复情况，我将直接连接另外一张查询出的表（子查询），做统计重名情况。然后有如下的sql。由于没有分组，可能存在重复的情况。

然后，再将查询的结果直接导出来。

SELECT
	fuzeren,
	user_no,
	c.cnt
FROM
	test
LEFT JOIN users ON test.fuzeren = users.user_name
LEFT JOIN (
	SELECT
		user_name,
		count(*) AS cnt
	FROM
		users
	GROUP BY
		user_name
) c ON fuzeren = c.user_name

重复计算字段

一般的解法，都是用子查询等，将查询的结果，则重新select 一次。

http://cn.voidcc.com/question/p-tvmbteju-s.html

在select的字段，如何直接引用这些字段呢？在mysql中倒是可以直接引用。语法如下：

大概用@var:= 表达式 的形式，为表量复制，然后使用@var来直接引用变量。

SELECT  @b := UPPER(hostname) as b ,SUBSTR(@b,1,3) as c from access_log limit 10;

-- 另一种用法
set @hello =1;
select @hello;

但是，gp中是否有类似的语法？测试了，貌似不行。

联表+更新

两张表进行联合，用其中的一张表的字段更新另外一张表的字段。

UPDATE demo1 LEFT JOIN demo2 on demo1.id = demo2.id set  demo1.title = demo2.title  , demo1.content = demo2.content ;

其实，如果没有这个思路，我们也可以，直接先查最终结果，（这个貌似比较好查），然后将查询的结果插入到一张新的表中。再更新表的名称，为目标表。

其他，可以参见：https://blog.csdn.net/u012604745/article/details/80642015

查询表结构

参见文章：https://www.cnblogs.com/yybrhr/p/9779972.html

SELECT
    COLUMN_NAME , -- "字段名称",
    COLUMN_TYPE , -- "字段类型长度",
    -- IF(EXTRA="auto_increment",CONCAT(COLUMN_KEY,"(", IF(EXTRA="auto_increment","自增长",EXTRA),")"),COLUMN_KEY) "主外键",
    -- IS_NULLABLE "空标识",
    COLUMN_COMMENT -- "字段说明"
    FROM
        information_schema.COLUMNS
    -- 数据库名：jn_power 表名 rpt_cap_hour_ammeter_201810
WHERE TABLE_SCHEMA = '$dbname' AND TABLE_NAME = '$table';

字符串字段聚合

-- CONCAT_WS(',',hour_detail)   这个只能拼接多个字段，不适合聚合

-- 聚合，并支持顺序，postgresql肯定适用
GROUP_CONCAT(hour_detail order by id asc SEPARATOR ',') as details

字符串分隔成数组

有这么一个需求，爬虫爬取的时候，数据没有拆分成多条。大概的样式是：

字段a1 字段a2 字段a3 字段b1 字段b2 字段b3

其实上面的结果，类似于聚合后的逆运算。那怎么将上面的数据拆分成多条呢？

mysql本身的话，只能借助于存储过程了，看了一眼，也挺难理解的。

http://www.jquerycn.cn/a_13260?_t=t

最终，换成了 php来处理。explode + array_chunk

<?php

$fi = fopen("导出.csv",'r');
$csv = fopen("mu.csv",'w');
fputcsv($csv,['bm','type','juwei']);
while(($line = fgets($fi)) !==false){
    $line = trim($line);
    $arr = explode(' ',$line);
    $chunk = array_chunk($arr,3);
    foreach($chunk as $k=>$v){
        fputcsv($csv,$v);
    } 
}
fclose($fi);
fclose($csv);

分组，聚合未参与分组的字段

注意，max(nickname)，max(job_number) 这两个字段，其实并没有参与聚合，但它实际上跟user_id相关，它参与了分组，懒得再用子查询，再关联了。故，使用了聚合函数，伪计算。

CREATE TABLE test.xitong_stat AS 
select 
    xitong,
    user_id,
    count(*) as cnt,
    max(nickname) as nickname,
    max(job_number) as job_number ,
    min(created_at) as start_time,
    max(created_at) as last_time
from test.user_xitong group by xitong,user_id ;