mysql - 如果可能的话需要将两个 mySQL 查询连接在一起

Question

是否可以将这两个查询合并为一个？

第一个查询:

SELECT products_options_values, options_values_price, orders_id
FROM orders_products_attributes oa
WHERE (products_options_id = 5 OR products_options_id = 1 OR products_options_id = 2)
AND oa.orders_id
IN (
SELECT DISTINCT o.orders_id
FROM orders o
WHERE o.ExportedToTradebox =  0
AND o.orders_id >  22000)

第二个查询:

SELECT * FROM orders_products
WHERE orders_id 
IN(
SELECT DISTINCT orders_id 
FROM orders 
WHERE ExportedToTradebox='0' 
and orders_id >'22000')

我已经尝试了几次，但只是收到 phpMyAdmin 报告的 SQL 错误抱歉，如果是简单的事情，我的 SQL 知识非常零散。

组合起来，以便在运行时将所有必需的信息放入一个数组中。我尝试从它生成的两个单独的数组中使用它，但程序中的 php 代码设计得很差，有多个 while 循环和 else-if 语句。尝试引入另一个 while-MoveNext 循环只会使一切崩溃。我想如果所有数据都可以收集到原始数组中，那么就足够简单来回显新字段。

相关数据库表结构如下:

订单表:

CREATE TABLE IF NOT EXISTS `orders` (
`orders_id` int(11) NOT NULL AUTO_INCREMENT,
`customers_id` int(11) NOT NULL DEFAULT '0',
`customers_name` varchar(64) NOT NULL DEFAULT '',
`customers_company` varchar(64) DEFAULT NULL,
`customers_street_address` varchar(64) NOT NULL DEFAULT '',
`customers_suburb` varchar(32) DEFAULT NULL,
`customers_city` varchar(32) NOT NULL DEFAULT '',
`customers_postcode` varchar(10) NOT NULL DEFAULT '',
`customers_state` varchar(32) DEFAULT NULL,
`customers_country` varchar(32) NOT NULL DEFAULT '',
`customers_telephone` varchar(32) NOT NULL DEFAULT '',
`customers_email_address` varchar(96) NOT NULL DEFAULT '',
`customers_address_format_id` int(5) NOT NULL DEFAULT '0',
`delivery_name` varchar(64) NOT NULL DEFAULT '',
`delivery_company` varchar(64) DEFAULT NULL,
`delivery_street_address` varchar(64) NOT NULL DEFAULT '',
`delivery_suburb` varchar(32) DEFAULT NULL,
`delivery_city` varchar(32) NOT NULL DEFAULT '',
`delivery_postcode` varchar(10) NOT NULL DEFAULT '',
`delivery_state` varchar(32) DEFAULT NULL,
`delivery_country` varchar(32) NOT NULL DEFAULT '',
`delivery_address_format_id` int(5) NOT NULL DEFAULT '0',
`billing_name` varchar(64) NOT NULL DEFAULT '',
`billing_company` varchar(64) DEFAULT NULL,
`billing_street_address` varchar(64) NOT NULL DEFAULT '',
`billing_suburb` varchar(32) DEFAULT NULL,
`billing_city` varchar(32) NOT NULL DEFAULT '',
`billing_postcode` varchar(10) NOT NULL DEFAULT '',
`billing_state` varchar(32) DEFAULT NULL,
`billing_country` varchar(32) NOT NULL DEFAULT '',
`billing_address_format_id` int(5) NOT NULL DEFAULT '0',
`payment_method` varchar(128) NOT NULL DEFAULT '',
`payment_module_code` varchar(32) NOT NULL DEFAULT '',
`shipping_method` varchar(128) NOT NULL DEFAULT '',
`shipping_module_code` varchar(32) NOT NULL DEFAULT '',
`coupon_code` varchar(32) NOT NULL DEFAULT '',
`cc_type` varchar(20) DEFAULT NULL,
`cc_owner` varchar(64) DEFAULT NULL,
`cc_number` varchar(32) DEFAULT NULL,
`cc_expires` varchar(4) DEFAULT NULL,
`cc_cvv` blob,
`last_modified` datetime DEFAULT NULL,
`date_purchased` datetime DEFAULT NULL,
`orders_status` int(5) NOT NULL DEFAULT '0',
`orders_date_finished` datetime DEFAULT NULL,
`currency` char(3) DEFAULT NULL,
`currency_value` decimal(14,6) DEFAULT NULL,
`order_total` decimal(14,2) DEFAULT NULL,
`order_tax` decimal(14,2) DEFAULT NULL,
`paypal_ipn_id` int(11) NOT NULL DEFAULT '0',
`ip_address` varchar(96) NOT NULL DEFAULT '',
`ExportedToTradebox` int(1) NOT NULL DEFAULT '0',
PRIMARY KEY (`orders_id`),
KEY `idx_status_orders_cust_zen` (`orders_status`,`orders_id`,`customers_id`),
KEY `idx_date_purchased_zen` (`date_purchased`),
KEY `idx_cust_id_orders_id_zen` (`customers_id`,`orders_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=23075 ;

订单属性:

CREATE TABLE IF NOT EXISTS `orders_products_attributes` (
`orders_products_attributes_id` int(11) NOT NULL AUTO_INCREMENT,
`orders_id` int(11) NOT NULL DEFAULT '0',
`orders_products_id` int(11) NOT NULL DEFAULT '0',
`products_options` varchar(32) NOT NULL DEFAULT '',
`products_options_values` text NOT NULL,
`options_values_price` decimal(15,4) NOT NULL DEFAULT '0.0000',
`price_prefix` char(1) NOT NULL DEFAULT '',
`product_attribute_is_free` tinyint(1) NOT NULL DEFAULT '0',
`products_attributes_weight` float NOT NULL DEFAULT '0',
`products_attributes_weight_prefix` char(1) NOT NULL DEFAULT '',
`attributes_discounted` tinyint(1) NOT NULL DEFAULT '1',
`attributes_price_base_included` tinyint(1) NOT NULL DEFAULT '1',
`attributes_price_onetime` decimal(15,4) NOT NULL DEFAULT '0.0000',
`attributes_price_factor` decimal(15,4) NOT NULL DEFAULT '0.0000',
`attributes_price_factor_offset` decimal(15,4) NOT NULL DEFAULT '0.0000',
`attributes_price_factor_onetime` decimal(15,4) NOT NULL DEFAULT '0.0000',
`attributes_price_factor_onetime_offset` decimal(15,4) NOT NULL DEFAULT '0.0000',
`attributes_qty_prices` text,
`attributes_qty_prices_onetime` text,
`attributes_price_words` decimal(15,4) NOT NULL DEFAULT '0.0000',
`attributes_price_words_free` int(4) NOT NULL DEFAULT '0',
`attributes_price_letters` decimal(15,4) NOT NULL DEFAULT '0.0000',
`attributes_price_letters_free` int(4) NOT NULL DEFAULT '0',
`products_options_id` int(11) NOT NULL DEFAULT '0',
`products_options_values_id` int(11) NOT NULL DEFAULT '0',
`products_prid` tinytext NOT NULL,
`tradebox_attributes_list` text NOT NULL,
PRIMARY KEY (`orders_products_attributes_id`),
KEY `idx_orders_id_prod_id_zen` (`orders_id`,`orders_products_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=77065 ;

订购产品:

CREATE TABLE IF NOT EXISTS `orders_products` (
`orders_products_id` int(11) NOT NULL AUTO_INCREMENT,
`orders_id` int(11) NOT NULL DEFAULT '0',
`products_id` int(11) NOT NULL DEFAULT '0',
`products_model` varchar(32) DEFAULT NULL,
`products_name` varchar(64) NOT NULL DEFAULT '',
`products_price` decimal(15,4) NOT NULL DEFAULT '0.0000',
`final_price` decimal(15,4) NOT NULL DEFAULT '0.0000',
`products_tax` decimal(7,4) NOT NULL DEFAULT '0.0000',
`products_quantity` float NOT NULL DEFAULT '0',
`onetime_charges` decimal(15,4) NOT NULL DEFAULT '0.0000',
`products_priced_by_attribute` tinyint(1) NOT NULL DEFAULT '0',
`product_is_free` tinyint(1) NOT NULL DEFAULT '0',
`products_discount_type` tinyint(1) NOT NULL DEFAULT '0',
`products_discount_type_from` tinyint(1) NOT NULL DEFAULT '0',
`products_prid` tinytext NOT NULL,
PRIMARY KEY (`orders_products_id`),
KEY `idx_orders_id_prod_id_zen` (`orders_id`,`products_id`),
KEY `idx_prod_id_orders_id_zen` (`products_id`,`orders_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=34938 ;

Answer 1

SELECT *  --You can select your own columns later
    FROM orders_products_attributes oa
         RIGHT OUTER JOIN orders_products op
             ON oa.orders_products_id = op.orders_products_id
                 AND products_options_id IN (5, 1, 2)
    WHERE op.orders_id IN (
            SELECT DISTINCT o.orders_id
                FROM orders o
                WHERE o.ExportedToTradebox =  0
                    AND o.orders_id >  22000
        )

假设您的 order_products_ids 引用同一条信息，这将使您在一张表中获得所有内容。每个属性都会有一行，其中包含重复的 Order_Products 数据。如果您想要将信息连接在一起，您可以随时尝试 GROUP_CONCAT:

SELECT op.orders_products_id
        , GROUP_CONCAT(
          oa.products_options_id 
          ORDER BY CASE oa.products_options_id
              WHEN 5 THEN 1
              WHEN 1 THEN 2
              WHEN 2 THEN 3
              ELSE 4
          END ASC
          SEPARATOR '::'
        ) AS Options
    FROM orders_products_attributes oa
        RIGHT OUTER JOIN orders_products op
            ON oa.orders_products_id = op.orders_products_id
                AND oa.products_options_id IN (5, 1, 2)
    WHERE op.orders_id IN (
            SELECT DISTINCT o.orders_id
                FROM orders o
                WHERE o.ExportedToTradebox =  0
                    AND o.orders_id >  22000
        )
    GROUP BY op.orders_products_id

;

这将为您提供一个逗号分隔的列表。只是取决于您需要什么。