MySQL GROUP BY和COUNT [英] MySQL GROUP BY and COUNT

问题描述

在对mysql表中的某些元素进行分组之后，我对计数有一个小问题， 我有一个订单表..，其中每个订单都有几行，并按一个代码分组(命名为codcomanda).我必须执行一个查询，该查询对每个客户的订单数进行计数，并仅列出名称和订单数.

I have a small problem regarding a count after grouping some elements from a mysql table, I have an orders table .. in which each order has several rows grouped by a code (named as codcomanda) ... I have to do a query which counts the number of orders per customer and lists only the name and number of orders.

这就是我的想法(这可能很愚蠢……我不是专业程序员)

This is what i came up (this might be dumb ... i'm not a pro programmer)

SELECT a.nume, a.tel, (
    SELECT COUNT(*) AS `count` 
      FROM (
        SELECT id AS `lwtemp` 
          FROM lw_comenzi_confirmate AS yt 
         WHERE status=1 AND yt.tel LIKE **a.tel** 
         GROUP BY yt.codcomanda
      ) AS b
  ) AS numar_comenzi 
  FROM lw_comenzi_confirmate AS a 
 WHERE status=1 
 GROUP BY tel;

nume = NAME
tel = PHONE(由于没有登录系统，因此是客户端的唯一标识符)

nume = NAME
tel = PHONE (which is the distinct identifier for clients since there's no login system)

上述查询的问题在于，我不知道如何将 a.tel 与选择了第一个选择的匹配.如果我用数据库中的数字替换它，那么它可以工作....

The problem with the above query is that I don't know how to match the a.tel with the one on which the first select is on. If I replace it with a number that is in the db it works....

有人可以帮助我一个如何引用该变量的人吗? 或者也许是另一种解决方案?

Can anyone help me one how to refer to that var? or maybe another solution on how to get this done?

如果需要更多信息，我将尽快提供.

If any more info is needed I`ll provide asap.

推荐答案

如果我对您的架构的理解有误，请纠正我:

Please, correct me if I'm wrong in my understanding of your schema:

• lw_comenzi_confirmate包含客户的nume和tel；
• lw_comenzi_confirmate包含订单明细(同一表)；
• 一个订单在lw_comenzi_confirmate表中可以有多个条目，订单由codcomanda字段区分.

• lw_comenzi_confirmate contains nume and tel of the customer;
• lw_comenzi_confirmate contains order details (same table);
• one order can have several entries in the lw_comenzi_confirmate table, order is distinguished by codcomanda field.

首先，我高度建议阅读有关规范化并修复您的问题的信息.数据库设计.

First, I highly recommend reading about Normalisation and fixing your database design.

以下应为您完成这项工作:

The following should do the job for you:

SELECT nume, tel, count(DISTINCT codcomanda) AS cnt
  FROM lw_comenzi_confirmate
 WHERE status = 1
 GROUP BY nume, tel
 ORDER BY nume, tel;

您可以在 SQL Fiddle 上测试此查询.

You can test this query on SQL Fiddle.

这篇关于MySQL GROUP BY和COUNT的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！