如何进行“group by”部分匹配

发布于 2024-11-06 18:15:03 字数 671 浏览 2 评论 0原文

我在 SQL 服务器中有一个表，其中项目代码和子项目代码位于相同的字段中。

结构是这样的

+----+------+-------------+-------+--------+--------+
| id | date | projectcode | debit | credit | budget |
+----+------+-------------+-------+--------+--------+
| 1  | bla  | A100        | bla
| 2  | bla  | A100.01     |
| 3  | bla  | A112        |
| 4  | bla  | A112.02

我如何做这样的选择

SELECT projectcode
  , sum(debit) as debit
  , sum(credit) as credit
  , sum(budget) as budget
FROM table1
GROUP BY -insert-answer-here-

我想要输出 group by A100 和 A100.01 和 A100.x 一起以及 A112 + A112.x

我该怎么做这？

我无法控制表格的结构。

原文

I have a table in SQL-server with projectcodes and sub-project-codes in the same fields.

The stucture is something like this

+----+------+-------------+-------+--------+--------+
| id | date | projectcode | debit | credit | budget |
+----+------+-------------+-------+--------+--------+
| 1  | bla  | A100        | bla
| 2  | bla  | A100.01     |
| 3  | bla  | A112        |
| 4  | bla  | A112.02

How do I do a select like this

SELECT projectcode
  , sum(debit) as debit
  , sum(credit) as credit
  , sum(budget) as budget
FROM table1
GROUP BY -insert-answer-here-

I want the output to group by A100 and A100.01 and A100.x together as well as A112 + A112.x

How do I do this?

I have no control over the structure of the table.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

不如归去 2024-11-13 18:15:04

GROUP BY LEFT(projectcode ,CHARINDEX('.',projectcode  + '.')-1)

GROUP BY LEFT(projectcode ,CHARINDEX('.',projectcode  + '.')-1)

回复收藏 0 原文

我最亲爱的 2024-11-13 18:15:04

也许这会起作用：

SELECT Substring(projectcode, 1, 4) as Project
  , sum(debit) as debit
  , sum(credit) as credit
  , sum(budget) as budget
FROM table1
GROUP BY Substring(projectcode, 1, 4)

Maybe this would work:

SELECT Substring(projectcode, 1, 4) as Project
  , sum(debit) as debit
  , sum(credit) as credit
  , sum(budget) as budget
FROM table1
GROUP BY Substring(projectcode, 1, 4)

回复收藏 0 原文