查询主键表通过外键数量排序

可以使用以下 SQL 语句进行查询：

SELECT main_table.pk_id, COUNT(foreign_table.fk_id) AS count
FROM main_table
LEFT JOIN foreign_table
ON main_table.pk_id = foreign_table.fk_id
GROUP BY main_table.pk_id
ORDER BY COUNT(foreign_table.fk_id) DESC;

其中，main_table 是主键表，foreign_table 是外键表，pk_id 是主键字段名，fk_id 是外键字段名。以上查询语句会按照每个主键对应的外键数量降序排列，并返回每个主键及其对应的外键数量。

相关问题

管理员admin表：id 编号（从一开始） 主键，a_id 姓名，a_password 密码，a_tel 联系方式； 宿舍楼dormitory表：C_id 楼栋 主键，s_gender 楼住性别，id 管理员 外键 ；宿舍room表：roomid 宿舍号 ,rfloor 楼层,num 空余床位,price 价格 ；学生信息stu_info表：id 编号 主键,sno 学号,sname 姓名,age 年龄,gender 性别,s_phone电话,dept_id学院 外键,g_id年级 外键,C_id 楼栋 外键,s_roomid 宿舍号 外键； 院系dept表：dept_id 编号 主键,name 院系名称 ；年级grade表：g_id 编号 主键,grade 年级；对这些学生根据院系和年级进行宿舍分配，假设一个宿舍只能住四个人 mysql语句

首先，我们需要创建一个视图，以便方便地获取每个宿舍的空余床位数量：

CREATE VIEW room_vacancy AS
SELECT r.roomid, COUNT(*) AS num_vacant
FROM room r
LEFT JOIN stu_info s ON r.roomid = s.s_roomid
WHERE s.s_roomid IS NULL
GROUP BY r.roomid;

接下来，我们可以使用以下 SQL 语句来进行宿舍分配：

INSERT INTO stu_info (sno, sname, age, gender, s_phone, dept_id, g_id, C_id, s_roomid)
SELECT s.sno, s.sname, s.age, s.gender, s.s_phone, s.dept_id, s.g_id, d.C_id, r.roomid
FROM (
  SELECT s.*, ROW_NUMBER() OVER (PARTITION BY s.dept_id, s.g_id ORDER BY s.sno) AS row_num
  FROM stu_info s
) AS s
JOIN dept d ON s.dept_id = d.dept_id
JOIN grade g ON s.g_id = g.g_id
JOIN room_vacancy rv ON rv.num_vacant >= 4
JOIN room r ON rv.roomid = r.roomid
WHERE s.s_roomid IS NULL AND s.row_num <= 4;

这个 SQL 语句会将每个院系和年级的学生按照学号顺序进行排序，并将他们分配到空余床位数大于等于 4 的宿舍中。每个宿舍最多只会分配 4 个学生。

使用python中的pymsql完成如下：表结构与数据创建 1. 建立 `users` 表和 `orders` 表。 `users` 表有用户ID、用户名、年龄字段，（id，name，age） `orders` 表有订单ID、订单日期、订单金额，用户id字段。（id，order_date，amount，user_id） 2 两表的id作为主键，`orders` 表用户id为users的外键 3 插入数据 `users` (1, '张三', 18), (2, '李四', 20), (3, '王五', 22), (4, '赵六', 25), (5, '钱七', 28); `orders` (1, '2021-09-01', 500, 1), (2, '2021-09-02', 1000, 2), (3, '2021-09-03', 600, 3), (4, '2021-09-04', 800, 4), (5, '2021-09-05', 1500, 5), (6, '2021-09-06', 1200, 3), (7, '2021-09-07', 2000, 1), (8, '2021-09-08', 300, 2), (9, '2021-09-09', 700, 5), (10, '2021-09-10', 900, 4); 查询语句 1. 查询订单总金额 2. 查询所有用户的平均年龄，并将结果四舍五入保留两位小数。 3. 查询订单总数最多的用户的姓名和订单总数。 4. 查询所有不重复的年龄。 5. 查询订单日期在2021年9月1日至9月4日之间的订单总金额。 6. 查询年龄不大于25岁的用户的订单数量，并按照降序排序。 7. 查询订单总金额排名前3的用户的姓名和订单总金额。 8. 查询订单总金额最大的用户的姓名和订单总金额。 9. 查询订单总金额最小的用户的姓名和订单总金额。 10. 查询所有名字中含有“李”的用户，按照名字升序排序。 11. 查询所有年龄大于20岁的用户，按照年龄降序排序，并只显示前5条记录。 12. 查询每个用户的订单数量和订单总金额，并按照总金额降序排序。

这里是使用 Python 的 PyMySQL 完成上述任务的代码：

import pymysql

# 连接数据库
conn = pymysql.connect(host='localhost', port=3306, user='root', password='123456', database='test')

# 创建游标
cur = conn.cursor()

# 创建 users 表
cur.execute('''
    CREATE TABLE users (
        id INT PRIMARY KEY,
        name VARCHAR(20),
        age INT
    )
''')

# 创建 orders 表
cur.execute('''
    CREATE TABLE orders (
        id INT PRIMARY KEY,
        order_date DATE,
        amount INT,
        user_id INT,
        FOREIGN KEY (user_id) REFERENCES users(id)
    )
''')

# 插入数据
users_data = [
    (1, '张三', 18),
    (2, '李四', 20),
    (3, '王五', 22),
    (4, '赵六', 25),
    (5, '钱七', 28)
]

orders_data = [
    (1, '2021-09-01', 500, 1),
    (2, '2021-09-02', 1000, 2),
    (3, '2021-09-03', 600, 3),
    (4, '2021-09-04', 800, 4),
    (5, '2021-09-05', 1500, 5),
    (6, '2021-09-06', 1200, 3),
    (7, '2021-09-07', 2000, 1),
    (8, '2021-09-08', 300, 2),
    (9, '2021-09-09', 700, 5),
    (10, '2021-09-10', 900, 4)
]

for user in users_data:
    cur.execute('INSERT INTO users VALUES (%s, %s, %s)', user)

for order in orders_data:
    cur.execute('INSERT INTO orders VALUES (%s, %s, %s, %s)', order)

# 提交事务
conn.commit()

# 查询语句
# 1. 查询订单总金额
cur.execute('SELECT SUM(amount) FROM orders')
print(cur.fetchone()[0])

# 2. 查询所有用户的平均年龄，并将结果四舍五入保留两位小数。
cur.execute('SELECT ROUND(AVG(age), 2) FROM users')
print(cur.fetchone()[0])

# 3. 查询订单总数最多的用户的姓名和订单总数。
cur.execute('''
    SELECT users.name, COUNT(*) AS total_orders
    FROM users JOIN orders ON users.id = orders.user_id
    GROUP BY users.id
    ORDER BY total_orders DESC
    LIMIT 1
''')
print(cur.fetchone())

# 4. 查询所有不重复的年龄。
cur.execute('SELECT DISTINCT age FROM users')
print([row[0] for row in cur.fetchall()])

# 5. 查询订单日期在2021年9月1日至9月4日之间的订单总金额。
cur.execute('SELECT SUM(amount) FROM orders WHERE order_date BETWEEN "2021-09-01" AND "2021-09-04"')
print(cur.fetchone()[0])

# 6. 查询年龄不大于25岁的用户的订单数量，并按照降序排序。
cur.execute('''
    SELECT users.name, COUNT(*) AS total_orders
    FROM users JOIN orders ON users.id = orders.user_id
    WHERE age <= 25
    GROUP BY users.id
    ORDER BY total_orders DESC
''')
print(cur.fetchall())

# 7. 查询订单总金额排名前3的用户的姓名和订单总金额。
cur.execute('''
    SELECT users.name, SUM(amount) AS total_amount
    FROM users JOIN orders ON users.id = orders.user_id
    GROUP BY users.id
    ORDER BY total_amount DESC
    LIMIT 3
''')
print(cur.fetchall())

# 8. 查询订单总金额最大的用户的姓名和订单总金额。
cur.execute('''
    SELECT users.name, SUM(amount) AS total_amount
    FROM users JOIN orders ON users.id = orders.user_id
    GROUP BY users.id
    ORDER BY total_amount DESC
    LIMIT 1
''')
print(cur.fetchone())

# 9. 查询订单总金额最小的用户的姓名和订单总金额。
cur.execute('''
    SELECT users.name, SUM(amount) AS total_amount
    FROM users JOIN orders ON users.id = orders.user_id
    GROUP BY users.id
    ORDER BY total_amount ASC
    LIMIT 1
''')
print(cur.fetchone())

# 10. 查询所有名字中含有“李”的用户，按照名字升序排序。
cur.execute('SELECT * FROM users WHERE name LIKE "%李%" ORDER BY name ASC')
print(cur.fetchall())

# 11. 查询所有年龄大于20岁的用户，按照年龄降序排序，并只显示前5条记录。
cur.execute('SELECT * FROM users WHERE age > 20 ORDER BY age DESC LIMIT 5')
print(cur.fetchall())

# 12. 查询每个用户的订单数量和订单总金额，并按照总金额降序排序。
cur.execute('''
    SELECT users.name, COUNT(*) AS total_orders, SUM(amount) AS total_amount
    FROM users JOIN orders ON users.id = orders.user_id
    GROUP BY users.id
    ORDER BY total_amount DESC
''')
print(cur.fetchall())

# 关闭游标和连接
cur.close()
conn.close()

注意：在运行代码之前，需要先安装 PyMySQL 模块，可以使用以下命令进行安装：

pip install pymysql