1. 首頁 > 生活 > >

數據庫多行合并成一行 sql把多行數據合并成一行

小知識



文章插圖
數據庫多行合并成一行 sql把多行數據合并成一行

文章插圖
建庫建表
drop database if exists jbxx;create database jbxx;use jbxx;create table grjbxx(ryid int auto_increment not null primary key,sfzh varchar(50) not null,hbh varchar(50) not null,xm varchar(50) not null,xb varchar(4) not null,mz varchar(50) not null,csrq date not null,hj varchar(50) not null,zz varchar(50) not null,lxdh varchar(20) not null,gmt_create datetime not null default current_timestamp);insert into grjbxx(sfzh,hbh,xm,xb,mz,csrq,hj,zz,lxdh) values('37032219000101001x','3703220105','張三','男','漢族','1900-02-05','山東高青','高青縣*小區*號樓*單元*戶號','1390000000');select * from grjbxx;查詢xm的分組統計情況
SELECT xm,count(*) from grjbxx group by xm；
只顯示xm重復行的統計情況
SELECT xm,count(*) from grjbxx group by xm having count(*)>1;
顯示xm重復的所有記錄信息
SELECT * from grjbxx where xm in(SELECT xm from grjbxx group by xm having count(xm)>1);
顯示sfzh重復的所有記錄信息
SELECT * from grjbxx where sfzh in(SELECT sfzh from grjbxx group by sfzh having count(sfzh)>1);
刪除表中多余的sfzh重復記錄，重復記錄是根據單個字段（sfzh）來判斷，只留有ryid最大的記錄
delete from grjbxxwhere sfzh in(SELECT sfzh from grjbxx group by sfzh having count(sfzh)>1)and ryid not in(select max(ryid) from grjbxx group by sfzh having count(sfzh)>1);數據庫修改
use mytest;--打開mytest數據庫SELECT * from stu;--查詢STU表全部記錄ALTER TABLE stu MODIFY gmt_modified datetime NOT NULL ON UPDATE CURRENT_TIMESTAMP; --修改gmt_modified字段非空，更新時自動更新ALTER TABLE stu MODIFY gmt_modified datetime NOT NULL;--修改gmt_modified字段非空ALTER TABLE stu change gmt_create gmt_create datetime NOT NULL DEFAULT CURRENT_TIMESTAMP; --修改gmt_create是datetime類型，非空，默認值自動時間desc stu;--顯示stu表結構UPDATE stu set gmt_modified='2018-08-01 01:59:01';--更新所有的gmt_modified的值truncate stu;--清空stu表，僅保留數據結構合并多行數據到一行，hbh相同的合并到一行
SELECT hbh,count(xm) as 人數 ,GROUP_CONCAT(xm) as 成員 from grjbxx GROUP BY hbh;
查詢前5條記錄
select * from grjbxx LIMIT 5;
統計多個數據，利用虛擬表dual顯示
SELECT(SELECT count(DISTINCT sfzh) from grjbxx) 總人數, (SELECT count(DISTINCT sfzh) from sb where sblb='01') 社保 from DUAL;查詢date字段日期范圍內的記錄，表示 1980-01-01 00:00:00 –1999-12-31 00:00:00，兩個邊界都包括，這段時間范圍的記錄，
select * from grjbxx WHERE csrq BETWEEN ‘1980-01-01’ and ‘1999-12-31’;
查詢一段時間內的記錄
select * from grjbxx WHERE gmt_create >=’2019-01-23 00:00:00′ and gmt_create<= ‘2019-01-23 14:53:06’;
日期轉換
SELECT DATE_FORMAT(20140614162458,'%Y-%m-%d %H:%i:%s');SELECT DATE_FORMAT('2014-02-24','%Y-%m-%d %H:%i:%s');SELECT DATE_FORMAT('2014-02-24','%Y-%m-%d')SELECT str_to_date('2014-02-24','%Y-%m-%d')SELECT DATE_FORMAT(gmt_create,'%Y-%m-%d') from grjbxx;ORCAL 日期轉換select to_date('2004-05-07 13:23:44','yyyy-mm-dd hh24:mi:ss') from dual使用substr函數查詢，SUBSTR(str,pos,len)表示：從pos開始的位置，截取len個字符(空白也算字符) 。
SELECT * from grjbxx where substr(sfzh,1,6)=’370322′
REPLACE(str,old