MySQL 常用语句（查重，分区间查询）

    1. 查重

       select * from people  where peopleId in (select peopleId from people group by peopleId having count(peopleId) > 1)  
      select * from Users where UserId in (select UserId from Users  group by UserId having count(UserId) > 1)  

      不过在数据量过大的时候查询的速度会非常慢
      select count(peopleId) from people 
      select count(distinct peopleId) from people  
      我们可以用上面两条语句的结果进行对比判断是否存在重复数据
    2. 分区间

       

       业务场景：统计消费总金额大于1000元的，800到1000元的，500到800元的，以及500元以下的人数。

      SELECT COUNT(CASE WHEN IFNULL(total_money,0) >=1000 THEN a.customer_id END) AS '>1000'
      ,COUNT(CASE WHEN IFNULL(total_money,0) >=800 AND IFNULL(total_money,0) <1000 THEN a.customer_id END) AS '800~1000'
      ,COUNT(CASE WHEN IFNULL(total_money,0) >=500 AND IFNULL(total_money,0) <800 THEN a.customer_id END) AS '500~800'
      ,COUNT(CASE WHEN IFNULL(total_money,0) <500  THEN a.customer_id END) AS '<500'
FROM mc_userdb.`customer_login` a
LEFT JOIN
( SELECT customer_id,SUM(order_money) AS total_money
  FROM mc_orderdb.`order_master` GROUP BY customer_id) b
ON a.`customer_id`=b.`customer_id`

3. 现在要统计：<50、50-60、60-70、70-80、80-90、90-100、>=100分数区间的人数；
利用 INTERVAL 划出7个区间；
再利用 elt 函数将7个区间分别返回一个列名，如下SQL：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
 
mysql> SELECT elt(INTERVAL(score, 0, 50, 60, 70, 80, 90, 100), '<50', '50-60', '60-70', '70-80', '80-90', '90-100', '>=100') as score_level, count(name) as counts
    -> FROM class
    -> GROUP BY elt(INTERVAL(score, 0, 50, 60, 70, 80, 90, 100), '<50', '50-60', '60-70', '70-80', '80-90', '90-100', '>=100');
+-------------+--------+
| score_level | counts |
+-------------+--------+
| 50-60       |      1 |
| 60-70       |      3 |
| 70-80       |      1 |
| 80-90       |      2 |
| 90-100      |      1 |
| <50         |      1 |
| >=100       |      1 |
+-------------+--------+
7 rows in set (0.00 sec)

 4. update select用法

UPDATE user_online_month_atu a

INNER JOIN (
SELECT
user_id,
sum(c.online_times) as online_times,
SUM(c.login_count) as login_count,
Sum(c.view_page_count) as view_page_count,
LEFT(c.log_date,length(c.log_date) - 2) as date
FROM
user_online_time_atu c
GROUP BY
c.user_id ,date
) b ON a.user_id = b.user_id AND a.`month`=b.date
SET a.online_time = b.online_times ,a.login_count=b.login_count,a.view_page_count=b.view_page_count
 
5. 删除空行
我有一个包含超过100000个数据元素的表,但其中有近350个空白行.如何使用phpmyadmin删除此空白行？手动删除是一项繁琐的工作.
 
一般答案是：

 

 

DELETE FROM table_name WHERE some_column = '';

要么

 

DELETE FROM table_name WHERE some_column IS NULL;

posted on 2020-12-08 11:17  暮云寨  阅读(259)  评论(0)  编辑  收藏  举报

导航