duanmei1930 2017-09-14 11:06
浏览 89
已采纳

使用join从一个MYSQL表中选择随机行

Afternoon folks,

I have had a good dig around and can't find the answer, so a good time to ask!

I'd like to select random rows from one table and then join into this random rows from another table where the ID that I have is the same. It would also be great to only select where I have an entry in the second table. I have tried all manner of sub-queries but am getting a bit lost. An inner join as read will do it but again with the randomness of it all!! Grrr...

SELECT
  tracks.track_id,
  cuttings.square_cutting,
  cuttings.cutting_2,
  cuttings.cutting_3,
  cuttings.blog_text 
FROM tbl_tracks tracks,
(SELECT 
    square_cutting,
    cutting_2,
    cutting_3,
    blog_text 
  FROM
    tbl_cuttings
  WHERE track_id = tracks.track_id <-- wont find it, obviously!!
  ORDER BY RAND() 
  LIMIT 1) cuttings
WHERE tracks.active = '1' ORDER BY RAND()

Thanks in advance for any help.

So: I'd like random tracks showing track id -> with random cuttings, of which there can be many but I just want 1.

It would then be ideal to only show a result if there is a cutting associated with that track.

Hope that helps.

I'm now trying to go a step further with this and order this by a RAND() seed as I'm now having to add in pagination. Only problem is that its not giving me back the same random list due to a given seed. Any Ideas?

SELECT
  tracks.track_id,
  cuttings.square_cutting,
  cuttings.cutting_2,
  cuttings.cutting_3,
  cuttings.blog_text
FROM tbl_tracks tracks
INNER JOIN
  (SELECT track_id,
    square_cutting,
    cutting_2,
    cutting_3,
    blog_text
    FROM
    tbl_cuttings
ORDER BY RAND()) cuttings ON tracks.track_id = cuttings.track_id
WHERE tracks.active = '1'
ORDER BY RAND(1)
LIMIT 0,4;
  • 写回答

1条回答 默认 最新

  • duanli0687 2017-09-14 11:12
    关注

    you could use an inner join

      SELECT
        tracks.track_id,
        cuttings.square_cutting,
        cuttings.cutting_2,
        cuttings.cutting_3,
        cuttings.blog_text 
      FROM tbl_tracks tracks
      INNER JOIN 
      (SELECT track_id,
          square_cutting,
          cutting_2,
          cutting_3,
          blog_text 
        FROM
          tbl_cuttings
         ORDER BY RAND() 
        LIMIT 1) cuttings on cuttings.track_id = tracks.track_id
      WHERE tracks.active = '1' 
      ORDER BY RAND()
    
    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论

报告相同问题?

悬赏问题

  • ¥20 keepalive配置业务服务双机单活的方法。业务服务一定是要双机单活的方式
  • ¥50 关于多次提交POST数据后,无法获取到POST数据参数的问题
  • ¥15 win10,这种情况怎么办
  • ¥15 如何在配置使用Prettier的VSCode中通过Better Align插件来对齐等式?(相关搜索:格式化)
  • ¥100 在连接内网VPN时,如何同时保持互联网连接
  • ¥15 MATLAB中使用parfor,矩阵Removal的有效索引在parfor循环中受限制
  • ¥20 Win 10 LTSC 1809版本如何无损提升到20H1版本
  • ¥50 win10 LTSC 虚拟键盘不弹出
  • ¥30 微信小程序请求失败,网页能正常带锁访问
  • ¥15 Matlab求解微分方程,如何用fish2d进行预优?