代码之家  ›  专栏  ›  技术社区  ›  Jordan Sitkin

SQL groupby和NULL值:如何仅按非NULL值对结果进行分组?

mysql sql
  •  0
  • Jordan Sitkin  · 技术社区  · 16 年前

    我的问题: 

    SELECT *,
    contacts.createdAt AS contactcreatedAt,
    contacts.updatedAt AS contactupdatedAt,
    bidresponses.itemid AS bidresponseitemid,
    bidresponses.personid AS bidresponsepersonid,
    SUM(tagsitems.quantity) AS totalquantity
FROM items
LEFT OUTER JOIN tagsitems ON items.id = tagsitems.itemid
LEFT OUTER JOIN itemscontacts ON items.id = itemscontacts.itemid
LEFT OUTER JOIN contacts ON itemscontacts.contactid = contacts.id
LEFT OUTER JOIN bidresponses ON items.id = bidresponses.itemid AND itemscontacts.personid = bidresponses.personid
LEFT OUTER JOIN bidtemplatefields ON bidresponses.bidtemplatefieldid = bidtemplatefields.id
WHERE ( (items.id = 70687 OR items.id = 70595) AND itemscontacts.relationship = 's' ) AND ( items.deletedAt IS NULL )
GROUP BY items.id, tagsitems.itemid, bidresponses.personid, bidresponses.bidtemplatefieldid
ORDER BY items.id ASC

    没有 分组依据 子句此查询返回所需的结果,减去重要的totalquantity值。

    编辑 : 我应该注意到,我使用分组的唯一原因是为了得到totalquantity值 SUM()和GROUP BY子句: 

    +-------+------------+------+---------------------------+------+------------+-----------+-----------+---------------------+---------------------+-----------+----------+-------+--------+----------+--------+-----------+----------+---------------------+--------------+---------------+--------------+-----------+------------+------+-----------+----------+----------------+-----------------+---------------------+---------------------+-----------+----------+---------------------+---------------------+--------------------+--------+-------------+----------+------+------------------+-------------+---------------------+---------------------+-------------------+---------------------+
| id    | itemtypeid | code | description               | cost | unittypeid | projectid | companyid | createdAt           | updatedAt           | deletedAt | unittype | tagid | itemid | quantity | itemid | contactid | personid | sentdate            | responsedate | bidtemplateid | relationship | awarddate | assigndate | id   | companyid | personid | companyidOwner | parentContactid | createdAt           | updatedAt           | firstName | lastName | company             | email               | bidtemplatefieldid | itemid | bidresponse | personid | id   | bidtemplatefield | fieldtypeid | contactcreatedAt    | contactupdatedAt    | bidresponseitemid | bidresponsepersonid |
+-------+------------+------+---------------------------+------+------------+-----------+-----------+---------------------+---------------------+-----------+----------+-------+--------+----------+--------+-----------+----------+---------------------+--------------+---------------+--------------+-----------+------------+------+-----------+----------+----------------+-----------------+---------------------+---------------------+-----------+----------+---------------------+---------------------+--------------------+--------+-------------+----------+------+------------------+-------------+---------------------+---------------------+-------------------+---------------------+
| 70595 |          1 | NULL | HD Banners                | NULL |       NULL |         7 |         1 | 2010-05-10 17:00:11 | 2010-08-14 18:57:41 | NULL      | each     |  NULL |   NULL |     NULL |  70595 |        16 |    34789 | 2010-08-14 22:37:01 |         NULL |             1 | s            |      NULL |       NULL |   16 |      NULL |     NULL |              1 |            NULL | 2010-08-05 18:40:01 | 2010-08-05 18:41:40 | NULL      | NULL     | sdf                 | 23523@wokd.com      |               NULL |   NULL | NULL        |     NULL | NULL | NULL             |        NULL | 2010-08-05 18:40:01 | 2010-08-05 18:41:40 |              NULL |                NULL |
| 70595 |          1 | NULL | HD Banners                | NULL |       NULL |         7 |         1 | 2010-05-10 17:00:11 | 2010-08-14 18:57:41 | NULL      | each     |  NULL |   NULL |     NULL |  70595 |        22 |    34794 | 2010-08-14 18:44:02 |         NULL |             1 | s            |      NULL |       NULL |   22 |      NULL |    34794 |              1 |            NULL | 2010-08-09 19:56:28 | 2010-08-10 13:55:03 | NULL      | NULL     | anewwwww            | hmm@hmm.com         |               NULL |   NULL | NULL        |     NULL | NULL | NULL             |        NULL | 2010-08-09 19:56:28 | 2010-08-10 13:55:03 |              NULL |                NULL |
| 70595 |          1 | NULL | HD Banners                | NULL |       NULL |         7 |         1 | 2010-05-10 17:00:11 | 2010-08-14 18:57:41 | NULL      | each     |  NULL |   NULL |     NULL |  70595 |        27 |    34797 | 2010-08-14 22:36:59 |         NULL |             1 | s            |      NULL |       NULL |   27 |      NULL |     NULL |              1 |            NULL | 2010-08-10 19:11:52 | NULL                | NULL      | NULL     | 3k3jdjhgj@wrwer.com | 3k3jdjhgj@wrwer.com |               NULL |   NULL | NULL        |     NULL | NULL | NULL             |        NULL | 2010-08-10 19:11:52 | NULL                |              NULL |                NULL |
| 70595 |          1 | NULL | HD Banners                | NULL |       NULL |         7 |         1 | 2010-05-10 17:00:11 | 2010-08-14 18:57:41 | NULL      | each     |  NULL |   NULL |     NULL |  70595 |        28 |    34798 | 2010-08-14 22:37:00 |         NULL |             1 | s            |      NULL |       NULL |   28 |      NULL |     NULL |              1 |            NULL | 2010-08-10 19:18:27 | NULL                | NULL      | NULL     | 3838474@234234.com  | 3838474@234234.com  |               NULL |   NULL | NULL        |     NULL | NULL | NULL             |        NULL | 2010-08-10 19:18:27 | NULL                |              NULL |                NULL |
| 70687 |          1 | NULL | Editing and adding labels | NULL |       NULL |         7 |         1 | 2010-05-15 07:26:33 | 2010-08-14 18:55:48 | NULL      | each     |    12 |  70687 |     NULL |  70687 |        16 |    34789 | 2010-08-14 22:37:01 |         NULL |             1 | s            |      NULL |       NULL |   16 |      NULL |     NULL |              1 |            NULL | 2010-08-05 18:40:01 | 2010-08-05 18:41:40 | NULL      | NULL     | sdf                 | 23523@wokd.com      |               NULL |   NULL | NULL        |     NULL | NULL | NULL             |        NULL | 2010-08-05 18:40:01 | 2010-08-05 18:41:40 |              NULL |                NULL |
| 70687 |          1 | NULL | Editing and adding labels | NULL |       NULL |         7 |         1 | 2010-05-15 07:26:33 | 2010-08-14 18:55:48 | NULL      | each     |     2 |  70687 |     NULL |  70687 |        16 |    34789 | 2010-08-14 22:37:01 |         NULL |             1 | s            |      NULL |       NULL |   16 |      NULL |     NULL |              1 |            NULL | 2010-08-05 18:40:01 | 2010-08-05 18:41:40 | NULL      | NULL     | sdf                 | 23523@wokd.com      |               NULL |   NULL | NULL        |     NULL | NULL | NULL             |        NULL | 2010-08-05 18:40:01 | 2010-08-05 18:41:40 |              NULL |                NULL |
| 70687 |          1 | NULL | Editing and adding labels | NULL |       NULL |         7 |         1 | 2010-05-15 07:26:33 | 2010-08-14 18:55:48 | NULL      | each     |    12 |  70687 |     NULL |  70687 |        27 |    34797 | 2010-08-14 22:36:59 |         NULL |             1 | s            |      NULL |       NULL |   27 |      NULL |     NULL |              1 |            NULL | 2010-08-10 19:11:52 | NULL                | NULL      | NULL     | 3k3jdjhgj@wrwer.com | 3k3jdjhgj@wrwer.com |               NULL |   NULL | NULL        |     NULL | NULL | NULL             |        NULL | 2010-08-10 19:11:52 | NULL                |              NULL |                NULL |
| 70687 |          1 | NULL | Editing and adding labels | NULL |       NULL |         7 |         1 | 2010-05-15 07:26:33 | 2010-08-14 18:55:48 | NULL      | each     |     2 |  70687 |     NULL |  70687 |        27 |    34797 | 2010-08-14 22:36:59 |         NULL |             1 | s            |      NULL |       NULL |   27 |      NULL |     NULL |              1 |            NULL | 2010-08-10 19:11:52 | NULL                | NULL      | NULL     | 3k3jdjhgj@wrwer.com | 3k3jdjhgj@wrwer.com |               NULL |   NULL | NULL        |     NULL | NULL | NULL             |        NULL | 2010-08-10 19:11:52 | NULL                |              NULL |                NULL |
| 70687 |          1 | NULL | Editing and adding labels | NULL |       NULL |         7 |         1 | 2010-05-15 07:26:33 | 2010-08-14 18:55:48 | NULL      | each     |    12 |  70687 |     NULL |  70687 |        28 |    34798 | 2010-08-14 22:37:00 |         NULL |             1 | s            |      NULL |       NULL |   28 |      NULL |     NULL |              1 |            NULL | 2010-08-10 19:18:27 | NULL                | NULL      | NULL     | 3838474@234234.com  | 3838474@234234.com  |               NULL |   NULL | NULL        |     NULL | NULL | NULL             |        NULL | 2010-08-10 19:18:27 | NULL                |              NULL |                NULL |
| 70687 |          1 | NULL | Editing and adding labels | NULL |       NULL |         7 |         1 | 2010-05-15 07:26:33 | 2010-08-14 18:55:48 | NULL      | each     |     2 |  70687 |     NULL |  70687 |        28 |    34798 | 2010-08-14 22:37:00 |         NULL |             1 | s            |      NULL |       NULL |   28 |      NULL |     NULL |              1 |            NULL | 2010-08-10 19:18:27 | NULL                | NULL      | NULL     | 3838474@234234.com  | 3838474@234234.com  |               NULL |   NULL | NULL        |     NULL | NULL | NULL             |        NULL | 2010-08-10 19:18:27 | NULL                |              NULL |                NULL |
+-------+------------+------+---------------------------+------+------------+-----------+-----------+---------------------+---------------------+-----------+----------+-------+--------+----------+--------+-----------+----------+---------------------+--------------+---------------+--------------+-----------+------------+------+-----------+----------+----------------+-----------------+---------------------+---------------------+-----------+----------+---------------------+---------------------+--------------------+--------+-------------+----------+------+------------------+-------------+---------------------+---------------------+-------------------+---------------------+

    具有 SUM()和GROUP BY子句: 

    +-------+------------+------+---------------------------+------+------------+-----------+-----------+---------------------+---------------------+-----------+----------+-------+--------+----------+--------+-----------+----------+---------------------+--------------+---------------+--------------+-----------+------------+------+-----------+----------+----------------+-----------------+---------------------+---------------------+-----------+----------+---------+----------------+--------------------+--------+-------------+----------+------+------------------+-------------+---------------------+---------------------+-------------------+---------------------+---------------+
| id    | itemtypeid | code | description               | cost | unittypeid | projectid | companyid | createdAt           | updatedAt           | deletedAt | unittype | tagid | itemid | quantity | itemid | contactid | personid | sentdate            | responsedate | bidtemplateid | relationship | awarddate | assigndate | id   | companyid | personid | companyidOwner | parentContactid | createdAt           | updatedAt           | firstName | lastName | company | email          | bidtemplatefieldid | itemid | bidresponse | personid | id   | bidtemplatefield | fieldtypeid | contactcreatedAt    | contactupdatedAt    | bidresponseitemid | bidresponsepersonid | totalquantity |
+-------+------------+------+---------------------------+------+------------+-----------+-----------+---------------------+---------------------+-----------+----------+-------+--------+----------+--------+-----------+----------+---------------------+--------------+---------------+--------------+-----------+------------+------+-----------+----------+----------------+-----------------+---------------------+---------------------+-----------+----------+---------+----------------+--------------------+--------+-------------+----------+------+------------------+-------------+---------------------+---------------------+-------------------+---------------------+---------------+
| 70595 |          1 | NULL | HD Banners                | NULL |       NULL |         7 |         1 | 2010-05-10 17:00:11 | 2010-08-14 18:57:41 | NULL      | each     |  NULL |   NULL |     NULL |  70595 |        16 |    34789 | 2010-08-14 22:37:01 |         NULL |             1 | s            |      NULL |       NULL |   16 |      NULL |     NULL |              1 |            NULL | 2010-08-05 18:40:01 | 2010-08-05 18:41:40 | NULL      | NULL     | sdf     | 23523@wokd.com |               NULL |   NULL | NULL        |     NULL | NULL | NULL             |        NULL | 2010-08-05 18:40:01 | 2010-08-05 18:41:40 |              NULL |                NULL |          NULL |
| 70687 |          1 | NULL | Editing and adding labels | NULL |       NULL |         7 |         1 | 2010-05-15 07:26:33 | 2010-08-14 18:55:48 | NULL      | each     |    12 |  70687 |     NULL |  70687 |        16 |    34789 | 2010-08-14 22:37:01 |         NULL |             1 | s            |      NULL |       NULL |   16 |      NULL |     NULL |              1 |            NULL | 2010-08-05 18:40:01 | 2010-08-05 18:41:40 | NULL      | NULL     | sdf     | 23523@wokd.com |               NULL |   NULL | NULL        |     NULL | NULL | NULL             |        NULL | 2010-08-05 18:40:01 | 2010-08-05 18:41:40 |              NULL |                NULL |          NULL |
+-------+------------+------+---------------------------+------+------------+-----------+-----------+---------------------+---------------------+-----------+----------+-------+--------+----------+--------+-----------+----------+---------------------+--------------+---------------+--------------+-----------+------------+------+-----------+----------+----------------+-----------------+---------------------+---------------------+-----------+----------+---------+----------------+--------------------+--------+-------------+----------+------+------------------+-------------+---------------------+---------------------+-------------------+---------------------+---------------+
    2 回复  |  直到 16 年前
        1
  •  1
  •   bbadour    16 年前

    您需要在GROUPBY子句中包含所有重要的未聚合列。现在,您不需要按createdAt等进行分组。

        2
  •  0
  •   Thomas    16 年前

    如果你需要这些排 SUM() 因此需要 GROUP BY

    最好的解决方案可能是在处理/循环处理客户机代码/脚本中的所有行时将数量相加。

    和 总和() 以获得每个 items.id 但是,即使在客户机代码的所有行中执行额外的循环,也可能比查询两次快,这还可能取决于结果的大小。

    推荐文章
    Johnny T  ·  基于当前值的SQL合并表[重复]
    1 年前
    Shukurullox Komiljonov  ·  从记录中获得相互和解。使用SQL
    1 年前
    John D  ·  需要为NULL或NOT NULL的WHERE子句
    1 年前
    ojek  ·  如何对SQL结果进行分组和编号?
    1 年前
    senek  ·  如何在PL/SQL中将选择结果(列)放入数组中
    1 年前
    Sax  ·  规范化Google表格(第一步)
    1 年前
    Barış Uşaklı  ·  如何在ON冲突更新中使用CTE中的值
    1 年前
    Jatin  ·  检索卷计数的动态sql抛出错误语法错误[关闭]
    1 年前
    Andrus  ·  如何在sql中查找第二个匹配项
    1 年前
    Nebula Tech  ·  在SQL Server中搜索字符串中单词的多次出现
    1 年前
    关于   移动版
    代码之家 - 一站式码农服务社区
    沪ICP备11025650号