SQL只获得表中的第一个记录行，我希望全部 [英] sql having get only the first recorded row in table and i want all

问题描述

  SELECT 
有人已经帮我完成了这个查询，但是我做了一个改编，并且遇到了一个问题： AVG（tyd.price）AS avg_price，COUNT（tyd.id_product）AS cnt，
 tyd.id_marchand，tyd.id_product，
 catalog.price AS c_price，tyd.price AS t_price，
 tyd.amount AS t_am，pro_tyd.amount AS p_am，
 pro_tyd.price AS p_price，catalog.img_src，
 tyd.step，tyd.login AS tyd_l 
 FROM catalog 
 INNER JOIN tyd ON catalog.id_marchand = tyd.id_marchand 
 AND catalog.id_product = tyd.id_product 
 AND tyd.step =1
 INNER JOIN pro_tyd ON tyd.id_marchand = pro_tyd.id_marchand 
 AND tyd.id_product = pro_tyd.id_product 
 GROUP BY 
 catalog.id_product，catalog.id_marchand 
 HAVING 
 tyd.login =[e-mail]
  

只有在 tyd.login =[e-mail]这是较低的ID。不适用于user1或user2 ...我只是不明白为什么......！另外，如果我通过tyd.login进行分组，但是在这种情况下，AVG和COUNT函数只能在单行上运行...

下面是表格： p>

  id id_marchand id_product登录价格金额延迟步骤时间
 29 1 1 [e-mail] 344 1 0 1 1343297500 
 120 1 1 [e-mail] 54 1 0 1 1343297504 
 109 1 1 [e-mail] 34 1 0 1 1343298598 
  

当 HAVING tyd.login =[e-mail]它完美地工作。当user1或user2我有0行。

感谢您的帮助

第一个主题： Sql，在避开where条件的情况下获得组的平均值

解决方案

问题是您的查询是非确定性的。您正在选择更多的列，按照您正在分组的列确定附加列的位置。如果后者是真的，那么这将落入SQL的ANSII标准，因为它不是（我认为）MySQL的失败，它允许声明运行。其他数据库管理系统已经走过了相反的道路，因为他们无法确定某些列是否属于组中未包含的其他列的功能，现在他们将允许任何带有选择列表中未包含在组中的列的语句。

为了尝试简化问题，请使用以下数据集（表T）

  ID Col1 Col2 
 1 1 1 
 2 1 3 
  

运行此操作：

$ b $ pre $ SELECT Col1，MAX（Col2）AS MaxCol2，MIN（Col2）AS MinCol2，AVG（Col2）AS AvgCol2
FROM T
GROUP BY Col1


总是会返回

  Col1 MaxCol2 MinCol2 AvgCol2 
 1 3 1 2 
  

但是，如果您将 ID 放入组合中

  SELECT ID，Col1，MAX（Col2）AS MaxCol2，MIN（Col2）AS MinCol2，AVG（Col2）AS AvgCol2 
 FROM T 
 GROUP BY Col1 
  

无法确定将返回哪个ID，1或2，最可能的结果是 p>

  ID Col1 MaxCol2 MinCol2 AvgCol2 
 1 1 3 1 2 
  

然而，在SQL中没有定义任何结果：

  ID Col1 MaxCol2 MinCol2 AvgCol2 
 2 1 3 1 2 
  

如果以上是结果集，并且向查询添加了 HAVING ID = 1 ，则由于HAVING子句应用于的点，您将不会得到任何结果数据。如果您要将ID添加到 GROUP BY ，您最终将得到2行，据我了解，它不是您想要的，并且如果要将它添加到 WHERE 您的MIN，MAX和AVG函数将受到影响。所以你需要使用子查询。所以在这个例子中，我会使用

  SELECT T.ID，T.Col1，MaxCol2，MinCol2，AvgCol2 
 FROM T 
 INNER JOIN 
（SELECT Col1，MAX（Col2）AS MaxCol2，MIN（Col2）AS MinCol2，AVG（Col2）AS AvgCol2 
 FROM T 
 GROUP BY Col1 
）T2 
 ON T.Col1 = T2.Col1 
 WHERE ID = 1  -  OR ID = 2取决于需求
  

为了将这种情况应用于您的情况，数据库引擎确定将针对不在组中的列返回的行是包含ID = 29的行。因此，您的HAVING子句仅应用于此行，在应用HAVING子句时，具有[e-mail]和[e-mail] in的行已从结果中删除。您需要分别对过滤执行聚合函数。

现在我还没有完全理解您的模式，但我希望我已经解释了这个问题。非确定性语句足够好，您可以对重写您的查询所需的任何修正进行修改

  SELECT Avg_Price，
 cnt，
 tyd.id_marchand，
 tyd.id_product，
 catalog.price AS c_price，
 tyd.price AS t_price，
 tyd.amount AS t_am，
 pro_tyd.amount AS p_am，
 pro_tyd.price AS p_price，
 catalog.img_src，
 tyd.step，
 tyd.login AS tyd_l 
 FROM目录
 INNER JOIN tyd 
 ON catalog.id_marchand = tyd.id_marchand 
 AND catalog.id_product = tyd.id_product 
 INNER JOIN Pro_tyd 
 ON tyd.id_marchand = pro_tyd.id_marchand 
和tyd.id _product = pro_tyd.id_product 
 INNER JOIN 
（SELECT ID_Marchand，ID_Product，Step，AVG（tyd.price）AS avg_price，COUNT（tyd.id_product）AS cnt 
 FROM Tyd 
 WHERE Step ='1'
 GROUP BY ID_Marchand，ID_Product，Step 
）Agg 
 ON Agg.id_marchand = pro_tyd.id_marchand 
 AND Agg.id_product = pro_tyd.id_product 
 AND Agg.Step = tyd.Step 
 WHERE tyd.Login ='[e-mail]'
  

Somebody already helped me with this query but I made an adaptation and I get a problem :

    SELECT 
        AVG(tyd.price) AS avg_price, COUNT(tyd.id_product) AS cnt, 
        tyd.id_marchand, tyd.id_product, 
        catalog.price AS c_price, tyd.price AS t_price, 
        tyd.amount AS t_am, pro_tyd.amount AS p_am, 
        pro_tyd.price AS p_price, catalog.img_src,  
        tyd.step, tyd.login AS tyd_l
    FROM catalog 
    INNER JOIN tyd ON catalog.id_marchand = tyd.id_marchand 
                   AND catalog.id_product =   tyd.id_product
                   AND tyd.step = "1" 
    INNER JOIN pro_tyd  ON tyd.id_marchand = pro_tyd.id_marchand 
                        AND tyd.id_product = pro_tyd.id_product
    GROUP BY 
         catalog.id_product, catalog.id_marchand
    HAVING 
         tyd.login = "[e-mail]"

and it only works when tyd.login = "[e-mail]" which is the lower id. doesn't work with user1 or user2...I just can't figure why...! Also it work if i group by tyd.login but in this case the AVG and COUNT function work only on a single line...

Here is the table :

id  id_marchand  id_product   login       price  amount  delay  step    time   
29      1           1       [e-mail]   344     1       0      1   1343297500
120     1           1       [e-mail]   54      1       0      1   1343297504
109     1           1       [e-mail]  34      1       0      1   1343298598

When HAVING tyd.login = "[e-mail]" it works perfectly. When user1 or user2 i got 0 lines.

thanks for your help

First subject : Sql, get the average on a group while escaping the where condition

解决方案

The problem is that your query is non deterministic. You are selecting more columns that you are grouping by where the additional columns are not determined by the columns you are grouping by. If the latter had been true, then this would fall into the ANSII standards of SQL, since it is not it is (in my opinion) a failure of MySQL that it allows the statement to run at all. Other DBMS have gone the opposite way, since they cannot determine if certain columns are functions of other columns not contained in the group by, they will now allow any statement with columns in the select list that are not contained within the group by.

To try and simplify the problem take the following dataset (Table T)

ID    Col1    Col2
1     1       1
2     1       3

Running this:

SELECT Col1, MAX(Col2) AS MaxCol2, MIN(Col2) AS MinCol2, AVG(Col2) AS AvgCol2
FROM T
GROUP BY Col1

Will always return

Col1    MaxCol2    MinCol2    AvgCol2
1       3          1          2

However, if you throw ID into the mix

SELECT ID, Col1, MAX(Col2) AS MaxCol2, MIN(Col2) AS MinCol2, AVG(Col2) AS AvgCol2
FROM T
GROUP BY Col1

There is no way of determining which ID will be returned, 1 or 2, the most likely result is

ID    Col1    MaxCol2    MinCol2    AvgCol2
1    1       3          1          2

However there is nothing defined in the SQL to state that the result could not be:

ID    Col1    MaxCol2    MinCol2    AvgCol2
2     1       3          1          2

So if if the above was the result set, and you added HAVING ID = 1 to the query, you would get no results due to the point at which the HAVING clause is applied to the data. If you were to Add ID to the GROUP BY you would end up with 2 rows, which as I understand it is not what you want, and if you were to add it to the WHERE your MIN, MAX and AVG functions would be affected. So you need to use a subquery. So in this example I would use

SELECT  T.ID, T.Col1, MaxCol2, MinCol2, AvgCol2
FROM    T
        INNER JOIN
        (   SELECT Col1, MAX(Col2) AS MaxCol2, MIN(Col2) AS MinCol2, AVG(Col2) AS AvgCol2
            FROM T
            GROUP BY Col1
        ) T2
            ON T.Col1 = T2.Col1
WHERE   ID = 1 -- OR ID = 2 DEPENDING ON REQUIREMENTS

To apply this to your situation, the database engine has deteremined that the row that will be returned for the columns not in the group by is the row containing ID = 29. So your HAVING Clause is only being applied to this row, the rows with [e-mail], and [e-mail] in have already been removed from the results by the time your HAVING clause is applied. You need to perform the aggregate functions separately to the filtering.

Now I haven't fully got my head around your schema, but I am hoping I have explained the issue of the non deterministic statement well enough that you can make any ammendments required to my attempt at rewriting your query

SELECT  Avg_Price,
        Cnt,
        tyd.id_marchand, 
        tyd.id_product, 
        catalog.price AS c_price, 
        tyd.price AS t_price, 
        tyd.amount AS t_am, 
        pro_tyd.amount AS p_am, 
        pro_tyd.price AS p_price, 
        catalog.img_src,  
        tyd.step, 
        tyd.login AS tyd_l
FROM    Catalog
        INNER JOIN tyd
            ON catalog.id_marchand = tyd.id_marchand 
            AND catalog.id_product = tyd.id_product
        INNER JOIN Pro_tyd
            ON tyd.id_marchand = pro_tyd.id_marchand 
            AND tyd.id_product = pro_tyd.id_product
        INNER JOIN
        (   SELECT  ID_Marchand, ID_Product, Step, AVG(tyd.price) AS avg_price, COUNT(tyd.id_product) AS cnt
            FROM    Tyd
            WHERE   Step = '1'
            GROUP BY ID_Marchand, ID_Product, Step
        ) Agg
            ON Agg.id_marchand = pro_tyd.id_marchand 
            AND Agg.id_product = pro_tyd.id_product
            AND Agg.Step = tyd.Step
WHERE   tyd.Login = '[e-mail]'

这篇关于SQL只获得表中的第一个记录行，我希望全部的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！