V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
axwz88
V2EX  ›  程序员

最近写分页遇到一个 limit 的小问题,一直想不通 SQL 怎么写

  •  
  •   axwz88 · 2019-07-16 23:37:22 +08:00 · 4111 次点击
    这是一个创建于 1717 天前的主题,其中的信息可能已经有所发展或是发生改变。
    学校表:school
    sid school_name
    1 北京大学
    2 清华大学
    3 南京大学


    专业表:major
    mid major_name sid
    1 专业 1 1
    2 专业 2 1
    3 专业 3 2
    4 专业 4 2
    5 专业 5 3
    6 专业 6 3


    查询语句:select school_name,major_name from school s,major m where s.sid=m.sid

    查询结果:
    school_name major_name
    北京大学 专业 1
    北京大学 专业 2
    清华大学 专业 3
    清华大学 专业 4
    南京大学 专业 5
    南京大学 专业 6

    问题:怎样可以用 limit 限制只查两个学校,并且能查出学校下的所有专业?
    第 1 条附言  ·  2019-07-17 00:21:50 +08:00
    重新组织一下

    学校表:school
    https://s2.ax1x.com/2019/07/17/ZqCaCj.png

    专业表:major
    https://s2.ax1x.com/2019/07/17/ZqC0vq.png

    查询语句:select school_name,major_name from school s,major m where s.sid=m.sid

    查询结果:
    https://s2.ax1x.com/2019/07/17/ZqCcaF.png

    问题:怎样可以用 limit 限制只查两个学校,并且能查出学校下的所有专业?
    32 条回复    2019-07-18 00:08:48 +08:00
    reus
        1
    reus  
       2019-07-16 23:39:56 +08:00
    子查询啊……

    select 专业
    where 学校 in (
    select 学校
    limit 2
    )
    axwz88
        2
    axwz88  
    OP
       2019-07-16 23:40:13 +08:00
    不好意思呀,不常在 v 站发帖,编辑的时候排版好了的,然后发布后空格都被删除了,所以就有了上面看到的缩成一坨的排版
    axwz88
        3
    axwz88  
    OP
       2019-07-17 00:25:47 +08:00
    @reus 这样不行的,虽然可以限制学校数量,但这样就不能用 where 条件筛选学校了
    xiaowei0823
        4
    xiaowei0823  
       2019-07-17 00:35:47 +08:00 via iPhone
    where 条件后面可以继续 and 吧
    xaplux
        5
    xaplux  
       2019-07-17 02:39:43 +08:00
    单表查询在学校上分页,然后根据学校批量查询专业,在内存中填充到学校即可
    ninjachen
        6
    ninjachen  
       2019-07-17 03:21:07 +08:00 via Android
    感觉这个没法分页
    yuikns
        7
    yuikns  
       2019-07-17 03:51:12 +08:00
    SELECT s.school_name, m.major_name
    FROM major m
    INNER JOIN (
    SELECT sid, school_name
    FROM school
    WHERE sid < 10
    LIMIT 2
    ) as s
    ON s.sid=m.sid

    这样?
    yuikns
        8
    yuikns  
       2019-07-17 04:02:59 +08:00   ❤️ 2
    @yuikns https://www.db-fiddle.com/f/mEqnELA5rBV1n5RswchfAJ/2
    突然找到一个有趣的工具。不过貌似不支持中文
    liubian
        9
    liubian  
       2019-07-17 06:55:24 +08:00 via Android
    SELECT s.school_name , GROUP_CONCAT(m.major_name) FROM school s LEFT JOIN major m ON s.sid= m.sid GROUP BY sid LIMIT 0,2
    beginor
        10
    beginor  
       2019-07-17 07:16:22 +08:00 via Android
    给个提示,用 row_number 来
    dcty
        11
    dcty  
       2019-07-17 08:40:16 +08:00
    需求拆分为:
    1. 我需要限制查询学校的数量
    2. 我需要根据 1 拿到的学校查询所有专业
    程序能实现吧,不一定要在 SQL 上死磕。
    Alexhohom
        12
    Alexhohom  
       2019-07-17 08:55:12 +08:00 via Android
    select * from tbla a inner join tblb b on a.sid= b.sid where sid in ( 1,2 )
    限制只查两个学校不应该从 SQL 实现吧,应该在传进来的值作处理时判断。分页可以用 row_number 函数。
    rocksolid
        13
    rocksolid  
       2019-07-17 08:56:31 +08:00
    @axwz88 你 where 查询条件放在子查询里不就好了
    brust
        14
    brust  
       2019-07-17 09:01:53 +08:00
    @yuikns #8 这个是干嘛的? 可以自动写 sql 吗
    ganbuliao
        15
    ganbuliao  
       2019-07-17 09:08:41 +08:00
    你可以试试 group_concat 这个函数
    vance
        16
    vance  
       2019-07-17 09:10:47 +08:00
    看不懂你要达到什么样的效果,限制 2 个学校不就直接 limit 学校就好了
    ganbuliao
        17
    ganbuliao  
       2019-07-17 09:11:42 +08:00
    select school.sid,school.school_name,group_concat(major,major_name) from school left join major on school.sid = major.sid group by school.sid limit 2
    应该可以
    kaneg
        18
    kaneg  
       2019-07-17 09:19:04 +08:00 via iPhone
    select school_name,major_name from school s,major m where s.sid=m.sid and s.id in (select sid from school limit 2 offset <分页偏移量>)

    分页条件加在学校的子查询即可
    qiayue
        19
    qiayue  
       2019-07-17 09:19:41 +08:00
    现在硬盘不值钱了,所以骚年,去冗余数据吧
    按照你要查询出来的结果去设计一张最终结果表格,然后给次更新数据时维护好这张结果表,然后每次查询时,直接从结果表查询,想要分页,啥都有
    Aruforce
        20
    Aruforce  
       2019-07-17 09:41:51 +08:00
    ```SQL
    select * from major m , (select * from school limit 0,2) s where m.sid = s.sid

    ```

    这样?
    Aruforce
        21
    Aruforce  
       2019-07-17 09:52:11 +08:00
    @yuikns 这个工具不错啊
    4tograph
        22
    4tograph  
       2019-07-17 10:16:07 +08:00
    select a.school_name,b.major_name
    from school a
    left join major b
    where a.sid in (select sid from school group by sid limit 2) 这样对吗?
    4tograph
        23
    4tograph  
       2019-07-17 10:17:44 +08:00
    @4tograph
    不好意思 ,on 条件忘记写了 。
    select a.school_name,b.major_name
    from school a
    left join major b on a.sid=b.sid
    where a.sid in (select sid from school group by sid limit 2)
    congeec
        24
    congeec  
       2019-07-17 11:39:23 +08:00 via iPhone
    # 正解

    类似问题都可以用 window function 解决
    sunny2580839896
        25
    sunny2580839896  
       2019-07-17 14:02:43 +08:00
    @reus #1 为啥我这样的报错的呀??? mysql 版本是 5.6 的
    leo108
        26
    leo108  
       2019-07-17 14:08:01 +08:00
    为什么不用两条 SQL 解决?曾经我也想一条 SQL 搞定所有问题,后来发现这个带来的所谓性能在可读性和可扩展性面前毫无价值。
    meetocean
        27
    meetocean  
       2019-07-17 14:32:16 +08:00
    假设自己终于看懂了楼主所说分页面的意思,等会把答案发上来。
    meetocean
        28
    meetocean  
       2019-07-17 15:01:45 +08:00
    问题已经解决!

    测试数据库:mysql 5.7

    ## 查询

    查询语句:

    select * from  (select * from school limit 0,2) s,major m where s.sid=m.sid;


    查询结果:

    school_name   major_name

    北京大学    专业 1

    北京大学    专业 2

    清华大学    专业 3

    清华大学    专业 4



    查询语句 2:

    select * from  (select * from school limit 2,4) s,major m where s.sid=m.sid;

    查询结果 2:

    school_name   major_name

    南京大学    专业 5

    南京大学    专业 6


    ## 程序实现:

    如果在程序中实现, limit 后面的数字改为变量名。

    count_per_page = 2;

    第一页:
    page = 1;
    offset = (page - 1) * count_per_page;

    limit offset, count_per_page

    第二页
    page = 2;
    offset = (page - 1) * count_per_page;

    limit offset, count_per_page
    meetocean
        29
    meetocean  
       2019-07-17 15:03:59 +08:00
    查询语句 2 代码 limit 2,4 改为 limit 2,2

    select * from (select * from school limit 2,2) s,major m where s.sid=m.sid;
    axwz88
        30
    axwz88  
    OP
       2019-07-17 18:03:13 +08:00
    谢谢大家的解答,每条回复我都认真的看了的,V 站的朋友们真的很用心。
    解释一下我的问题,简单的说就是学校和专业两张表关联查询,然后专业表和学校表都有其他的属性,上面的举例是为了让问题更容易讲清楚所以简化了,实际的查询可能是这样的:

    select s.school_name,m.major_name from school s,major m where s.sid=m.sid and m.专业类型="工科专业" and s.学校类型="工科院校"

    这样就会查出很多符合条件的学校和该学校下符合条件的专业,每个学校下面的专业都不相同,我的需求是做分页,每页限制 20 个学校,把满足条件的学校和该学校下满足条件的专业显示出来,分页是限制每页 20 个学校,但不限制专业。大概意思就是这样。
    axwz88
        31
    axwz88  
    OP
       2019-07-17 18:06:43 +08:00
    我晚上回去会把大家解答的实验一下,不管能不能达到最终效果都感谢大家的回复。
    axwz88
        32
    axwz88  
    OP
       2019-07-18 00:08:48 +08:00
    经过试验,下面的 SQL 语句可以完美解决问题,一开始是我没把问题表达清楚...

    SELECT s.school_name,m.major_name FROM major m inner join (select * from school where 学校类型="工科院校" LIMIT 0,20) s on s.sid = m.sid where m.专业类型="工科专业"

    对[学校分页] 每页 20 个学校, 查出来的该学校的专业行数不限
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   我们的愿景   ·   实用小工具   ·   5353 人在线   最高记录 6543   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 31ms · UTC 07:31 · PVG 15:31 · LAX 00:31 · JFK 03:31
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.