mysql开发技巧

一.如何进行列转换

cross join

eg:Select * from (

                    select sum(kills) as '沙僧'  from user1 a join user_kills b on a.id = b.user_id and a.'user_name'='沙僧' ) a cross join (

                     select sum(kills) as '猪八戒'  from user1 a join user_kills b on a.id = b.user_id and a.'user_name'='猪八戒 ' ) b cross join (

                      select sum(kills) as '孙悟空'  from user1 a join user_kills b on a.id = b.user_id and a.'user_name'='孙悟空 ' ) c

二.从数据表晨随机取N条数据

        mysql:    select * from tablename order by rand() limit 10

       mqlserver:    select top 10 * from tablename order by NEWID()

三.SQL查找出一个字段重复的值（每个重复的值取出一个）

      select * from dealer a where id=(select min(id) from dealer where province_id=a.province_id)

四.SQL not in 优化

select * fromipcstorage.cachetmpaleft join (select lblid from ipcconfigdb.labelfilter where ftype=2 )bon  a.lblid = b.lblid whereb.lblid is null

5.sql  日期转换

   FROM_UNIXTIME()

   FROM_UNIXTIME(a.activity_start_time, \'%Y-%c-%d %h:%i:%s\' ) as activity_start_time,FROM_UNIXTIME(a.activity_end_time, \'%Y-%c-%d                  %h:%i:%s\' ) as activity_end_time

6.sql  CASE WHEN

UPDATE `juo_card_group` SET `is_package` = (CASE WHEN `is_package` = '1' THEN '1,2' ELSE '1' END);

7.sql  删除重复的数据https://www.cnblogs.com/wangfuyou/p/6058169.html

    DELETE

FROM

    ips_activity_product

WHERE

    id IN (

        SELECT

            b.id

        FROM

            (

                SELECT

                    a.id

                FROM

                    ips_activity_product a

                WHERE

                    (a.activity_child_id, a.sku) IN (

                        SELECT

                            activity_child_id,

                            sku

                        FROM

                            ips_activity_product

                        GROUP BY

                            activity_child_id,

                            sku

                        HAVING

                            count(*) > 1

                    )

                AND id NOT IN (

                    SELECT

                        min(id)

                    FROM

                        ips_activity_product

                    GROUP BY

                        activity_child_id,

                        sku

                    HAVING

                        count(*) > 1

                )

            ) b

    )