我在同一表上做多个工会
但是,我需要订购rand((
的第二个表的记录请记住,我不想拥有重复的记录,因为IAM使用rand((
示例:
新闻表有以下数据:( test1,test2,test3(
广告表具有以下数据:(ADS1,ADS2,ADS3(
结果应该是这样:
新闻由ID
排序广告由rand((排序:这意味着ADS2可能出现在列表的顶部,也许ADS1出现在列表的顶部等等。
这是我的SQL语句:
(select news.title
from news
order by news.id desc limit 6) union
(select
advertisements.title
from advertisements
order rand() limit 1,1)
union
(select
news.title,
from news
order by news.id desc limit 6,6)
union
(select
advertisements.title
from advertisements
order by rand() limit 2,1)
近乎我所能说的,您似乎想要6篇新闻文章,然后再进行广告,然后再次重复。这不是您的查询所做的,但我猜这是使用union
的意图。
我建议枚举值,然后在外面进行排序:
select title
from ((select n.title, @rn := @rn + 1, 'n' as which, id
from news n cross join (select @rn := 0) params
order by n.id desc
limit 12
)
union all
(select a.title, (@rna := @rna + 1) as rn, 'a', NULL
from advertisements a cross join (select @rna := 0) params
order rand()
limit 2
)
) na
order by (case when which = 'n' and rn <= 6 then 1
when which = 'a' and rn = 1 then 2
when which = 'n' and rn <= 12 then 3
when which = 'a' and rn = 1 then 4
end),
id desc;