使用多个表在mysql中加入多个联合 [英] multiple unions with joins in mysql using multiple tables

问题描述

我在 mysql 查询中创建了一个 UNION.这对我来说很好用.

I have created a UNION in mysql query. This is working fine for me.

此查询的主要目的是从多个表中获取通知数据.

The main purpose of this query to get notification's data from multiple tables.

这需要 0.0009 秒 时间来执行.用更少的数据.

This is taking 0.0009 sec time to execute. with less data.

任何 sql 专家都可以帮助我改进此 sql 查询或指导我进行此 sql 查询.

Can any sql expert help me to improve this sql query or guide me for this sql query.

我不擅长mysql

  SELECT
        HM_notification.product_id,
        HM_products.slug,
        HM_notification.action_type,
        HM_notification.url,
        HM_notification.read_date
        FROM `HM_notification` 
            JOIN HM_customers ON HM_customers.id = HM_notification.added_by 
            JOIN HM_products ON HM_products.id = HM_notification.product_id 
            JOIN HM_reviews ON HM_reviews.product_id = HM_notification.product_id 
            WHERE HM_products.user_id=34 AND HM_notification.added_by != 34 AND action_type = 'reviews'

    UNION        

  SELECT 
        HM_notification.product_id,
        HM_products.slug,
        HM_notification.action_type,
        HM_notification.url,
        HM_notification.read_date
        FROM `HM_notification` 
            JOIN HM_customers ON HM_customers.id = HM_notification.added_by 
            JOIN HM_bid ON HM_bid.product_id = HM_notification.product_id 
            JOIN HM_products ON HM_products.id = HM_notification.product_id 
            WHERE HM_products.user_id=34 AND HM_notification.added_by != 34  AND action_type = 'newbid' OR action_type = 'editbid'

    UNION        

    SELECT 
        HM_notification.product_id,
        HM_products.slug,
        HM_notification.action_type,
        HM_notification.url,
        HM_notification.read_date
        FROM `HM_notification` 
            JOIN HM_customers ON HM_customers.id = HM_notification.added_by 
            JOIN HM_products ON HM_products.id = HM_notification.product_id 
            JOIN HM_order_items ON HM_order_items.product_id = HM_notification.product_id 
            WHERE HM_products.user_id=34 AND HM_notification.added_by != 34 AND (action_type = 'order_pending' OR action_type = 'processing' OR action_type = 'onhold' OR action_type = 'unhold' OR action_type = 'receive' OR action_type = 'addshipment' OR action_type = 'editshipment' OR action_type = 'newcomment' OR action_type = 'addshipmentcomment')

    UNION        

  SELECT 
        HM_notification.product_id,
        HM_products.slug,
        HM_notification.action_type,
        HM_notification.url,
        HM_notification.read_date
        FROM `HM_notification` 
            JOIN HM_customers ON HM_customers.id = HM_notification.added_by 
            JOIN HM_products ON HM_products.id = HM_notification.product_id 
            JOIN HM_conversation ON HM_conversation.pid = HM_notification.product_id 
            WHERE HM_products.user_id=34 AND HM_notification.added_by != 34 AND (HM_conversation.user_one != 34 || HM_conversation.user_two != 34) AND action_type = 'conversation'

这是一个简单的 sql 查询，用于获取具有产品 ID 的特定 url.

This is a simple sql query to fetch specific url with product id.

推荐答案

试试这个:

SELECT
            HM_notification.product_id,
            HM_products.slug,
            HM_notification.action_type,
            HM_notification.url,
            HM_notification.read_date
            FROM `HM_notification` 
                JOIN HM_customers ON HM_customers.id = HM_notification.added_by 
                JOIN HM_products ON HM_products.id = HM_notification.product_id 
                JOIN HM_reviews ON HM_reviews.product_id = HM_notification.product_id 
                WHERE HM_products.user_id=34 AND HM_notification.added_by != 34 AND action_type = 'reviews'

        UNION        

      SELECT 
            HM_notification.product_id,
            HM_products.slug,
            HM_notification.action_type,
            HM_notification.url,
            HM_notification.read_date
            FROM `HM_notification` 
                JOIN HM_customers ON HM_customers.id = HM_notification.added_by 
                JOIN HM_bid ON HM_bid.product_id = HM_notification.product_id 
                JOIN HM_products ON HM_products.id = HM_notification.product_id 
                WHERE HM_products.user_id=34 AND HM_notification.added_by != 34  AND (action_type = 'newbid' OR action_type = 'editbid')

        UNION        

        SELECT 
            HM_notification.product_id,
            HM_products.slug,
            HM_notification.action_type,
            HM_notification.url,
            HM_notification.read_date
            FROM `HM_notification` 
                JOIN HM_customers ON HM_customers.id = HM_notification.added_by 
                JOIN HM_products ON HM_products.id = HM_notification.product_id 
                JOIN HM_order_items ON HM_order_items.product_id = HM_notification.product_id 
                WHERE HM_products.user_id=34 AND HM_notification.added_by != 34 AND (action_type = 'order_pending' OR action_type = 'processing' OR action_type = 'onhold' OR action_type = 'unhold' OR action_type = 'receive' OR action_type = 'addshipment' OR action_type = 'editshipment' OR action_type = 'newcomment' OR action_type = 'addshipmentcomment')

        UNION        

      SELECT 
            HM_notification.product_id,
            HM_products.slug,
            HM_notification.action_type,
            HM_notification.url,
            HM_notification.read_date
            FROM `HM_notification` 
                JOIN HM_customers ON HM_customers.id = HM_notification.added_by 
                JOIN HM_products ON HM_products.id = HM_notification.product_id 
                JOIN HM_conversation ON HM_conversation.pid = HM_notification.product_id 
                WHERE HM_products.user_id=34 AND HM_notification.added_by != 34 AND (HM_conversation.user_one != 34 OR HM_conversation.user_two != 34) AND action_type = 'conversation'

这篇关于使用多个表在mysql中加入多个联合的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！