问题[subquery](dba)

我有一个 SQLite DB，假设 id 可以被视为行的顺序，我想根据某些标准在表中查找重复序列。

考虑这个例子（http://sqlfiddle.com/#!5/dbdb0/1）

架构+数据：

CREATE TABLE eventLog 
    (
     id integer primary key autoincrement, 
     type varchar(20), 
     details varchar(30)
    );

INSERT INTO eventLog
(type, details)
VALUES
('power', 'power on');

INSERT INTO eventLog
(type, details)
VALUES
('cleaner', 'cleaning started');

INSERT INTO eventLog
(type, details)
VALUES
('cleaner', 'cleaning finished');

INSERT INTO eventLog
(type, details)
VALUES
('power', 'power off');

INSERT INTO eventLog
(type, details)
VALUES
('power', 'power on');

INSERT INTO eventLog
(type, details)
VALUES
('cleaner', 'cleaning started');

INSERT INTO eventLog
(type, details)
VALUES
('power', 'power off');

基本查询：

select * from eventLog
order by id asc

结果：

我突出显示了一些序列：如果我查看“详细信息”中的重复序列，结果应该是 ids：[1,2][5,6]，如果我查看“类型”列，结果应该是 [[ 1,2][5,6]]，[[3,4][6,7]]。

有没有一种方法无需外部编程即可查询重复序列 - 仅使用 SQL？

以下目标实际上并不是我的查询的目的，但我用它作为类比来更简单地解释我想要实现的目标：

我正在尝试构建一个 BigQuery 脚本，它查看所有触发了破坏警报的街机机器，然后识别所有那些自警报以来没有进行自动售货机的机器，或者第一次售货机超过 28 台的机器在触发警报后的几天内，以识别在触发警报后可能发生免费使用的机器。

到目前为止，我已经突出显示了所有这些实例，但是在我将 Payments 表加入查询的情况下，所有在每台机器的警报返回后 28 天以上进行的交易，我只对在警报。

我有 3 张桌子

警报

顾客

付款

所以在这种情况下： cust_num "1" 不会被返回，因为在警报后不到 28 天有售卖 cust_num "2" 将返回售卖日期为 NULL，因为自警报 cust_num 以来没有进行任何售卖“3” 将返回销售日期为“2022-03-01”，因为这是警报 cust_num 之后的第一个 Vend “4” 将返回销售日期为“2022-04-20”，因为这是报警后的第一个 Vend

我需要返回所有 3 个字段，因此根据上面的示例，我的输出将是

我尝试在我的 select 语句中添加一个子查询，如下所示：

MIN(vend_date)
FROM payments AS paymin
WHERE paymin.vend_date > alarm_date)
AS vend_date

然而，当我将子查询添加到现有查询时，这往往会导致 bigquery 运行的时间比我有耐心等待它的时间更长。

我以前从未在其中一个网站上寻求过帮助，所以如果我在错误的地方或以错误的方式提问，请道歉！我对 BQ 还比较陌生，并且与业内的任何分析师都相距甚远。

非常感谢任何帮助！

干杯

__ 编辑：

所以我放弃了子查询，它太密集了，因为它要查询近 200 万行！

我尝试使用简单的 MIN 并将所有内容组合在一起，类似于这个简化的示例

SELECT
 payments.cust_num,
 alarm.machine_num, p.PAN, alarm.alarm_date, MIN(payments.vend_date) as vend_Date
 
 FROM alarm
    
LEFT JOIN customer
    ON alarm.machine_num = customer.machine_num
 INNER JOIN payments
    ON customer.cust_num =payments.cust_num 

WHERE 
vend_Date > DATE_ADD(alarm.alarm_date, INTERVAL +28 DAY) OR 
vend_Date IS NULL

GROUP BY payments.cust_num, alarm.machine_num, alarm.alarm_date
ORDER BY  payments.cust_num

但是，这并没有选择在 alarm_date 之后的第一个 vend_date 为 NULL 的实例它也只返回比警报日期晚 28 天的第一个日期，而不是当第一个 vend_date 比 alarm_date 之后的 28 天新时的帐户

我需要找到给定日期范围内表格（有很多部分）中每个部分的最高成本和最低成本。并输出记录每个成本的日期。

示例数据：

CREATE TABLE v_po_history
(Part int,cost numeric(20,6),date_received date);

INSERT INTO v_po_history

VALUES
(846060,28.373,'1/5/2022'),
(846060,27.588,'3/8/2022'),
(846060,29.143,'4/25/2022'),
(846060,29.143,'2/28/2022'),
(70/1300/100,176.500,'1/7/2022'),
(70/1300/100,195.000,'3/19/2022'),
(80/800/75,77.846,'2/1/2022'),
(80/800/75,76.688,'4/19/2022'),
(80/800/75,76.602,'4/13/2022'),
(800372,0.9925,'1/1/2022'),
(800372,0.9925,'1/19/2022'),
(800372,0.9925,'4/1/2022'),
(800372,0.9925,'3/10/2022');

我需要我的输出看起来像：

|  Part  |  Lowest Cost  |  Date  |  Highest Cost  |  Date  |
|--------|---------------|--------|----------------|--------|
| 846060 |    27.588     | 3/8/22 |     29.143     | 4/25/22|
|70/13...|    176.500    | 1/7/22 |     195.000    | 3/19/22|
|80/80...|    76.602     | 4/13/22|     77.846     | 2/1/22 |
| 800372 |    0.9925     | 1/1/22 |     0.9925     | 4/1/22 |

查询我从其他论坛拼凑而成：

select distinct part, description, location, t_fd.First_Date, t_fd.lowest_cost, t_ld.Last_Date, t_ld.highest_cost from
v_po_history, 
--date for lowest cost
(select top 1 date_received as First_Date, min(cost) as lowest_cost from v_po_history where 
cost = (select min(cost) from v_po_history where part not like '*%' and date_received >= '2022-01-01' and date_received <= '2022-05-01' and location = 'HS')
and date_received >= '2022-01-01' and date_received <= '2022-05-01' 
group by date_received) as t_fd,
-- date for highest cost
(select top 1 date_received Last_Date, max(cost) as highest_cost from v_po_history where 
cost = (select max(cost) from v_po_history where part not like '*%' and date_received >= '2022-01-01' and date_received <= '2022-05-01' and location = 'HS')
and date_received >= '2022-01-01' and date_received <= '2022-05-01' 
group by date_received) as t_ld
where part not like '*%' and date_received >= '2022-01-01' and date_received <= '2022-05-01' 
and location = 'HS'

输出：

|  Part  |  Lowest Cost  |  Date  |  Highest Cost  |  Date  |
|--------|---------------|--------|----------------|--------|
| 846060 |    0.9925     | 1/1/22 |     195.000    | 4/25/22|
|70/13...|    0.9925     | 1/1/22 |     195.000    | 4/25/22|
|80/80...|    0.9925     | 1/1/22 |     195.000    | 4/25/22|
| 800372 |    0.9925     | 1/1/22 |     195.000    | 4/25/22|

我明白为什么我会得到这些结果。该查询仅选择最低成本和最高成本，但不是针对每个部分，仅选择表中的最低和最高值。我的问题是如何调整它以获得我想要的结果？还是我需要放弃这个查询并以不同的方式处理这个问题？我正在使用 Pervasive SQL 顺便说一句。这是一个小提琴

我对这里的 SQL 感到非常困惑，我有这个示例，我在子查询中使用主查询中的值，然后使用 XML PATH 在日期之间添加逗号。设置是 2 个表，其中一个带有我想串在一起的日期。我不确定为什么子查询 where 语句没有重新调整正确的结果。

http://www.sqlfiddle.com/#!5/5443b/2

设置

CREATE TABLE log
    ([logID] [int] NULL, 
     [LogDate] [datetime] NULL
    )
;

CREATE TABLE logdata
    ([logdataID] [int] NULL, 
     [logID] [datetime] NULL
    )
;
    
INSERT INTO log
    ([logID], [LogDate])
VALUES
    (1, 2021-02-01),
    (1, 2021-02-02),
    (1, 2021-02-03),
    (3, 2021-03-12),
    (4, 2021-02-12)
;

INSERT INTO logdata
    ([logdataID], [logID])
VALUES
    (1, 1),
    (2, 2),
    (3, 3)
;

我的尝试：

Select 
logID,
logdataID
LogDate =  (SELECT ',' + CAST(LogDate AS varchar) FROM log WHERE logID =  logID FOR XML PATH('') )
from logdata

结果

1,2021-02-01,2021-02-02,2021-02-03,2021-03-12,2021-02-12
2,2021-02-01,2021-02-02,2021-02-03,2021-03-12,2021-02-12
3,2021-02-01,2021-02-02,2021-02-03,2021-03-12,2021-02-12

但我想要的结果是：

1 2021-02-01,2021-02-02,2021-02-03
2 2021-03-12
3 2021-02-12

我创建了一个查询，该查询可以返回具有 YTD、去年、前一年和前一年的不同列的特定组的所有收入。

它的工作原理非常缓慢，而且由于我使用的子查询，我可以告诉它。我最近开始了一份新工作，以前在 MSSQL 上使用子查询不是问题，对性能几乎没有影响，但我想知道这是否是 Postgres 的问题？

我在我运行的最后一个查询中限制了 10 行，并且花了 3 分钟才恢复 10 行。

代码：

SELECT 
g.id, 
COALESCE(g.description, '') AS description,
(select sum(d.total) from ainvdet d inner join ainvhead h USING (ainvheadid) where d.product_group = g.id and  date_part('year', h.order_date) = date_part('year', current_date)) as salesytd,
(select sum(d.total) from ainvdet d inner join ainvhead h USING (ainvheadid) where d.product_group = g.id and  date_part('year', h.order_date) = date_part('year', current_date)-1) as salesytd1,
(select sum(d.total) from ainvdet d inner join ainvhead h USING (ainvheadid) where d.product_group = g.id and  date_part('year', h.order_date) = date_part('year', current_date)-2) as salesytd2,
(select sum(d.total) from ainvdet d inner join ainvhead h USING (ainvheadid) where d.product_group = g.id and  date_part('year', h.order_date) = date_part('year', current_date)-3) as salesytd3
FROM grpdesc g 
WHERE g.company = 1 AND g.record_type = 'G'
GROUP BY g.id, g.description

我正在开发一个 php 应用程序，需要从 mysql 表中检索最新记录。下面是表格设计和样本数据。

所以标准是：

按当前 DESC 排序，然后按 start_date DESC

预期产出

我的想法如下，有没有更好的解决方案？

SELECT * 
  FROM `experience` r 
  JOIN (
        SELECT MAX(start_date) as date, 
               resume_id 
          FROM `experience` 
         WHERE present = 1 
         group by resume_id
        )r2 ON r.resume_id = r2.resume_id 
           AND r.present = 1 
           AND r.start_date = r2.date  
UNION
SELECT * 
  FROM `experience` r 
  JOIN (
        SELECT MAX(start_date) as date, 
               resume_id 
          FROM `experience` 
         WHERE present = 0 
         group by resume_id
       ) r2 ON r.resume_id = r2.resume_id 
           AND r.present = 0 
           AND r.start_date = r2.date  
 WHERE NOT EXISTS (SELECT 1 
                     FROM `experience` 
                    WHERE present = 1 
                      AND r.resume_id = resume_id
                  )

// took around 11-20 seconds to process above query on total 499,000 records

解释查询

更新：

http://sqlfiddle.com/#!9/bc5a12/1

CREATE TABLE `experience`(
       `id` int(11) NOT NULL,
       `resume_id` varchar(9) DEFAULT NULL,
       `company_name` varchar(12) DEFAULT NULL,
       `start_date` date DEFAULT NULL,
       `end_date` date DEFAULT NULL,
       `present` varchar(7) DEFAULT NULL
       ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `experience`(
       `id`, 
       `resume_id`,  
       `company_name`, 
       `start_date`, 
       `end_date`, 
       `present`) 
 VALUES
(1, '1', 'abc', '2009-01-01', '2009-12-31', '0'),
(2, '1', 'def', '2010-01-01', '2012-12-31', '0'),
(3, '1', 'ghi', '2013-01-01', '0000-00-00', '1'),
(4, '2', 'abc', '2009-01-01', '2009-12-31', '0'),
(5, '2', 'def', '2010-01-01', '2012-12-31', '0'),
(6, '2', 'ghi', '2013-01-01', '2016-01-01', '0'),
(7, '3', 'abc', '2009-01-01', '2009-12-31', '0'),
(8, '3', 'def', '2017-01-01', '0000-00-00', '1'),
(9, '3', 'ghi', '2010-01-01', '0000-00-00', '1');

ALTER TABLE `experience`
  ADD PRIMARY KEY (`id`);

由于我无法弄清楚的原因，我有这个相当基本的查询非常慢：

SELECT s.id 
FROM segments s
WHERE
    ST_DWithin(
        s.geom::GEOGRAPHY,
        ST_Envelope((SELECT ST_COLLECT(s2.geom) FROM segments s2 WHERE s2.id IN (407820025,  407820024,  407817407,  407817408,  407816908,  407816909,  407817413,  407817414,  407817409,  407817410,  407817405,  407817406,  407816905,  407816907,  407817412,  407817411,  407816906,  407816904,  407816764,  407816765)))::GEOGRAPHY,
        30
    );

                                                                                                                           QUERY PLAN                                                                                                                            
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Seq Scan on segments s  (cost=55.58..48476381.06 rows=7444984 width=4)
   Filter: st_dwithin((geom)::geography, (st_astext(st_envelope($0)))::geography, '30'::double precision)
   InitPlan 1 (returns $0)
     ->  Aggregate  (cost=55.57..55.58 rows=1 width=32)
           ->  Index Scan using segments_pkey on segments s2  (cost=0.44..55.52 rows=20 width=113)
                 Index Cond: (id = ANY ('{407820025,407820024,407817407,407817408,407816908,407816909,407817413,407817414,407817409,407817410,407817405,407817406,407816905,407816907,407817412,407817411,407816906,407816904,407816764,407816765}'::integer[]))

我真正感到困惑的是带有子查询的 ST_Envelope 本身非常快

SELECT ST_Envelope((SELECT ST_COLLECT(geom) FROM segments WHERE id IN (407820025,  407820024,  407817407,  407817408,  407816908,  407816909,  407817413,  407817414,  407817409,  407817410,  407817405,  407817406,  407816905,  407816907,  407817412,  407817411,  407816906,  407816904,  407816764,  407816765)))::GEOGRAPHY;

                                                                                                                           QUERY PLAN                                                                                                                            
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Result  (cost=55.58..55.60 rows=1 width=32)
   InitPlan 1 (returns $0)
     ->  Aggregate  (cost=55.57..55.58 rows=1 width=32)
           ->  Index Scan using segments_pkey on segments  (cost=0.44..55.52 rows=20 width=113)
                 Index Cond: (id = ANY ('{407820025,407820024,407817407,407817408,407816908,407816909,407817413,407817414,407817409,407817410,407817405,407817406,407816905,407816907,407817412,407817411,407816906,407816904,407816764,407816765}'::integer[]))

如果我插入 ST_Envelope 的结果，主查询也是如此

SELECT id 
FROM segments
WHERE
    st_dwithin(
        geom::geography,
        '0103000020E61000000100000005000000C87B6E0D8FB85EC04BFD8462B9C34640C87B6E0D8FB85EC0929B35C16DC44640BBF8DDA6F2B75EC0929B35C16DC44640BBF8DDA6F2B75EC04BFD8462B9C34640C87B6E0D8FB85EC04BFD8462B9C34640'::GEOGRAPHY,
        30
    );

                                                                                                                                                                                                                                                                                QUERY PLAN                                                                                                                                                                                                                                                                                
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Index Scan using segments_geom_geo_idx on segments  (cost=0.42..4.82 rows=1 width=4)
   Index Cond: ((geom)::geography && '0103000020E61000000100000005000000C87B6E0D8FB85EC04BFD8462B9C34640C87B6E0D8FB85EC0929B35C16DC44640BBF8DDA6F2B75EC0929B35C16DC44640BBF8DDA6F2B75EC04BFD8462B9C34640C87B6E0D8FB85EC04BFD8462B9C34640'::geography)
   Filter: (('0103000020E61000000100000005000000C87B6E0D8FB85EC04BFD8462B9C34640C87B6E0D8FB85EC0929B35C16DC44640BBF8DDA6F2B75EC0929B35C16DC44640BBF8DDA6F2B75EC04BFD8462B9C34640C87B6E0D8FB85EC04BFD8462B9C34640'::geography && _st_expand((geom)::geography, '30'::double precision)) AND _st_dwithin((geom)::geography, '0103000020E61000000100000005000000C87B6E0D8FB85EC04BFD8462B9C34640C87B6E0D8FB85EC0929B35C16DC44640BBF8DDA6F2B75EC0929B35C16DC44640BBF8DDA6F2B75EC04BFD8462B9C34640C87B6E0D8FB85EC04BFD8462B9C34640'::geography, '30'::double precision, true))

Postgres 不应该计算一次 ST_Envelope 然后将其用于 WHERE 条件，有效地执行我手动执行的操作吗？我也不明白为什么在原始查询中没有使用索引来执行过滤器。

我尝试将子查询放在 CTE 中，但这并没有解决问题。

我正在努力转换我的查询以摆脱子查询。我知道不使用子查询更好，但我更改此查询的第一个原因是因为我的 ORM（Doctrine）不能使用任何子查询连接，它不支持它（或 CTE）。

有没有办法摆脱这个查询中的子查询？

SELECT 
    s.id,
    e.exception,
    s.name,
    w.url,
    w.web_id,
    w.active,
    w.suspended,
    r.email,
    p.name AS partner,
    p.id AS partnerId,
    contacts.names AS contactNames,
    contacts.tels AS contactTels,
    contacts.emails AS contactEmails
FROM
    service s
        JOIN
    web w ON s.web_id = w.id
        JOIN
    rus r ON w.rus_id = r.id
        JOIN
    partner p ON r.partner_id = p.id
        LEFT JOIN
    exception e ON e.service_id = s.id
        LEFT JOIN
    (SELECT 
        p.id,
            GROUP_CONCAT(c.name) names,
            GROUP_CONCAT(c.tel) tels,
            GROUP_CONCAT(c.email) emails
    FROM
        partner p
    LEFT JOIN contact c ON c.partner_id = p.id
    WHERE
        c.main = 1 OR c.important = 1
    GROUP BY p.id) contacts ON contacts.id = p.id
        LEFT JOIN
    contact c ON c.partner_id = p.id

表格和样本数据：

CREATE TABLE `partner` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `ico` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `created` datetime NOT NULL,
  `active` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `partner_idx_active` (`active`),
  FULLTEXT KEY `partnerEntity` (`name`,`ico`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

INSERT INTO `partner` (`id`, `name`, `ico`, `created`, `active`) VALUES
(1, 'partner1', '123',  '2021-05-18 22:27:24',  1);

CREATE TABLE `contact` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `partner_id` int(11) DEFAULT NULL,
  `name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `email` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `tel` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `created` datetime NOT NULL,
  `active` int(11) NOT NULL,
  `main` int(11) DEFAULT NULL,
  `important` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `IDX_4C62E6389393F8FE` (`partner_id`),
  FULLTEXT KEY `contactEntity` (`name`,`email`,`tel`),
  CONSTRAINT `FK_4C62E6389393F8FE` FOREIGN KEY (`partner_id`) REFERENCES `partner` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

INSERT INTO `contact` (`id`, `partner_id`, `name`, `email`, `tel`, `created`, `active`, `main`, `important`) VALUES
(1, 1,  'contact1', '[email protected]',    '123456789',    '2021-05-18 22:28:30',  1,  1,  NULL),
(2, 1,  'contact2', '[email protected]',   '123456788',    '2021-05-18 22:28:48',  1,  NULL,   1),
(3, 1,  'contact3', '[email protected]',   '123451234',    '2021-05-18 22:29:13',  1,  NULL,   NULL);


CREATE TABLE `rus` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `partner_id` int(11) DEFAULT NULL,
  `email` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `created` datetime NOT NULL,
  `active` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `IDX_3370C8199393F8FE` (`partner_id`),
  KEY `rus_idx_active` (`active`),
  FULLTEXT KEY `rusEntity` (`email`),
  CONSTRAINT `FK_3370C8199393F8FE` FOREIGN KEY (`partner_id`) REFERENCES `partner` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

INSERT INTO `rus` (`id`, `partner_id`, `email`, `created`, `active`) VALUES
(1, 1,  '[email protected]',    '2021-05-18 22:27:36',  1);

CREATE TABLE `service` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `web_id` int(11) DEFAULT NULL,
  `name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `created` datetime NOT NULL,
  `active` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `IDX_E19D9AD2FE18474D` (`web_id`),
  KEY `service_idx_active` (`active`),
  FULLTEXT KEY `serviceEntity` (`name`),
  CONSTRAINT `FK_E19D9AD2FE18474D` FOREIGN KEY (`web_id`) REFERENCES `web` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

INSERT INTO `service` (`id`, `web_id`, `name`, `created`, `active`) VALUES
(1, 1,  'service1', '2021-05-18 22:28:08',  1);

CREATE TABLE `exception` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `partner_id` int(11) DEFAULT NULL,
  `exception` longtext COLLATE utf8_unicode_ci NOT NULL,
  `created` datetime DEFAULT NULL,
  `service_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `FK_7FC98E6D9393F8FE` (`partner_id`),
  KEY `FK_7FC98E6DED5CA9E6` (`service_id`),
  CONSTRAINT `FK_7FC98E6D9393F8FE` FOREIGN KEY (`partner_id`) REFERENCES `partner` (`id`),
  CONSTRAINT `FK_7FC98E6DED5CA9E6` FOREIGN KEY (`service_id`) REFERENCES `service` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

INSERT INTO `exception` (`id`, `partner_id`, `exception`, `created`, `service_id`) VALUES
(1, 1,  'test..',   '2021-05-18 22:31:14',  1);

CREATE TABLE `web` (
  `suspended` int(11) NOT NULL,
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `rus_id` int(11) DEFAULT NULL,
  `url` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `web_id` int(5) unsigned zerofill DEFAULT NULL,
  `created` datetime NOT NULL,
  `active` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `IDX_15C9385126907186` (`rus_id`),
  KEY `web_idx_active` (`active`),
  FULLTEXT KEY `webEntity` (`url`),
  CONSTRAINT `FK_15C9385126907186` FOREIGN KEY (`rus_id`) REFERENCES `rus` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

INSERT INTO `web` (`suspended`, `id`, `rus_id`, `url`, `web_id`, `created`, `active`) VALUES
(0, 1,  1,  'www.test.com', 01337,  '2021-05-18 22:27:54',  1);

小提琴手

我正在学习 SQL 课程，完全被这个问题难住了。

使用子查询有一个查询返回：CustomerID、EmailAddress、FirstName、LastName、'#3：订购超过 1 件产品的客户'作为 queryInfo 此查询的子查询将是：订购超过 1 件产品的所有客户

注意：不止一个产品，而不是数量 > 1 所以订购 10 个相同产品的客户不是我要找的。

这是我使用的完整查询：

SELECT c.CustomerID
     , c.EmailAddress
     , c.FirstName
     , c.LastName
     , '#3: Customers who have ordered more than 1 product' AS queryInfo
  FROM Customers c
 WHERE c.CustomerID IN (
           SELECT p.ProductID 
             FROM Customers c 
             JOIN Orders o
                  ON c.CustomerID = o.CustomerID
             JOIN OrderItems oi
                  ON o.OrderID = oi.OrderID
             JOIN Products p
                  ON oi.ProductID = p.ProductID 
         GROUP BY p.ProductID 
           HAVING COUNT(*) > 1
         )
     ;

不知道我做错了什么，但非常感谢任何输入。谢谢！

这一定是一个很简单的问题，子查询单独返回结果但是复合查询报告：

ERROR 1064 (42000)：您的 SQL 语法有错误；检查与您的 MariaDB 服务器版本相对应的手册，以了解在第 1 行的“table where table in (select table_name from information_schema.tables where tab”附近使用的正确语法

这是查询：

select * 
from table 
where table in 
    (select table_name 
     from information_schema.tables 
     where table_schema='my_database' 
     limit 1);

我也没有限制地尝试过，这是我的第一次尝试：

select * 
from table 
where table in 
    (select table_name 
     from information_schema.tables 
     where table_schema='my_database');