Mysql查出前10位消费次数小于2的会员(聚焦面试题)

create table info_user(
    U_id     int(11)    not null  primary key   auto_increment,
    U_name   varchar(100)   not null

);

insert into info_user(U_name) values("张三"),("李四"),("王五");


create table info_user_consumer(
    Id       int(11)    not null   primary key   auto_increment,
    C_id     int (11)   not null,
    U_id     int(11)    not null
    

);

insert into info_user_consumer(C_id,U_id) values1(1,1),(2,1),(3,2),(4,2),(5,2),(6,3),(7,1);






[子查询充当字段]
查询所有学生信息，但显示班级的名称
select
 stuId,
 (select claName from classInfo where classInfo.claId = student.classId) as className,
 stuName,
 age
 from student;
查询所有班级信息，但还要显示每个班级的学生个数
select 
 claId,
 claName,
 (select count(*) from student where classInfo.claId=student.classId) as stuCount
 from classInfo;
查询歌手信息,并显示歌手开演唱会的次数
select 
 gsId,
 gsName,
 (select count(*) from ych a where a.gsId=b.gsId) as ychCount
 from geshou b;

 
 select * from student
 where classId in (select claId from classInfo where claName!='php1303' and claName!='php1307');
 
 
 
select (select U_id from ((select U_id,count(C_id) C_count from info_user_consumer group by U_id having C_count<2) b)) U_id,U_name,C_count from (select U_id,count(C_id) C_count from info_user_consumer group by U_id having C_count<2) b inner join info_user on b.U_id = info_user.U_id;
 
select U_id,U_name,C_count from (select U_id,count(C_id) C_count from info_user_consumer group by U_id having C_count<2) b inner join info_user on b.U_id = info_user.U_id;
 
select U_id,count(C_id) C_count from info_user_consumer group by U_id having C_count<2; 

select U_id from ((select U_id,count(C_id) C_count from info_user_consumer group by U_id having C_count<2) b);

 
select U_id,count(C_id) C_count from info_user_consumer group by U_id having C_count<2;
 
 mysql> select U_id,count(C_id) C_count from info_user_consumer group by U_id having C_count<2;







Microsoft Windows XP [版本 5.1.2600]
(C) 版权所有 1985-2001 Microsoft Corp.

C:\Documents and Settings\Administrator>mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.5.36 MySQL Community Server (GPL)

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select (select U_id from ((select U_id,count(C_id) C_count from info_user_consumer group by U_id having C_count<2) b)),U_name,C_count from (select U_id,count(C_id) C_count from info_user_consumer group by U_id having C_count<2) b inner join info_user on b.U_id = info_user.U_id;
ERROR 1046 (3D000): No database selected
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| cdcol              |
| mysql              |
| performance_schema |
| phpmyadmin         |
| sondemo            |
| test               |
| webauth            |
| xilian168          |
+--------------------+
9 rows in set (0.00 sec)

mysql> use sondemo;
Database changed
mysql> select (select U_id from ((select U_id,count(C_id) C_count from info_user_consumer group by U_id having C_count<2) b)),U_name,C_count from (select U_id,count(C_id) C_count from info_user_consumer group by U_id having C_count<2) b inner join info_user on b.U_id = info_user.U_id;
+-----------------------------------------------------------------------------------------------------------------+--------+---------+
| (select U_id from ((select U_id,count(C_id) C_count from info_user_consumer group by U_id having C_count<2) b)) | U_name | C_count |
+-----------------------------------------------------------------------------------------------------------------+--------+---------+
|                                                                                                               3 | ??     |       1 |
+-----------------------------------------------------------------------------------------------------------------+--------+---------+
1 row in set (0.02 sec)

mysql> select (select U_id from ((select U_id,count(C_id) C_count from info_user_consumer group by U_id having C_count<2) b)) U_id,U_name,C_count from (select U_id,count(C_id) C_count from info_user_consumer group by U_id having C_count<2) b inner join info_user on b.U_id = info_user.U_id;
+------+--------+---------+
| U_id | U_name | C_count |
+------+--------+---------+
|    3 | ??     |       1 |
+------+--------+---------+
1 row in set (0.02 sec)

mysql> select U_id,U_name,C_count from (select U_id,count(C_id) C_count from info_user_consumer group by U_id having C_count<2) b inner join info_user on b.U_id = info_user.U_id;
ERROR 1052 (23000): Column 'U_id' in field list is ambiguous
mysql> explain select U_id,U_name,C_count from (select U_id,count(C_id) C_count from info_user_consumer group by U_id having C_count<2) b inner join info_user on b.U_id = info_user.U_id;
ERROR 1052 (23000): Column 'U_id' in field list is ambiguous
mysql> explain select b.U_id,U_name,C_count from (select U_id,count(C_id) C_count from info_user_consumer group by U_id having C_count<2) b inner join info_user on b.U_id = info_user.U_id;
+----+-------------+--------------------+--------+---------------+---------+---------+-------+------+---------------------------------+
| id | select_type | table              | type   | possible_keys | key     | key_len | ref   | rows | Extra                           |
+----+-------------+--------------------+--------+---------------+---------+---------+-------+------+---------------------------------+
|  1 | PRIMARY     | <derived2>         | system | NULL          | NULL    | NULL    | NULL  |    1 |                                 |
|  1 | PRIMARY     | info_user          | const  | PRIMARY       | PRIMARY | 4       | const |    1 |                                 |
|  2 | DERIVED     | info_user_consumer | ALL    | NULL          | NULL    | NULL    | NULL  |    7 | Using temporary; Using filesort |
+----+-------------+--------------------+--------+---------------+---------+---------+-------+------+---------------------------------+
3 rows in set (1.78 sec)

mysql> select b.U_id,U_name,C_count from (select U_id,count(C_id) C_count from info_user_consumer group by U_id having C_count<2) b inner join info_user on b.U_id = info_user.U_id;
+------+--------+---------+
| U_id | U_name | C_count |
+------+--------+---------+
|    3 | ??     |       1 |
+------+--------+---------+
1 row in set (0.66 sec)

mysql> explain select (select U_id from ((select U_id,count(C_id) C_count from info_user_consumer group by U_id having C_count<2) b)) U_id,U_name,C_count from (select U_id,count(C_id) C_count from info_user_consumer group by U_id having C_count<2) b inner join info_user on b.U_id = info_user.U_id;
+----+-------------+--------------------+--------+---------------+---------+---------+-------+------+---------------------------------+
| id | select_type | table              | type   | possible_keys | key     | key_len | ref   | rows | Extra                           |
+----+-------------+--------------------+--------+---------------+---------+---------+-------+------+---------------------------------+
|  1 | PRIMARY     | <derived4>         | system | NULL          | NULL    | NULL    | NULL  |    1 |                                 |
|  1 | PRIMARY     | info_user          | const  | PRIMARY       | PRIMARY | 4       | const |    1 |                                 |
|  4 | DERIVED     | info_user_consumer | ALL    | NULL          | NULL    | NULL    | NULL  |    7 | Using temporary; Using filesort |
|  2 | SUBQUERY    | <derived3>         | system | NULL          | NULL    | NULL    | NULL  |    1 |                                 |
|  3 | DERIVED     | info_user_consumer | ALL    | NULL          | NULL    | NULL    | NULL  |    7 | Using temporary; Using filesort |
+----+-------------+--------------------+--------+---------------+---------+---------+-------+------+---------------------------------+
5 rows in set (0.00 sec)

mysql> select b.U_id,U_name,C_count from (select U_id,count(C_id) C_count from info_user_consumer group by U_id having C_count<2) b inner join info_user on b.U_id = info_user.U_id;
+------+--------+---------+
| U_id | U_name | C_count |
+------+--------+---------+
|    3 | ??     |       1 |
+------+--------+---------+
1 row in set (0.00 sec)

mysql> explain select b.U_id,U_name,C_count from (select U_id,count(C_id) C_count from info_user_consumer group by U_id having C_count<2) b inner join info_user on b.U_id = info_user.U_id;
+----+-------------+--------------------+--------+---------------+---------+---------+-------+------+---------------------------------+
| id | select_type | table              | type   | possible_keys | key     | key_len | ref   | rows | Extra                           |
+----+-------------+--------------------+--------+---------------+---------+---------+-------+------+---------------------------------+
|  1 | PRIMARY     | <derived2>         | system | NULL          | NULL    | NULL    | NULL  |    1 |                                 |
|  1 | PRIMARY     | info_user          | const  | PRIMARY       | PRIMARY | 4       | const |    1 |                                 |
|  2 | DERIVED     | info_user_consumer | ALL    | NULL          | NULL    | NULL    | NULL  |    7 | Using temporary; Using filesort |
+----+-------------+--------------------+--------+---------------+---------+---------+-------+------+---------------------------------+
3 rows in set (0.00 sec)

mysql>