TSQL group by 生成重复行 [英] TSQL group by generate duplicate row

问题描述

我试图在 2 列中按日期范围(不一定是同一日期)提取所有价格和税费，并按 ID 分组.因为我需要按其他 2 个列进行分组，因为 T-SQL 需要:

I'm trying to extract all prices and taxes by dates range (not necessary the same date) in 2 column and group by ID. Because I need to group by 2 others columns because T-SQL need that:

列..."在选择列表中无效，因为它既不包含在聚合函数中也不包含在 GROUP BY 子句中.

Column '...' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

有时我有重复的用户/ID.(不知道为什么..)

I have a duplicate user/ID sometimes. ( don't know why by the way..)

我有这个 SQL:

WITH myQuery AS 
(
  Select
      c.name, c.id,
      CASE 
      WHEN g.dateCreated BETWEEN CAST ('2016-06-01 00:00:00.000' AS DATETIME) 
           AND CAST ('2017-05-31 23:59:59.000' AS DATETIME) 
      THEN SUM(CAST(g.price AS decimal(20,2) ))
      ELSE 0
      END AS TOTAL_PRICE,
      CASE 
      WHEN g.dateCreated BETWEEN CAST ('2016-01-01 00:00:00.000' AS DATETIME) 
           AND CAST ('2016-12-31 23:59:59.000' AS DATETIME) 
      THEN SUM(CAST(g.tax AS decimal(20,2) ))
      ELSE 0
      END AS TOTAL_TAX
  FROM customers c 
  inner join goodies g
      ON c.id = g.customer_id
  GROUP BY  c.name, c.id, g.dateCreated
)
SELECT count(*) FROM  myQuery

我有 5203 行.我只有 5031 个用户.

I got 5203 rows. I have only 5031 users.

当我分析我的数据时，我有一些重复的数据.

When I Analyse my data, I have some duplicate data.

示例:

  Alex, 12,   0.00,  0.00
  Alex, 12, 100.00, 14.55
 Nancy,  4,   0.00,  0.00
Arthur, 97,  48.14, 09.17

我试图仅按 id 分组，但似乎我不能这样做.

I tried to group by only by id but it seem that I can't do that.

为什么我有重复的数据以及如何防止这种情况发生并确保即使用户不购买商品也有 1 行?

Why I have a duplicate data and How to prevent that and ensure that I have 1 row by USER even if they don't buy goodies?

推荐答案

更正条件聚合并从 group by 中删除 dateCreated:

Correcting your conditional aggregation and removing dateCreated from the group by:

with myQuery as (
select
    c.name
  , c.id
  , total_price = sum(case
      when g.dateCreated >= '20160601' and g.dateCreated < '20170601'
        then cast(g.price as decimal(20,2))
      else 0
      end)
  , total_tax = sum(case
      when g.dateCreated >= '20160101' and g.dateCreated < '20170101'
        then cast(g.tax as decimal(20,2))
      else 0
      end)
from customers c
  left join goodies g
    on c.id = g.customer_id
group by
    c.name
  , c.id
--, g.dateCreated
)
select count(*) from  myQuery;

将 inner join 更改为 left join 将返回 customers 即使他们在 goodies.

Changing the inner join to a left join will return customers even if they have no corresponding row in goodies.

我还更改了您的日期范围代码，以便更明确地说明所包含的内容.

I also changed your date range code to be more explicit about what is included.

参考:

• 不良习惯kick :错误处理日期/范围查询 - Aaron Bertrand
• 做什么<代码>之间和魔鬼有什么共同点?- 亚伦·伯特兰

• Bad habits to kick : mis-handling date / range queries - Aaron Bertrand
• What do between and the devil have in common? - Aaron Bertrand

这篇关于TSQL group by 生成重复行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！