那些年很迷的sql

谁践踏了优雅 2021-09-15 10:08 341阅读 0赞

**1、distinct：**

**sql：** select \* from user

![这里写图片描述][70]

**为了对username去重：**

**sql：** select distinct username from user

![这里写图片描述][70 1]

**为了对username去重，并且查询其他字段：**

**sql：** select distinct username, usertype,userpassword from user

![这里写图片描述][70 2]

**问题出现了：** distinct 之后还要查询其他字段的时候，distinct 不起作用了

**sql：** select distinct username, usertype,userpassword from user group by username

![这里写图片描述][70 3]

**sql：** select distinct username, usertype,userpassword from user group by userpassword

![这里写图片描述][70 4]

**sql：** select distinct username, userpassword from user

![这里写图片描述][70 5]

**分析：**

group by username之后确实对username去重了，但是group by userpassword之后只去重了username相同并且userpassword相同的记录,如果没有group by 的话distinct将会对username和userpassword全部相同的进行去重

**猜想：**

可能在存在distinct的查询语句中，如果想查询distinct之外的其他字段，并且结果中不能有这个字段的相同之，就必须group by与distinct相同的字段

**2、将一张表的一个字段赋值给另一张表的一个字段：**

**sql:** update cs\_user r set r.username=(select h.tel2 from cs\_human h where h.id=r.objid and h.deleted=0) and r.deleted=0

**问题出现了：** 我想做的是将cs\_human表中的tel2字段赋值给cs\_user表中的username，但是执行完上面的sql后，cs\_user表中的username字段全变成0或者1了。。。之后就换了一种写法，就实现了。。。  
**sql:** update cs\_user r,cs\_human h set r.username =h.tel2 where r.objid=h.id

**3、group\_concat（oracle中的wm\_concat）：**

**sql脚本：**

DROP TABLE IF EXISTS `test1`;
    CREATE TABLE `test1` ( `id` varchar(32) DEFAULT NULL, `name` varchar(255) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    
    -- ----------------------------
    -- Records of test1
    -- ----------------------------
    INSERT INTO `test1` VALUES ('1', '小明');
    INSERT INTO `test1` VALUES ('2', '小红');
    
    -- ----------------------------
    -- Table structure for test2
    -- ----------------------------
    DROP TABLE IF EXISTS `test2`;
    CREATE TABLE `test2` ( `id` varchar(255) DEFAULT NULL, `tel` varchar(255) DEFAULT NULL, `testid` varchar(255) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    
    -- ----------------------------
    -- Records of test2
    -- ----------------------------
    INSERT INTO `test2` VALUES ('1', '9', '1');
    INSERT INTO `test2` VALUES ('2', null, '1');
    INSERT INTO `test2` VALUES ('3', '8', '1');
    INSERT INTO `test2` VALUES ('4', '7', '2');

**① group by 问题：**如果group\_concat（字段1）之后不group by的话，结果永远只有一条记录，他会将所有的字段1（有值的字段1，没值的字段1直接被忽略）用，拼接起来组成一个查询结果字段

SELECT t.id, GROUP_CONCAT(tt.tel) FROM test1 t LEFT JOIN test2 tt on t.id=tt.testid

![这里写图片描述][70 6]

加上group by之后：

SELECT t.id, GROUP_CONCAT(tt.tel) FROM test1 t LEFT JOIN test2 tt on t.id=tt.testid GROUP BY t.id

![这里写图片描述][70 7]

**② null值问题：**不知道各位观众老爷有没有发现一个问题，就是上面的查询结果是不是应该要是8，，9呢，而不应该是8，9？？？是的，group\_concat这个函数他自己是忽略null值的，如果他遇到null值就会直接跳过。

**解决方法：**还记不记得在 [https://blog.csdn.net/tuesdayma/article/details/78414511][https_blog.csdn.net_tuesdayma_article_details_78414511] 中提到过的ifnull函数（oracle中的nvl()函数），他就能解决null值问题。而且不存在顺序问题（即如果两个字段都是用了这个函数，他们的结果拼接顺序是一样的）

SELECT t.id, GROUP_CONCAT(ifnull(tt.id,'-'))ids ,GROUP_CONCAT(ifnull(tt.tel,'-')) tel FROM test1 t LEFT JOIN test2 tt on t.id=tt.testid GROUP BY t.id

![这里写图片描述][70 8]

[70]: /images/20210511/5f704d8612ed43468106bd3f6cb577d8.png
[70 1]: /images/20210511/a465a40ef2e84d0db884d48d9419a1e0.png
[70 2]: /images/20210511/c120a47a633f4b86a134e368d6d4c38b.png
[70 3]: /images/20210511/cc22b2d1f61e44ca8277c09633e99ab6.png
[70 4]: /images/20210511/240ca81e47d647c6bc0e28edc9e95351.png
[70 5]: /images/20210511/7b0b199622e34a59ada73571f5f4706b.png
[70 6]: /images/20210511/e6fa33aa639b4316a4b850a995974af5.png
[70 7]: /images/20210511/e2bc9a72c1194624b90cf35e0e00d1ae.png
[https_blog.csdn.net_tuesdayma_article_details_78414511]: https://blog.csdn.net/tuesdayma/article/details/78414511
[70 8]: /images/20210511/eccd53c3a6cc4767869ad6e0a7971012.png