duanmei1930
2017-09-14 11:06使用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条回答
为你推荐
- 从Table1中选择一个随机行,其中表2中不存在id
- sql
- mysql
- php
- 3个回答
- 即使在服务器上使用ORDER BY子句而不是localhost,SQL查询也会给我随机顺序
- laravel
- mysql
- php
- 1个回答
- 使用join从一个MYSQL表中选择随机行
- select
- random
- mysql
- php
- 1个回答
- 什么是用作mysql select / join属性的适当长度的随机代码
- random
- mysql
- php
- 2个回答
- 集合中的随机数组
- json
- laravel
- arrays
- php
- shuffle
- 1个回答