Mysql （7）查询 删除重复

1.去除多列的重复值后返回特定的列

select *
FROM
(
select *
FROM
youku_mobile Q
WHERE
Q.videoID != 'NoID' and Q.videoID != 'null'
)A
GROUP BY
A.mac,A.videoID,A.action
ORDER BY id

2.由一张表的数据更新另一张表的字段

UPDATE youku_raw R,youku_mobile C
SET
R.start_time = C.time
WHERE
C.mac=R.mac AND C.videoID=R.video_id1 and C.action = "begin"

3.有几个字段相同的行中取出某个字段最大的行

select A.*
from
youku_PC_record A
where
A.duration_3 =
(
    select max(B.duraiton_3)
    from
    youku_PC_record B
    where
    B.video_id1 = A.video_id1
)
--order by   #加不加都可以
--A.duration_3 desc

4.删除重复记录

用一条sql语句来实现
   1）找到要删除的数据 然后删除这些数据。具体实现如下，
      delete users as a
       from users as a,
       (
            from users 
            group by name 
            having count(name) > 1
      ) as b
      where 
       a.name = b.name
      and
     a.id <> b.id;

 加上 having count(name) > 1 可以避免扫描没有重复的记录，提高效率

   2）找到要保留的数据 然后用not in 来删除不再这些数据中的记录。大家很容易就想到如下的sql语句：
      delete from users where id not in ( select min(id)  from users group by name ); 
但是mysql删除动作不能带有本表的查询动作，
意思是你删除users表的东西不能以users表的信息为条件 
所以这个语句会报错，执行不了。只要通过创建临时表作为查询条件。
具体实现如下：

delete from
users 
WHERE
id
not in 
( 
    select * 
    from 
    ( 
      select min(id)  
        from 
        users 
        group by name
     )B
);

#我写的：
DELETE FROM
youku_PC_event
WHERE
id not IN
(
SELECT *
FROM
    (SELECT MIN(id) id
        FROM
        youku_PC_event
        GROUP BY mac,time
    )B
)