最初に、コメントで提案された解決策について説明します。
1つのタスク。
複雑なことは何もありませんでしたが、正しい決定はほとんどありませんでした。 最も一般的な間違いは、カテゴリが公開されていない可能性がある場合にオプションを検討する人がほとんどいないということです。 コードの間違ったグループ化または単純な大まかな構文エラーに巻き込まれました。 EXPLAINを見た後、自殺念慮が生じた決定がありました。 :-)
しかし、いくつかの「軽微な」罪を除いて、間違いを修正した後、多くの人が正しく働きました。
2タスク。
実際、それは思ったよりも簡単に判明しました-しかし、これは私のせいです。条件が十分に正確ではなく、誰も明確にするために推測しなかったからです。 その結果、それはキャッチになりましたが、誰も注意を払いませんでした。 ここに問題があります：ordiフィールドは並べ替えを担当しますが、それが一意であることは明確にされていません（ただし、逆も当てはまりませんでした。 ソートはこのフィールドで実際に行われていますが、メンバーシップごとに独自の値があります。つまり、1枚の写真アルバムに1,2,3が入り、もう1枚の写真アルバムに4,5,6が入らず、1,2,3にも入る場合です。 このため、決定の半分は正しいものであり、同時にではありません。
エラーから-上記の発生フラグのチェックはほとんどありません。 これを除けば、ほぼ全員が働いた。
与えられた条件を考慮すると、これはかなり複雑なタスクです。つまり、要求は非常に面倒です。 さらに明確な理由。
3タスク。
予想されるように、決定はほとんどありませんでした。 提案されたファロッドは、 1つだけが相対的に機能するはずです 。 タスクは本当に非常に困難であり、ここでは2番目のような面倒なクエリではなく、ソリューション自体に直接あるためです。
今決定。
まず、リストされたすべての条件を正しく考慮してすべてを選択するテンプレートクエリを作成します。 テーブル内の同じデータを表示する必要がある場合、ただし外部条件に応じてこれを常に行います。 今では問題ではありません。これを段階的に解決する方法を説明したいと思います。
JOIN ... ON（...）を使用して結合条件を設定することにより、1つのクエリで3つのテーブルすべてをバインドします。 次のようなものが得られます。
SELECT
*
FROM photo_category as c
JOIN photo_gallery as g ON (g.c_id=c.id AND c.is_published AND g.is_published)
JOIN photo_image as i ON (i.g_id = g.id AND i.is_published)
GROUP BY g.id
HAVING COUNT (i.id) > 0
ORDER BY c.ordi, g.ordi, i.ordi;
接続条件をON（...）で記述し、関連するものがすぐに明確になり、WHEREが乱雑にならないようにします（実際、それらが記述されている1つのいまいましいもの）。 空のギャラリーとカテゴリをフィルタリングするには、グループ化が必要です（それらの場合、HAVING COUNT（i.id）条件はNULLになります）。 フィールドによるソートが存在します。
このリクエストではすべての初期条件が満たされていますが、これはまったく解決策ではありません。 空白にしました。 さあ行きましょう。
最初のタスク。
すでにすべてを考慮に入れている最初の要求を受け入れます。 is_main_fotoフラグを考慮して、カテゴリIDともう1つのJOINテーブルによる選択条件を追加します。 しかし、これは不可能な可能性があるため、左から参加します。
SELECT
*
FROM photo_category as c
JOIN photo_gallery as g ON (g.c_id=c.id AND c.is_published AND g.is_published)
JOIN photo_image as i ON (i.g_id = g.id AND i.is_published)
LEFT JOIN photo_image as i2 ON (i2.g_id = g.id AND i2.is_main_foto)
WHERE c.id = 1
GROUP BY g.id
HAVING COUNT (i.id) > 0
ORDER BY c.ordi, g.ordi, i.ordi;
現在、 im2.idの値がNULLの場合 、絶対に任意の写真がi.idに含まれます。 ちなみに、この場合の通常のDBMSは、 iでどの値を取るべきかが明確ではないため、誓う必要があります。 完全な幸福のために、IFを使用して答えを書きます。
SELECT
c.id as cid,
c.title as ctitle,
g.id as gid,
g.title as gtitle,
IF (i2.id IS NULL , i.id, i2.id) as image_id
FROM photo_category as c
JOIN photo_gallery as g ON (g.c_id=c.id AND c.is_published AND g.is_published)
JOIN photo_image as i ON (i.g_id = g.id AND i.is_published)
LEFT JOIN photo_image as i2 ON (i2.g_id = g.id AND i2.is_main_foto)
WHERE c.id = 1
GROUP BY g.id
HAVING COUNT (i.id) > 0
ORDER BY c.ordi, g.ordi, i.ordi;
IFの仕組みがわからない人のために、 IF（<条件>、<真の場合は式の値>、偽の場合は式の値>）について説明します。 トレナー演算子は実用的です。
すべて、問題は解決しました。
2番目のタスク。
解決策はそれほど複雑ではありません：現在の要素のORDIを取得し、次にordiが結果のものよりも小さいという条件でサンプリング条件を満たすものに対してMAXを作成し、それによって前の画像を取得し、同様に次の画像を取得する必要があります。 詳細に入らないと、すべてが非常に簡単になりますが、詳細に入れば、非常に複雑なクエリを取得します。
まず、現在の要素のordi （ソートフィールド）を取得します。これは非常に簡単です。
SELECT ordi FROM photo_image WHERE id = 1
さらに、現在のギャラリーのみを入力する場合に検索する必要があるものを考慮します。つまり、ギャラリーのIDを決定する必要があります（条件により、外部から取得できますが、完全を期すために自分で計算します）。
SELECT g_id FROM photo_image WHERE id = 1
それでは、以前のORDIのリクエストを作成しましょう（公開されたORDIのみを探しています）。
SELECT MAX (ordi) from photo_image
WHERE is_published AND g_id = ( SELECT g_id FROM photo_image WHERE id = 1)
AND ordi < ( SELECT ordi FROM photo_image WHERE id = 1)
以下についても同様です。
SELECT MIN (ordi) from photo_image
WHERE is_published AND g_id = ( SELECT g_id FROM photo_image WHERE id = 1)
AND ordi > ( SELECT ordi FROM photo_image WHERE id = 1)
次と前のORDIがわかったので、対応する要素のIDを取得する必要があります。ただし、現在のギャラリーにのみいる必要があります。
SELECT id, title
FROM photo_image
WHERE
g_id = ( SELECT g_id FROM photo_image WHERE id = 1)
AND
(
ordi =
(
SELECT MAX (ordi) from photo_image
WHERE is_published AND g_id = ( SELECT g_id FROM photo_image WHERE id = 1)
AND ordi < ( SELECT ordi FROM photo_image WHERE id = 1)
)
OR
ordi =
(
SELECT MIN (ordi) from photo_image
WHERE is_published AND g_id = ( SELECT g_id FROM photo_image WHERE id = 1)
AND ordi > ( SELECT ordi FROM photo_image WHERE id = 1)
)
);
すべてのように見えますが、条件チェックはまだありません。 また、サンプルに結果が1つある場合に、次のものを前のものと区別する方法も明確ではありません。 これを解決するには、写真（計算対象）に写真を追加し、「テンプレート」リクエストを使用します。その結果、次のようになります。
SELECT ordi, id, title
FROM photo_image
WHERE
EXISTS
(
SELECT
i.id
FROM photo_category as c
JOIN photo_gallery as g ON (g.c_id=c.id AND c.is_published AND g.is_published)
JOIN photo_image as i ON (i.g_id = g.id AND i.is_published)
WHERE i.id = 1
)
AND
g_id = ( SELECT g_id FROM photo_image WHERE id = 1)
AND
(
ordi =
(
SELECT MAX (ordi) from photo_image
WHERE is_published AND g_id = ( SELECT g_id FROM photo_image WHERE id = 1)
AND ordi < ( SELECT ordi FROM photo_image WHERE id = 1)
)
OR
ordi =
(
SELECT MIN (ordi) from photo_image
WHERE is_published AND g_id = ( SELECT g_id FROM photo_image WHERE id = 1)
AND ordi > ( SELECT ordi FROM photo_image WHERE id = 1)
)
OR
id = 1
);
状態
EXISTS
(
SELECT
i.id
FROM photo_category as c
JOIN photo_gallery as g ON (g.c_id=c.id AND c.is_published AND g.is_published)
JOIN photo_image as i ON (i.g_id = g.id AND i.is_published)
WHERE i.id = 1
)
現在の写真、ギャラリー、またはカテゴリが公開されていない場合、選択からすべてを削除します。 HAVING式とグループ化には意味がありません。ギャラリーに画像がなければ、取り出しは空のままになるためです。 結果は3、2、または1行になります。 したがって、目的の写真は特定のIDを持つ写真になり、前後の写真はordiに基づいて決定されます。この写真は、このIDの写真よりも少ないか多いです。
多くのサブクエリがあり、ゆっくりと動作するようです。 実際はありません-MySQLは繰り返しの成長をキャッシュします（自信のためにSQL_CACHEを作成できます）。したがって、各サブクエリは1回だけ実行されます。 インデックスが存在する場合（そうでない場合、インデックスが存在しない場合）、十分に迅速に機能します。
問題は解決しました。
注：この問題を1つの要求で解決する方法がわからないと言った。 これは、実際には各写真に対して大量のパラメーターを取得する必要があり、1回のリクエストでこれを行うと、単に巨大になり、本当に遅くなるためです。 ここで-これは2つのリクエスト（次と前）ですが、共通のフィルターを使用しています。
3番目のタスク。
待ち伏せは、特定のグループに対して実際に制限を行う必要があるということです。 リクエスト全体の結果のみを制限するため、通常の制限ではこれを行いません。 LIMITを使用して各カテゴリのUNIONクエリを結合します-もちろん、ソリューションはもちろんですが、カテゴリが多い場合はどうでしょうか？ 転がりません。 次に頭に浮かぶのは、WHEREによるフィルタリングです。 しかし、その後、フィルタリングが何であるかの兆候が必要です。つまり、WHERE条件では、Nよりも大きい行を区別する必要があります。 シリアル番号を各カテゴリのアルバム数に設定することによってのみ。 作り方 ローカル変数のみを使用します。 この問題などが他の方法で解決できないことを完全に確信しているわけではありません。
元のリクエストを受け取って、次のようにわずかに変更した場合：
SELECT
@a:=@a+1, *
FROM photo_category as c
JOIN photo_gallery as g ON (g.c_id=c.id AND c.is_published AND g.is_published)
JOIN photo_image as i ON (i.g_id = g.id AND i.is_published)
GROUP BY g.id
HAVING COUNT (i.id) > 0
ORDER BY c.ordi, g.ordi, i.ordi;
次に、番号付きの行を取得します。 行ごとに、@ aの値は1ずつ増加します。 素晴らしい。 ただし、特定のグループすべてをカウントする必要があるため、c_idが変更された場合は、@ aをリセットする必要があります。 このために別の変数とリセット条件を導入します（同時に、アスタリスクの代わりに必要なフィールドを追加します）：
SELECT
@a:=@a+1,
IF (@cid=cid, @a:=@a+1, (@cid:=cid) AND (@a:=1)),
c.id as cid, c.title as ctitle,
g.id as gid, g.title as gtitle,
FROM photo_category as c
JOIN photo_gallery as g ON (g.c_id=c.id AND c.is_published AND g.is_published)
JOIN photo_image as i ON (i.g_id = g.id AND i.is_published)
GROUP BY g.id
HAVING COUNT (i.id) > 0
ORDER BY c.ordi, g.ordi, i.ordi;
これはどういう意味ですか。 変数cidの値がカテゴリIDの現在の値と等しい場合、変数@ aは1増加し、その後、結果は@aになります。 それ以外の場合、 cid値は現在のカテゴリIDに等しくなり、@ aは1に等しくなり、式の結果は= 1になります（つまり、実際にはちょうど良い一致ですが、同じ@aになります）。 これで、WHERE @a <Nの条件を単純に除外できるように見えますが、これは機能しません。
そして、ここに理由があります。 このスキームは、行がすでにソートされている場合にのみ正しく実行されます。 ただし、並べ替えは、要求が処理された後に行われ 、処理中には行われません。 最初に並べ替えてから、番号を付ける必要があります。
幸いなことに、すでに述べたように、式IF（ cid = cid、@a：= @ a + 1、（ cid ：= cid）AND（@a：= 1））は次と等しいという事実に注目しますどちらの場合も@a。 次に、WHEREで直接記述して使用できます。 条件によっては画像を表示する必要がありましたが、最初にリクエストを実行してからフィルターでカウント操作を実行するだけなので、これは問題を引き起こしません。 次に、取得するものを示します。
SELECT q.* FROM
(
SELECT --
c.id as cid, c.title as ctitle,
g.id as gid, g.title as gtitle,
IF (i2.title IS NULL , i.title, i2.title)
FROM photo_category as c
LEFT JOIN photo_gallery as g ON (g.c_id=c.id AND c.is_published AND g.is_published)
LEFT JOIN photo_image as i ON (i.g_id = g.id AND i.is_published)
LEFT JOIN photo_image as i2 ON (i2.g_id = g.id AND i2.is_main_foto)
GROUP BY g.id
HAVING COUNT (i.id) > 0
ORDER BY c.ordi, g.ordi DESC
) as q
WHERE IF (@cid=q.cid, @a:=@a+1, (@cid:=q.cid) AND (@a:=1)) <= N;
これはすでにproavilnoで機能します。 問題は解決しました。 ちなみに、ローカル変数は巧みに使用することで非常に強力なものであることに注意してください。 それでも、このリクエストは、この種の多くの典型的なタスクのユニバーサルソリューションと呼ぶことができます：カテゴリごとに最後に追加された商品またはベストまたは購入した商品5個、各ユーザーに最高の記事5個、各ジャンルの最も売れた本10個など。 なぜなら、サブクエリがどうなるかはまったく同じだからです。
まあ、それだけです。 ご清聴ありがとうございました。 あなたが興味を持っていたことを願っています。
PS：問題ステートメントのギャラリーのタイトルについて-もちろん、それはタイプミスでした:-)これを報告した人々に感謝しますが、私は時間通りに修正できませんでした。
PS2：
* This source code was highlighted with Source Code Highlighter .