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条回答

      报告相同问题?

      相关推荐 更多相似问题

      悬赏问题

      • ¥30 关于#网络安全#的问题:非对称加密验证
      • ¥20 关于线性代数里施密特正交化和QR分解的疑问
      • ¥15 matlab超类包含解析错误
      • ¥15 python拖拽文件问题
      • ¥15 执行import paddle代码出现错误如何解决?
      • ¥15 hisat2align exited with value 137
      • ¥15 寻找大学生合作开发软件(Delphi)
      • ¥30 AndroidBench&eMMC内存测试速度&Android
      • ¥15 W10 文件共享失败 怎么解决
      • ¥20 b站私信完整导出的方法