查询连接在一起

查询连接在一起

关注
发信
关注(28)粉丝(399)

如果可能，需要将两个 mySQL 查询连接在一起

本文介绍了如果可能，需要将两个 mySQL 查询连接在一起的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

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

Is it possible to combine these two queries to form one?

第一次查询:

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知识非常零散.

I've tried several times and i just get SQL errors reported by phpMyAdminSorry if it's something simple, my sql knowledge is very patchy.

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

Combined so that all the required information is brought into one array, when they are run. I have tried to use it from the two separate arrays it generates, but the php code in the program is poorly designed with multiple while loops along with else-if statements. Attempting to bring in yet another while-MoveNext loop just crashes everything. I figured if all the data can be collected to the original array it will be simple enough to echo the new fields.

相关数据库表结构如下:

Related DB table structures below:

订单表:

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 ;

推荐答案

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:

Presuming your order_products_ids reference the same piece of information, this will get you everything in one table. You will have a row for each attribute, with the Order_Products Data Duplicated. If you want information concatenated together, you can always try 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

;

这会给你一个逗号分隔的列表.只是有点取决于你需要什么.

That'll get you a Comma Separated list. Just sort of depends on what you need.

这篇关于如果可能，需要将两个 mySQL 查询连接在一起的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！

09-05 06:13
Powered by LMLPHP ©2024 查询连接在一起 0.047433