Mysql之sql開發技巧

如何進行行轉列

場景：報表統計、彙總顯示

報表統計示例：

create table t_amount(
    create_time date not null,
    amount float(, ) not null default 
);

insert into t_amount values('2012-10-01', ), ('2012-10-10', ), ('2012-10-21', );
insert into t_amount values('2012-11-01', ), ('2012-11-10', ), ('2012-11-21', );
insert into t_amount values('2012-12-02', ), ('2012-12-10', ), ('2012-12-21', );


----行轉列
select '銷售額' as '月份', 
    sum(case month(create_time) when  then amount end) as '10月',
    sum(case month(create_time) when  then amount end) as '11月',
    sum(case month(create_time) when  then amount end) as '12月'
from t_amount;
           

效果如下圖所示：

彙總統計示例：

create table stu_01(
    name varchar() not null,
    course varchar() not null,
    score float(,) not null default 
);

insert into stu_01 values('張三', '國文', );
insert into stu_01 values('張三', '數學', );
insert into stu_01 values('張三', '英語', );
insert into stu_01 values('李四', '國文', );
insert into stu_01 values('李四', '數學', );
insert into stu_01 values('李四', '英語', );
insert into stu_01 values('王五', '國文', );
insert into stu_01 values('王五', '數學', );
insert into stu_01 values('王五', '英語', );
select * from stu_01;

--行轉列
select name, max(case course when '國文' then score end) as '國文',
       max(case course  when '數學' then score end) as '數學',
       max(case course  when '英語' then score end) as '英語'
from stu_01 group by name;


--行轉列(帶總分和平均分)
select name, max(case course when '國文' then score end) as '國文',
       max(case course  when '數學' then score end) as '數學',
       max(case course  when '英語' then score end) as '英語',
       sum(score) as '總分',
       avg(score) as '平均分'
from stu_01 group by name;
           

效果如下圖所示：

如何進進行列轉行

場景：表格的屬性拆分、ETL轉換

使用序清單的方式進行列轉行示例：

drop table user_01;
create table user_01(
    username varchar() not null,
    mobile varchar() not null
);
insert into user_01 values('張三', '13889278278,17628278987,19087826678');
insert into user_01 values('李四', '13628798689,13783937637');
insert into user_01 values('王五', '13039889876');


--第一步：生成一個序清單(最大的序列值需要大于上面待拆分mobile字段裡面含有元素的最大個數)
create table seq_01(
    seq_num int primary key auto_increment
);
insert into seq_01 values(),(),(),(),(),(),(),(),();
select * from seq_01;

--第二步，将序清單和稍微處理過的帶拆分的表進行内連接配接
select * from seq_01 s inner join(
    select username, concat(mobile, ',') as mobile, 
        length(mobile)-length(replace(mobile, ',', '')) + as size 
    from  user_01) u on s.seq_num<=u.size;

--第三步，将内連接配接後的表進行字元串截取操作得到最終的表
SELECT 
    username,
    REPLACE(SUBSTRING(SUBSTRING_INDEX('13889278278,17628278987,19087826678,',
                    ',',
                    s.seq_num),
            CHAR_LENGTH(SUBSTRING_INDEX('13889278278,17628278987,19087826678,',
                            ',',
                            s.seq_num - )) + ),
        ',',
        '') AS mobile
FROM
    seq_01 s
        INNER JOIN
    (SELECT 
        username,
            CONCAT(mobile, ',') AS mobile,
            LENGTH(mobile) - LENGTH(REPLACE(mobile, ',', '')) +  AS size
    FROM
        user_01) u ON s.seq_num <= u.size;
           

效果如下：

使用序清單+case-when+coalesce函數進行列轉行示例：

create table book_01(
    book_name varchar() not null,
    author varchar() not null,
    amount int unsigned
);

insert into book_01 values('java in action', 'zhangsan', ), ('c++ programming', 'lisi', ), ('python thinking', 'wangwu', );


SELECT 
    book_name,
    CASE s.seq_num
        WHEN  THEN 'author'
        WHEN  THEN 'amount'
    END AS intem,
    COALESCE(CASE s.seq_num
                WHEN  THEN author
            END,
            CASE s.seq_num
                WHEN  THEN amount
            END) AS item_value
FROM
    seq_01 s
        INNER JOIN
    book_01 b ON s.seq_num <= ;
           

效果如下：

如何生成唯一序列号

--存儲當天目前訂單号表的表
create table order_seq(
    date_str varchar() not null,
    order_sn int unsigned 
);


--訂單号生成的存儲過程
delimiter //
create procedure gener_order_seq()
begin
    declare v_cnt int unsigned;
    declare v_timestr varchar(8);
    declare rowcount bigint;
    set v_timestr=date_format(now(), '%Y%m%d');
    select round(rand()*,)+ into v_cnt;

    start transaction;
        update order_seq set order_sn=order_sn+v_cnt where date_str=v_timestr;
        if row_count()=0 then
            insert into order_seq(date_str, order_sn) values(v_timestr, v_cnt);
        end if;
        select concat(v_timestr, lpad(order_sn, , )) as order_sn from order_seq where date_str=v_timestr;
    commit;
end //
delimiter ;

--調用存儲過程生成訂單号(大約1000條/s)
call gener_order_seq;
           

如何删除重複資料

• 關鍵點一：如何判斷哪些資料重複？
• 關鍵點二：如何删除重複的資料隻保留一條？

create table user_03(
    id int unsigned not null auto_increment,
    name varchar() not null,
    primary key(id)
) engine=InnoDB charset=utf8;
insert into user_03 value(default, '張三'), (default,'李四'),(default,'小娟'),(default,'李四'), (default,'李四'), (default,'王五'),(default,'王五');


--删除重複資料，隻保留id最大的那條記錄
delete u1 from user_03 u1 left join(
        select name, max(id)  as maxid from user_03 group by name having count(name)>
) u2 on u1.name=u2.name where u1.id<u2.maxid;