我该如何做'分组比赛' [英] How do I do `group by` partial match

问题描述

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

结构是这样的

  + ---- + ------ + ------------- + ------- + -------- + -------- + 
 | id |日期| projectcode |借方|信贷|预算| 
 + ---- + ------ + ------------- + ------- + -------- +  - ------ + 
 | 1 | bla | A100 | bla 
 | 2 | bla | A100.01 | 
 | 3 | bla | A112 | 
 | 4 | bla | A112.02 
  

如何做这样的选择

  SELECT项目代码
，总和（借方）为借方
，总额（贷方）为贷方
，总和（预算）为预算
 FROM table1 
 GROUP BY -insert-answer-here- 
  

我想要输出到组，由 A100和A100.01以及A100.x一起以及A112 + A112.x

我如何做到这一点？

我无法控制表格的结构。

解决方案

  GROUP BY LEFT（projectcode，CHARINDEX（'。'，projectcode +'。'） -  1）
  




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.

解决方案

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

这篇关于我该如何做'分组比赛'的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！