我有以下兩個 MySQL 查詢，我想在一個過程中執行它

以下查詢返回 10 行配方 ID，我想將第一個查詢的 10 行輸出傳遞給第二個查詢，並在一個過程中顯示兩個查詢的輸出。

查詢一：

SELECT 
   p.ID as recipe_ID,
   p.post_title as recipe_title,
   p.post_date as modified_date,
   p.post_name as recipe_url,
   CASE WHEN s.total_count THEN s.total_count ELSE 0 END as total_like_count,
   CASE WHEN c.bookmark=1 THEN 1 ELSE 0 END as bookmark_status,
   CASE WHEN c.like_status=1 THEN 1 ELSE 0 END as like_status,
   CASE WHEN pm.meta_value=1 THEN 1 ELSE 0 END as unlock_status

FROM
   posts p
LEFT JOIN social_val s on(p.ID=s.post_id)
LEFT JOIN social_status_connection as c on(p.ID=c.post_id AND c.user_id=1)
LEFT JOIN postmeta pm on(p.ID=pm.post_id AND pm.meta_value=1 AND pm.meta_key='recipe_lock'),
term_relationships as t, term_taxonomy tt
WHERE
 p.post_type = 'recipe'
   AND p.post_status = 'publish'   
   and p.ID = t.object_id
   and tt.term_id = 1279
   and t.term_taxonomy_id=tt.term_taxonomy_id     

ORDER BY modified_date DESC
LIMIT 0 , 10

查詢 2 下面的查詢是自連接，它生成上述特定配方 ID 的圖像。

SELECT 
   p.guid as recipeImageURL
FROM
   posts p,
   posts m
where
   p.post_type = 'attachment'
       AND p.post_parent = m.ID
       AND m.ID = ID ORDER BY p.post_modified DESC
LIMIT 1;

如果您需要將一個查詢的輸出循環到另一個查詢，您可以使用cursors。

例如，在這個儲存的常式中查找我們循環過的所有表數據庫：

DECLARE table_cur CURSOR FOR SELECT ...
OPEN table_cur;
table_loop:LOOP
...code...
END LOOP;
CLOSE table_cur;

因此，在您的情況下，為第一個查詢聲明游標並遍歷每個 recipeid。

我在單個過程中嘗試過使用 CURSOR：

**第一個程序：**顯示配方詳細資訊

BEGIN SELECT p.ID as recipe_ID, p.post_title as recipe_title, p.post_date as modified_date, p.post_name as recipe_url, CASE WHEN s.total_count THEN s.total_count ELSE 0 END as total_like_count, CASE WHEN c.bookmark=1 THEN 1 ELSE 0 END 作為 bookmark_status，CASE WHEN c.like_status=1 THEN 1 ELSE 0 END 作為 like_status，CASE WHEN pm.meta_value=1 THEN 1 ELSE 0 END 作為 unlock_status

             FROM
                 posts p
             LEFT JOIN social_val s on(p.ID=s.post_id)
             LEFT JOIN social_status_connection as c on(p.ID=c.post_id AND c.user_id=userid)
             LEFT JOIN postmeta pm on(p.ID=pm.post_id AND pm.meta_value=1 AND pm.meta_key='recipe_lock'),
             term_relationships as t, term_taxonomy tt
             WHERE
               p.post_type = 'recipe'
                 AND p.post_status = 'publish'   
                 and p.ID = t.object_id
                 and tt.term_id =termid
                 and t.term_taxonomy_id=tt.term_taxonomy_id        
              ORDER BY modified_date DESC
               LIMIT offset , 5;

CALL recipe_list(userid,termid,offset); //呼叫圖像END的第二個過程

第二個程序：（對於配方圖像） BEGIN DECLARE post_id int DEFAULT 0;

– 為 recipe_image_cursor 聲明游標 DEClARE recipe_image_cursor CURSOR FOR SELECT p.ID as recipe_ID FROM posts p LEFT JOIN social_share_counts s on(p.ID=s.post_id) LEFT JOIN social_status_connection as c on(p.ID=c.post_id AND c. user_id=1) LEFT JOIN postmeta pm on(p.ID=pm.post_id AND pm.meta_value=1 AND pm.meta_key=‘recipe_lock’), term_relationships as t, term_taxonomy tt WHERE p.post_type = ‘recipe’ AND p. post_status = ‘publish’

and p.ID = t.object_id and tt.term_id = 1279 and t.term_taxonomy_id=tt.term_taxonomy_id LIMIT 0 , 10;

打開recipe_image_cursor；

獲取圖像：循環

FETCH recipe_image_cursor INTO post_id;

–顯示結果 SELECT p.guid as recipeImageURL FROM posts p, posts m where p.post_type = ‘attachment’ AND p.post_parent = m.ID AND m.ID = post_id ORDER BY p.post_modified DESC LIMIT 1; 結束循環get_image；關閉 recipe_image_cursor; 結尾

如何以列格式顯示游標的結果..可能嗎？或者哪個是更好的方法？..我是新的程序和游標

引用自：https://dba.stackexchange.com/questions/123043