代码之家 › 专栏 › 技术社区 › Bohao LI

如何使用子查询的结果

subquery sql mysql

Bohao LI · 技术社区 · 6 年前

我有三个SQL表,如下所示:

(以下“订单”表不完整)

如何仅使用一个sql查询来解决以下问题:

选择2014年订购的客户(至少)2013年名为“Smith”的客户订购的所有产品。

这可能吗?

我曾想过:

首先,找到客户命名为“Smith”的2013年订购的所有产品。
其次,找出2014年至少订购了上述所有产品的客户名单。

这就引出了如下SQL查询:

SELECT cname,
FROM customers
NATURAL JOIN orders
WHERE YEAR(odate) = '2014'
  AND "do_something_here"
    (SELECT DISTINCT pid
     FROM orders
     NATURAL JOIN customers
     WHERE LOWER(cname)='smith'
       AND YEAR(odate)='2013') as results;

其中应使用所有子查询结果(“Smith”在2013年订购的产品列表)来查找所需的客户。

但我不知道这是不是一个好办法。

对不起,我的英语不是以英语为母语的。

如果要在phpMyAdmin上进行测试,下面是SQL:

-- phpMyAdmin SQL Dump
-- version 4.7.5
-- https://www.phpmyadmin.net/
--
-- Host: localhost
-- Generation Time: Mar 21, 2018 at 02:49 PM
-- Server version: 5.7.20
-- PHP Version: 7.1.7

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET AUTOCOMMIT = 0;
START TRANSACTION;
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

--
-- Database: `tp1`
--

-- --------------------------------------------------------

--
-- Table structure for table `customers`
--

CREATE TABLE `customers` (
  `cid` int(11) NOT NULL,
  `cname` varchar(30) NOT NULL,
  `residence` varchar(50) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `customers`
--

INSERT INTO `customers` (`cid`, `cname`, `residence`) VALUES
(0, 'didnotorder', 'Great Britain'),
(1, 'Jones', 'USA'),
(2, 'Blake', NULL),
(3, 'Dupond', 'France'),
(4, 'Smith', 'Great Britain'),
(5, 'Gupta', 'India'),
(6, 'Smith', 'France');

-- --------------------------------------------------------

--
-- Table structure for table `orders`
--

CREATE TABLE `orders` (
  `pid` int(11) NOT NULL,
  `cid` int(11) NOT NULL,
  `odate` date NOT NULL,
  `quantity` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `orders`
--

INSERT INTO `orders` (`pid`, `cid`, `odate`, `quantity`) VALUES
(1, 1, '2014-12-12', 2),
(1, 4, '2014-11-12', 6),
(2, 1, '2014-06-02', 6),
(2, 1, '2014-08-20', 6),
(2, 1, '2014-12-12', 2),
(2, 2, '2010-11-12', 1),
(2, 2, '2014-07-21', 3),
(2, 3, '2014-10-01', 1),
(2, 3, '2014-11-12', 1),
(2, 4, '2014-01-07', 1),
(2, 4, '2014-02-22', 1),
(2, 4, '2014-03-19', 1),
(2, 4, '2014-04-07', 1),
(2, 4, '2014-05-22', 1),
(2, 4, '2014-09-12', 4),
(2, 6, '2014-10-01', 1),
(3, 1, '2014-12-12', 1),
(3, 2, '2013-01-01', 1),
(3, 4, '2015-10-12', 1),
(3, 4, '2015-11-12', 1),
(4, 1, '2014-12-12', 3),
(4, 2, '2014-06-11', 2),
(4, 5, '2014-10-12', 1),
(4, 5, '2014-11-13', 5),
(5, 2, '2015-07-21', 3),
(6, 2, '2015-07-21', 7),
(6, 3, '2014-12-25', 1);

-- --------------------------------------------------------

--
-- Table structure for table `products`
--

CREATE TABLE `products` (
  `pid` int(11) NOT NULL,
  `pname` varchar(30) NOT NULL,
  `price` decimal(7,2) NOT NULL,
  `origin` varchar(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `products`
--

INSERT INTO `products` (`pid`, `pname`, `price`, `origin`) VALUES
(0, 'wasnotordered', '11.00', NULL),
(1, 'chocolate', '5.00', 'Belgium'),
(2, 'sugar', '0.75', 'India'),
(3, 'milk', '0.60', 'France'),
(4, 'tea', '10.00', 'India'),
(5, 'chocolate', '7.50', 'Switzerland'),
(6, 'milk', '1.50', 'France');

--
-- Indexes for dumped tables
--

--
-- Indexes for table `customers`
--
ALTER TABLE `customers`
  ADD PRIMARY KEY (`cid`);

--
-- Indexes for table `orders`
--
ALTER TABLE `orders`
  ADD PRIMARY KEY (`pid`,`cid`,`odate`),
  ADD KEY `orders_fk_cid` (`cid`);

--
-- Indexes for table `products`
--
ALTER TABLE `products`
  ADD PRIMARY KEY (`pid`);

--
-- Constraints for dumped tables
--

--
-- Constraints for table `orders`
--
ALTER TABLE `orders`
  ADD CONSTRAINT `orders_fk_cid` FOREIGN KEY (`cid`) REFERENCES `customers` (`cid`),
  ADD CONSTRAINT `orders_fk_pid` FOREIGN KEY (`pid`) REFERENCES `products` (`pid`);
COMMIT;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

2 回复 | 直到 6 年前

EzLo tumao kaixin 6 年前

您可以尝试以下方法。基本上是用史密斯2013年的所有产品与客户联合,然后 LEFT JOIN 2014年每位客户购买的产品。如果两个数量相等,则意味着史密斯2013年的所有产品在2014年至少为每个客户购买了一次。

SELECT
    C.cid
FROM
    Customers C

    CROSS JOIN (
        SELECT DISTINCT
            P.pid
        FROM
            Customers C
            INNER JOIN Orders O ON C.cid = O.cid
            INNER JOIN Products P ON O.pid = P.pid
        WHERE
            C.cname = 'Smith' AND
            YEAR(O.odate) = 2013) X

    LEFT JOIN (
        SELECT DISTINCT
            C.cid,
            P.pid
        FROM
            Customers C
            INNER JOIN Orders O ON C.cid = O.cid
            INNER JOIN Products P ON O.pid = P.pid
        WHERE
            YEAR(O.odate) = 2014) R ON C.cid = R.cid AND X.pid = R.pid
GROUP BY
    C.cid
HAVING
    COUNT(X.pid) = COUNT(R.pid)

如果您想看到客户,即使没有史密斯2013年的产品,您也可以切换 CROSS JOIN 对于 FULL JOIN (...) X ON 1 = 1 。

Gordon Linoff 6 年前

您可以通过查找每个 cid 与史密斯的客户有共同之处。然后,只需检查计数是否涵盖所有产品:

select o2014.cid, count(distinct o2013.pid) as num_products,
       group_concat(distinct o2013.pid) as products
from orders o2013 join
     orders o2014
     on o2013.pid = o2014.pid and
        year(o2013.odate) = 2013 and year(o2014.odate) = 2014
where o2013.cid = (select c.cid from customers c where c.cname = 'Smith')
group by o2014.cid
having num_products = (select count(distinct o2013.products)
                       from orders o2013
                       where o2013.cid = (select c.cid from customers c where c.cname = 'Smith')
                      );