SQL Left Join返回的内容少于预期

I have a database, and I need to get data that is on the table PEDIDOC mixed in with FALTANTE, but I need to do a left join because I need ALL the data in PEDIDOC even if there is no matching FALTANTE. I have triple checked and all information is inputed correctly and everything is there, if I just do Select * FROM pedidoc I get what I expect when I filter it in Excel, but when I do my left joins, then there are productos missing.

我有一个数据库,我需要将表PEDIDOC中的数据与FALTANTE混合在一起,但我需要进行左连接,因为我需要PEDIDOC中的所有数据,即使没有匹配的FALTANTE。我有三重检查,所有信息都正确输入,一切都在那里,如果我只是做Select * FROM pedidoc我得到了我在Excel中过滤它时所期望的,但是当我做我的左连接时,则有产品缺失。

Here is the query

这是查询

SELECT
        `pedidoc`.`fecha`,
        `pedidoc`.`IdPedido`, `pedidoc`.`plaza`, `pedidoc`.`IdProducto`,
        `pedidoc`.`Categoria`,`pedidoc`.`Pedido`,`faltante`.`faltante`
FROM `pedidoc`
        LEFT JOIN `caducidad` ON `pedidoc`.`IdProducto`=`faltante`.`IdProducto`
GROUP BY `pedidoc`.`fecha`, `pedidoc`.`IdProducto`

Here is the database

这是数据库

CREATE TABLE Faltante (
    IdProducto DECIMAL(17,0) NOT NULL,
    Plaza VARCHAR(40) NOT NULL,
    Fecha VARCHAR(10) NOT NULL,
    Faltante INT NULL,
    FOREIGN KEY(IdProducto) REFERENCES Producto(IdProducto),
    UNIQUE(IdProducto, Fecha, Plaza)
)Engine=InnoDB;

CREATE TABLE pedidoc (
    IdProducto DECIMAL(17,0) NOT NULL,
    IdPedido VARCHAR(40) NOT NULL,
    Plaza VARCHAR(40) NOT NULL,
    Fecha VARCHAR(10) NOT NULL,
    Categoria VARCHAR(40) NOT NULL,
    Pedido INT NULL,
    FOREIGN KEY(IdProducto) REFERENCES Producto(IdProducto),
    UNIQUE(IdProducto, Fecha, Plaza)
)Engine=InnoDB;

The data that it does return is correct (took some random samples) but it is missing more than half of the data in pedidoc.

它确实返回的数据是正确的(采取了一些随机样本)但它缺少了一半以上的pedidoc数据。

What is wrong with my query?

我的查询有什么问题?

FYI I am running Windows 8 on my test machine and a WAMP stack.

仅供参考我在我的测试机器和WAMP堆栈上运行Windows 8。

Saludos, Gustavo

EDIT> Here is the table I get for the "first" day. The problem is that it only takes one product per day, when there is really one product per day per plaza. There is supposed to be one product list, almost the same for each plaza, but for Monterrey it only shows 2, instead of all the products

编辑>这是我在“第一天”获得的表格。问题是,每个广场每天只有一种产品,每天只需要一种产品。应该有一个产品清单,每个广场几乎相同,但对于蒙特雷,它只显示2个,而不是所有的产品

fecha           IdPedido    plaza   IdProducto  Categoria   Pedido  caducidad
01/01/2012  2589970-20  Mexico  4111    Bigdonuts   0   0
01/01/2012  2589970-20  Mexico  7920    Bigdonuts   406 0
01/01/2012  2589970-20  Mexico  7921    Bigdonuts   425 0
01/01/2012  2589970-20  Mexico  7922    Bigdonuts   712 0
01/01/2012  2589970-20  Mexico  7923    Bigdonuts   454 0
01/01/2012  2589970-20  Mexico  7924    Bigdonuts   31  0
01/01/2012  2589970-20  Mexico  7925    Bigdonuts   11  0
01/01/2012  2589970-20  Mexico  7926    Bigdonuts   147 0
01/01/2012  2590100-10  Monterrey   7928    Bigdonuts   128 0
01/01/2012  2590100-10  Monterrey   7929    Bigdonuts   70  0
01/01/2012  2590090-30  Reynosa 7931    Big Donuts  12  0
01/01/2012  2590090-30  Reynosa 7932    Big Donuts  154 0
01/01/2012  2590090-30  Reynosa 7933    Big Donuts  23  0
01/01/2012  2590090-30  Reynosa 7934    Big Donuts  169 0
01/01/2012  2590090-30  Reynosa 7935    Big Donuts  50  0
01/01/2012  2589970-20  Mexico  7936    Bigdonuts   352 0
01/01/2012  2590100-10  Monterrey   7937    Bigdonuts   0   0
01/01/2012  2590090-30  Reynosa 7938    Big Donuts  296 0
01/01/2012  2590090-30  Reynosa 7939    Big Donuts  12  0
01/01/2012  2590080-50  Saltillo    7941    Bigdonuts   64  0
01/01/2012  2590080-50  Saltillo    7942    Bigdonuts   38  0
01/01/2012  2589970-20  Mexico  7944    Bigdonuts   269 0
01/01/2012  2589970-20  Mexico  7945    Bigdonuts   284 0
01/01/2012  2589970-20  Mexico  7946    Bigdonuts   320 0
01/01/2012  2589970-20  Mexico  7954    Bigdonuts   0   0
01/01/2012  2589970-20  Mexico  7969    Bigdonuts   334 0
01/01/2012  2589970-20  Mexico  7970    Bigdonuts   246 0
01/01/2012  2589970-20  Mexico  7971    Bigdonuts   39  0
01/01/2012  2589970-20  Mexico  7972    Bigdonuts   327 0
01/01/2012  2589970-20  Mexico  8071    Bigdonuts   0   0
01/01/2012  2590080-50  Saltillo    8112    Bigdonuts   0   0
01/01/2012  2590080-50  Saltillo    8113    Bigdonuts   0   0
01/01/2012  2590080-50  Saltillo    8114    Bigdonuts   0   0
01/01/2012  2590080-50  Saltillo    8115    Bigdonuts   0   0
01/01/2012  2590080-50  Saltillo    8116    Bigdonuts   0   0
01/01/2012  2590080-50  Saltillo    8117    Bigdonuts   0   0
01/01/2012  2589970-20  Mexico  8212    Bigdonuts   0   0
01/01/2012  2589970-20  Mexico  8453    Bigdonuts   0   0
01/01/2012  2589970-20  Mexico  8454    Bigdonuts   0   0
01/01/2012  2589970-20  Mexico  8456    Bigdonuts   0   0
01/01/2012  2589970-20  Mexico  8457    Bigdonuts   0   0
01/01/2012  2590100-10  Monterrey   68895   Bigdonuts   0   0

2 个解决方案

#1

Just change

GROUP BY `pedidoc`.`fecha`, `pedidoc`.`IdProducto`

GROUP BY pedidoc.IdProducto, pedidoc.fecha, pedidoc.Plaza

so it matches your UNIQUE constraint on pedidoc, thereby ensuring that every row of pedidoc will appear exactly once.

所以它匹配你对pedidoc的UNIQUE约束,从而确保pedidoc的每一行都只出现一次。

(Note: your query, with or without this change, is not portable to other DBMSes, since it depends on special MySQL behavior; see §12.17.3 "MySQL Extensions to GROUP BY" in the MySQL 5.6 Reference Manual. I'm assuming that's O.K.?)

(注意:无论是否有此更改,您的查询都无法移植到其他DBMS,因为它取决于特殊的MySQL行为;请参阅MySQL 5.6参考手册中的§12.17.3“GROUP BY的MySQL扩展”。我假设没关系?)

#2

GROUP BY is SUPPOSED to reduce the number of rows

支持GROUP BY以减少行数

I think you want ORDER BY

我想你想要ORDER BY

If you just want to eliminate the duplicate records, you could use SELECT DISTINCT. Just add the DISTINCT keyword after the SELECT keyword, as described here: http://www.w3schools.com/sql/sql_distinct.asp

如果您只想消除重复记录,可以使用SELECT DISTINCT。只需在SELECT关键字后添加DISTINCT关键字,如下所述:http://www.w3schools.com/sql/sql_distinct.asp

However, I'm not certain whether that will give you what you want. ruakh is right about this being a non-portable extension, and more importantly for you, as noted in the reference on ruakh's answer, the GROUP BY will group into one record all of the records whose values are the same for each of the fields in the GROUP BY.

但是,我不确定这是否会给你你想要的东西。 ruakh说这是一个不可移植的扩展是正确的,对于你来说更重要的是,正如在ruakh的回答中提到的那样,GROUP BY将把所有记录的所有记录分成一个记录,其中每个记录的值都相同。 GROUP BY。

For any fields mentioned in the SELECT which are NOT mentioned in the GROUP BY, MySQL will randomly pick any of the values for those fields in those records. If all of those records have the same value for those fields, it will not matter which record is chosen. For example, in your sample data above, Categoria is Bigdonuts for all of the first set of eight records with plaza of Mexico, so it doesn't matter which value of Categoria is chosen.

对于SELECT中提到的GROUP BY中未提及的任何字段,MySQL将随机选择这些记录中这些字段的任何值。如果所有这些记录对这些字段具有相同的值,则选择哪条记录无关紧要。例如,在上面的示例数据中,Categoria对于墨西哥广场的所有第一组八个记录都是Bigdonuts,因此选择Categoria的哪个值无关紧要。

However, for Pedido, there are several different values shown, including 0, 406, and 425. MySQL will randomly pick one of those eight different values. I can't imagine that that is what you want.

但是,对于Pedido,显示了几个不同的值,包括0,406和425.MySQL将随机选择这八个不同值中的一个。我无法想象这就是你想要的。

That is why, in general, unless you know that all of records being grouped in a GROUP BY have the same values for a field, you will probably want to use one of the aggregate functions, like SUM, COUNT, etc.: http://www.w3schools.com/sql/sql_functions.asp

这就是为什么,除非您知道在GROUP BY中分组的所有记录对于字段具有相同的值,您可能希望使用其中一个聚合函数,如SUM,COUNT等:http: //www.w3schools.com/sql/sql_functions.asp

#1