MySQL联合限制问题 [英] mysql union limit problem

问题描述

我希望分页脚本基本上可以正常工作，但是情况有点复杂.我需要从两个SQL查询的联合中选择数据.请参阅下面的查询.我有一个桌子和一个桌子.我想要的是按受欢迎程度显示特定类别的所有书籍.我通过加入表格书和书访来获取至少一次访问的所有书的数据.然后将其与所有书籍合并，而无需访问.一切正常，但是当我尝试进行分页时，我需要将其限制为(0,10)(10,10)(20,10)(30,10)，对吗?如果该类别中有9本书可供访问，而该类别却没有任何访问，则有3761本书(总计3770本书)，它应该列出377页，每页列出10本书.但它不会显示某些页面的任何数据，因为它试图显示限制为3760,10的书籍，因此没有第二次查询的记录在并集中.也许我无法在这里澄清情况，但是如果您对此情况有所考虑，那么您会明白我的意思.

I want a paging script working properly basically but the situation is a bit complex. I need to pick data from union of two sql queries. See the query below. I have a table book and a table bookvisit. What I want is here to show all books for a particular category in their popularity order. I am getting data for all books with atleast one visit by joining table book and bookvisit. and then union it with all books with no visit. Everything works fine but when I try to do paging, I need to limit it like (0,10) (10,10) (20,10) (30,10), correct? If I have 9 books in bookvisit for that category and 3761 books without any visit for that category (total of 3770 books), it should list 377 pages , 10 books on each page. but it does not show any data for some pages because it tries to show books with limit 3760,10 and hence no records for second query in union. May be I am unable to clear the situation here but if you think a bit about the situation, you will get my point.

SELECT * FROM ( 
SELECT * FROM (
 SELECT viewcount, b.isbn, booktitle, stock_status, price, description FROM book AS b 
 INNER JOIN bookvisit AS bv ON b.isbn = bv.isbn WHERE b.price <> 0 AND hcategoryid = '25' 
 ORDER BY viewcount DESC 
 LIMIT 10, 10
 ) AS t1 
 UNION 
 SELECT * FROM 
 ( 
 SELECT   viewcount, b.isbn, booktitle, stock_status, price, description FROM book AS b 
 LEFT JOIN bookvisit AS bv ON b.isbn = bv.isbn WHERE b.price <> 0 AND hcategoryid = '25' 
 AND viewcount IS NULL 
 ORDER BY viewcount DESC 
 LIMIT 10, 10
  ) AS t2 
  ) 
  AS qry
   ORDER BY viewcount DESC 
LIMIT 10

推荐答案

请勿对单独的查询使用限制.仅在最后使用限制.您想要从2个查询中获取孔结果集，然后仅显示10个结果，无论这是LIMIT 0、10还是LIMIT 3760,10

Do not use limit for the separate queries. Use limit only at the end. You want to get the hole result set from the 2 queries and then show only the 10 results that you need no matter if this is LIMIT 0, 10 or LIMIT 3760,10

SELECT * FROM (  
 SELECT * FROM (  
  SELECT viewcount, b.isbn, booktitle, stock_status, price, description FROM book AS b 
  INNER JOIN bookvisit AS bv ON b.isbn = bv.isbn WHERE b.price <> 0 AND hcategoryid = '25' 
  ORDER BY viewcount DESC   
 ) AS t1   
 UNION   
 SELECT * FROM  
 (   
  SELECT   viewcount, b.isbn, booktitle, stock_status, price, description FROM book AS b 
  LEFT JOIN bookvisit AS bv ON b.isbn = bv.isbn WHERE b.price <> 0 AND hcategoryid = '25' 
  AND viewcount IS NULL   
  ORDER BY viewcount DESC   
 ) AS t2   
)   
 AS qry  
ORDER BY viewcount DESC   
LIMIT 10, 10

这篇关于MySQL联合限制问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！