将多个SQL查询合并为一个 [英] Combine several SQL querys to one

问题描述

我知道这可能真的很简单，但是我试图找到一些类似的例子并失败了.

I know this is probably really simple but I have tried to find some similar examples and failed.

问题是我想列出数据库中画廊的8张随机图像，并按添加日期排序.而且我已经做到了，但是只有使用几个迭代查询，这才变得很慢.因此，如果有人乐于教我如何更快地将它们组合在一起，那么我猜UNION是应该走的路吗?这是我的工作(但是代码很繁琐)

The problem is that I would like to list 8 random images from a gallery in a database, sorted by added date. And I have managed to do this, but only with several iterating query's that becomes really slow. So if someone would be so kind to teach me about combining them for faster speed, I guess UNION is the way to go? Here is my working (but slooow code)

<?php       
        $latestPictures = mysql_query("SELECT pictureID, addedDate FROM picture ORDER BY addedDate DESC LIMIT 8"); 

        $latestConcerts = mysql_query("SELECT concertID, addedDate FROM concert WHERE pictureID is null ORDER BY addedDate DESC LIMIT 8"); 


        // Add concerts and pictures to array
        while($curFestival = mysql_fetch_object($latestPictures))
        {
            $array[$curFestival->addedDate] = "p" . $curFestival->pictureID;
        }

        while($curConcert = mysql_fetch_object($latestConcerts))
        {
            $array[$curConcert->addedDate] = "c" . $curConcert->concertID;
        }

        // Order array by key
        krsort($array);

        $latestArray = array_slice($array, 0, 8);

        foreach($latestArray as $key => $value) {

            $type = substr($value, 0, 1);
            $ID = substr($value, 1);

            // If type == picture
            if($type == 'p')
            {           
                $picturesPicturesID = mysql_query("SELECT concertID, name FROM photo WHERE concertID IN(SELECT concertID FROM concert WHERE pictureID = $ID) ORDER BY photoID");

                // Get random picture
                $curRandomPicture = rand(0, (mysql_num_rows($picturesPicturesID) - 1));
                $curPictureConcertID = mysql_result($picturesPicturesID, $curRandomPicture, "concertID");
                $curPictureName = mysql_result($picturesPicturesID, $curRandomPicture, "name");             
                $curPicture = mysql_fetch_object(mysql_query("SELECT c.URL, p.name FROM concert c, picture p WHERE p.pictureID = c.pictureID AND c.concertID = $curPictureConcertID")); 

             echo "Some image"; 
             }
            // If type == concert
            if($type == 'c')
            {
                $concertPicturesID = mysql_query("SELECT concertID, name FROM photo WHERE concertID = $ID ORDER BY photoID");

                // Get random picture
                $curRandomPicture = rand(0, (mysql_num_rows($concertPicturesID) - 1));  
                $curPictureConcertID = mysql_result($concertPicturesID, $curRandomPicture, "concertID");
                $curPictureName = mysql_result($concertPicturesID, $curRandomPicture, "name");              
                $curPicture = mysql_fetch_object(mysql_query("SELECT URL, name FROM concert WHERE concertID = $curPictureConcertID")); 

            echo "Some image";
            }
        }
?>

我意识到我忘了包含表格，它们在这里:

I realized that I forgot to include the tables, here they are:

TABLE OF photo:
PKEY: photoID
FKEY: concertID
name

TABLE OF concert:
PKEY: concertID
FKEY: pictureID
name
URL
addedDate

TABLE OF picture
PKEY: pictureID
name
date

因此，每个帖子都是TABLE照片和演唱会的一部分，但是图片中只有一部分是女巫，有时仅用于将不同的专辑分组在一起.当他们分组在一起时，我想从该分组ID(图片)中随机发一个名字，如果他们是他们自己，则从那里(音乐会)自己发一个随机名称.

So every post is part of TABLE photo AND concert, but only some is part of picture witch is only used sometimes to group differens albums together. When they are grouped together I whant a random name post from that grouping ID (picture) and if they are by them self a random name post from there (concert).

推荐答案

Ackckkk！ (用猫的比尔臭名昭著的话来说.)

Accckkk! (In the infamous words of Bill the Cat.)

很难从数据库中找出代码真正想要的结果集.

It's hard to figure out what result set your code really wants from the database.

此查询不是最有效的查询，但它将从photos表返回8个随机行，这些行按addDate排序:

This query isn't the most efficient, but it will return 8 random rows from the photos table, with those rows ordered by addedDate:

SELECT r.*, c.*
  FROM (SELECT p.*
          FROM photo p
         WHERE p.concertid IS NOT NULL
         ORDER BY RAND()
         LIMIT 0,8
       ) r
  JOIN concert c
    ON c.concertid = p.concertid
 ORDER BY r.addedDate ASC

如果您有一个很大的photo表，它将变得慢，因为该RAND()函数必须为表中的每一行调用，而MYSQL必须产生一个临时结果集(表的副本)，然后在派生列上对其进行排序.

If you have a really large photo table, this is going to be slow, because that RAND() function has to get called for every single row in the table, and MYSQL has to produce a temporary result set (a copy of the table) and then sort it on that derived column.

(注意:我在这里假设它是要从中返回随机"行的photo表，并且我假设concertid是concert表上的主键，并且photo表中的一个外键，很明显，您有三个表... concert，picture和photo，但是不清楚哪些列是主键，哪些列是外键，所以很可能我错了.)

(NOTE: I'm assuming here that it's the photo table that you want to return "random" rows from, and I'm assuming that concertid is the primary key on the concert table, and a foreign key from the photo table. It's apparent that you have three tables... concert, picture and photo, but it's not clear which columns are the primary keys and which columns are the foreign keys, so it's likely I have it wrong.)

(注意:将p.*和c.*替换为您实际要返回的表达式列表.)

(NOTE: replace the p.* and c.* with a list of expressions you actually want to return.)

有更有效的方法来返回单个随机行.在您的情况下，您只需要八行，并且大概不想返回重复的行.

There are more efficient approaches to returning a single random row. In your case, you want exactly eight rows, and you presumably don't want to return a duplicate.

这篇关于将多个SQL查询合并为一个的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！