mysql - Where 子句未从表中提取正确的数据

Question

我的子表包含以下列:idSubs、姓氏、名字、电话、电子邮件、类型

我的 subProjects 表包含以下列:idSubs、idProjects、PaidOutAmount

我正在尝试获取 PaidOutAmount 中显示的最小金额和最大金额。我还想要支付这些款项的承包商的姓氏。

我运行以下命令:

Select min(PaidOutAmount) least, subs.LastName as PaidTo,   
max(PaidOutAmount) max, subs.LastName as PaidTo
from subprojects, subs 
where subprojects.idSubs = subs.idSubs

结果表是:

least   PaidTo   max      PaidTo
126.00  Torrey  5650.55   Torrey

这就是我想要的，除了第一个“PaidTo”列下出现的名称是“Torrey”，它应该是“Bassler”。在我的 subProjects 表中，最小金额为 126.00，但 idSubs 为 6。如果我转到我的 subs 表，6 实际上指的是姓 Bassler 的承包商。我不知道我做错了什么。

根据豪尔赫的回答，我尝试了以下操作:

Select subs.Lastname as PaidTo, PaidOutAmount as Value from subprojects,
subs where subprojects.idSubs = subs.idSubs and PaidOutAmount =    
min(PaidOutAmount) or PaidOutAmount = max(PaidOutAmount)

在 stackoverflow 上的其他地方发现 max 对于 where 子句无效并且应该使用子查询后，我将其更改为:

Select subs.Lastname as PaidTo, PaidOutAmount as Value from subprojects,
subs where subprojects.idSubs = subs.idSubs and (select PaidOutAmount =  
min(PaidOutAmount)or PaidOutAmount = max(PaidOutAmount))

这会生成一个包含所有订阅者和所有 PaidOutAmount 的表。换句话说，它并不像我试图做的那样只提取最小值和最大值。经过实验，我发现上述任何配置或变体都会产生与我简单使用以下内容相同的结果:

Select subs.Lastname as PaidTo, PaidOutAmount as Value from subprojects,
subs 

可能是什么问题？

抱歉，我太笨了，我只是没有看到问题所在。根据你的建议，我去了 SQlFiddle。我修改了您建议的查询:

SELECT
 subs.LastName AS PaidTo, subprojects.PaidOutAmount as Value
FROM subprojects AS S
  INNER JOIN (
        SELECT
              idSubs
            , MIN(PaidOutAmount) MinPaidOutAmount
            , MAX(PaidOutAmount) MaxPaidOutAmount
        FROM subprojects
        GROUP BY
              idSubs
  ) AS
  subs ON S.idSubs = subs.idSubs
 WHERE S.PaidOutAmount =  subs.MinPaidOutAmount
  OR S.PaidOutAmount = subs.MaxPaidOutAmount

我放入SqlFiddle的相关表是这样的:

CREATE TABLE subs
(idSubs int(11) NOT NULL ,
LastName VARCHAR(20),
FirstName VARCHAR(15)  ,
Phone VARCHAR(10)  ,
Email VARCHAR(45)  ,
Type VARCHAR(15),
PRIMARY KEY (`idSubs`));

CREATE TABLE  subprojects (
idSubs int(11) NOT NULL ,
idProjects INT(11) NOT NULL,
PaidOutAmount DECIMAL(7,2) NULL DEFAULT NULL ,
PRIMARY KEY (`idSubs`, `idProjects`));

INSERT INTO SubProjects VALUES (1, 200, 755.23);
INSERT INTO SubProjects VALUES (6, 200, 126.00);
INSERT INTO SubProjects VALUES (8, 200, 2523.53);
INSERT INTO SubProjects VALUES (1, 300, 5650.55);
INSERT INTO SubProjects VALUES (6, 300, 750.19);
INSERT INTO SubProjects VALUES (7, 300, 2500.00);
INSERT INTO SubProjects VALUES (8, 100, 374.14);



INSERT INTO Subs VALUES    
(1,'Torrey','Brian','5635435877','torrey@torrey.com', 'Carpenter');
INSERT INTO Subs  VALUES (2, 'McDonough', 'Mark', '5635997115',   
'McDonough@gmail.com', 'Carpenter');
INSERT INTO Subs VALUES (3, 'Basler', 'Bruce', '5635904457',  
'BB@yahoo.com', 'Carpenter');
INSERT INTO Subs  VALUES (4, 'ThePainter', 'Tyler', '5635800330',  
'paintpros@gmail.com', 'Painter');
INSERT INTO Subs VALUES (5, 'ThePainter2', 'Adam', '5635642819',  
'adampnt@gmail.com', 'Painter');
INSERT INTO Subs VALUES (6, 'A&G', 'Dean', '5635902919',  
'dean12@agelectric.com', 'Electrician');
INSERT INTO Subs VALUES (7, 'AllSeasons', 'Dean', '5635431296',  
'service@allseasons.com', 'Heating/Cooling');
INSERT INTO Subs VALUES (8, 'Gregory', 'Tom', '5634950997',   
'tgregory@floorshowcompanies.com', 'Flooring');
INSERT INTO Subs VALUES (9, 'Jaeger', 'Kevin', '5635836677',   
'kevin@jaegerplmb.com', 'Plumbing');

Answer 1

要过滤您需要的聚合:

• a.对数据进行分组，并且
• b.使用 HAVING 子句

但是您的查询需要其他内容 - 子查询，如下所示:

SELECT
      s.*
FROM subprojects AS S
      INNER JOIN (
            SELECT
                  idSubs
                , MIN(PaidOutAmount) MinPaidOutAmount
                , MAX(PaidOutAmount) MaxPaidOutAmount
            FROM subprojects
            GROUP BY
                  idSubs
      ) AS
      subs ON S.idSubs = subs.idSubs
WHERE S.PaidOutAmount =  subs.MinPaidOutAmount
      OR S.PaidOutAmount = subs.MaxPaidOutAmount