代码之家 › 专栏 › 技术社区 › nnmmss

要联接到由逗号分隔键引用的表的SQL代码,并对其中的数据求和

tsql linq visual-studio sql c#

nnmmss · 技术社区 · 6 年前

我有两张这样的桌子:

InfoTable
-------------------
AVNR    Substation       ColumnTitle     S6_name      AVNRString
-------------------------------------------------------------------
1129     AHWAZ-1        T3+T4              MW          1129,1134
1130     AHWAZ-1        T3+T4              MX          1130,1135
1134     AHWAZ-1        T3+T4              MW          1129,1134
1135     AHWAZ-1        T3+T4              MX          1130,1135
8906     SHOMAL         T9                 MW             8906
8907     SHOMAL         T9                 MX             8907

和

DataTable
------------------------------------------------
Pdate           Ptime     AVNR        Wert
------------------------------------------------
03-13-2017      01:00     1129         12.65
03-13-2017      02:00     1129         25.65
03-13-2017      03:00     1129         102.05
03-12-2017      01:00     1129         12.65
03-14-2017      01:00     1129         12.65
03-16-2017      05:00     1129         12.65    
03-13-2017      01:00     1134         12.65
03-13-2017      02:00     1134         25.65
03-13-2017      03:00     1130         102.05
03-12-2017      01:00     1135         11.15
03-14-2017      01:00     1130         10.00
03-16-2017      01:00     8906         24.5
03-13-2017      01:00     8906         23.5
03-16-2017      02:00     8907         22.5
03-13-2017      01:00     8907         21.5
03-16-2017      05:00     8906         12.5
03-13-2017      02:00     8907         20.5

我想得到这个结果

 ResultTable
-------------------
Substation    ColumnTitle     S6_name   Pdate       pTime   Sum_of_Wert     
-------------------------------------------------------------------
 AHWAZ-1        T3+T4          MW      03-13-2017    01:00   25.3  (sum of Wert for 1129,1134  for that date and time)
 AHWAZ-1        T3+T4          MW      03-13-2017    02:00   51.3  (sum of Wert for 1129,1134  for that date and time)
 AHWAZ-1        T3+T4          MW      03-13-2017    03:00    xxx
 AHWAZ-1        T3+T4          MX      03-14-2017    01:00    xxx
 SHOMAL         T9             MW      03-13-2017    01:00    xxx
 SHOMAL         T9             MW      03-13-2017    02:00    xxx

......

i、 e.我想得到 Substations 在 AvrString 为每个日期和时间。

在SQL中我有可能做到这一点吗?我没有,也许在林肯?我在visual studio中编程过,但速度很慢。我想知道有没有这个主意。

我在visual studio中用c#编写带有sql数据库的程序

3 回复 | 直到 6 年前

StuartLC 6 年前

这里的问题是谁设计的 InfoTable 不遵守正当规则 table normalization 1129,1134 指示其他行中键之间的关系使生活非常困难。

如果您使用的是现代版本的SQL Server-2016或更高版本-您的培根将与 STRING_SPLIT 功能。

这将允许您重新规范化表(即为每个表拆分行 AVNR AVNRString ). 你就可以把裂缝 平均值 回到 INT 为了加入这个并将其应用到一个分组中,即。

WITH normalizeInfoTable AS
(
   SELECT it.Substation, it.ColumnTitle, it.S6_name, CAST(cs.Value as INT) as AVNR
   FROM InfoTable it
   CROSS APPLY STRING_SPLIT (it.AVNRString, ',') cs
)
SELECT it.Substation, it.ColumnTitle, it.S6_name, dt.Pdate, dt.pTime, SUM(dt.Wert) 
  FROM normalizeInfoTable it
  INNER JOIN DataTable dt
  ON it.AVNR = dt.AVNR
  GROUP BY it.Substation, it.ColumnTitle, it.S6_name, dt.Pdate, dt.pTime;

SqlFiddle up here

信息“配对”在您的表中重复,即

AVNR    Substation       ColumnTitle     S6_name      AVNRString
1129     AHWAZ-1        T3+T4              MW          1129,1134
1134     AHWAZ-1        T3+T4              MW          1129,1134

生成4行,因为1129和1134中的每一行将重复两次。如果这不是所需要的,那么可以通过添加 DISTINCT 在CTE中,即。

SELECT DISTINCT it.Substation, it.ColumnTitle, it.S6_name, CAST(cs.Value as INT) as AVNR
...

还要注意的是,适当的规范化仍然是首选的,因为 平均值 可以使用外键强制关系,并使用此列上的索引提高性能。应用 STRING_SPLIT 函数具有性能惩罚。

Wei Lin 6 年前

@斯图尔特的回答太棒了

这是给你的一条路 “使用 distinct 和 like

with CTE AS (
  select distinct [Substation], [ColumnTitle],[S6_name], [AVNRString]
  from InfoTable T1
)
select T2.Substation,T2.ColumnTitle, T2.S6_name,T1.Pdate,T1.pTime,sum(T1.Wert) Sum_of_Wert
from DataTable T1
left join InfoTable T2 on  
  T2.AVNRString = T1.AVNR
  or T2.AVNRString like '%,'+T1.AVNR+'%'
  or T2.AVNRString like '%'+T1.AVNR+',%'
group by T2.Substation,T2.ColumnTitle, T2.S6_name,T1.Pdate,T1.pTime
order by Sum_of_Wert

SQL Fiddle DEMO LINK

附言: 谢谢@dotnetstep

我试着用这个条件脚本来避免它。

   T2.AVNRString = T1.AVNR
      or T2.AVNRString like '%,'+T1.AVNR+'%'
      or T2.AVNRString like '%'+T1.AVNR+',%'

Nerdroid 6 年前

这里有一个查询 STRING_SPLIT

SELECT it.Substation,it.ColumnTitle,it.S6_name,dt.Pdate,dt.pTime, SUM(dt.Wert) AS Sum_of_Wert
FROM InfoTable it
CROSS APPLY (SELECT value FROM STRING_SPLIT(it.AVNRString, ',') ) x
CROSS APPLY (SELECT * FROM DataTable  WHERE AVNR = CAST(x.value as INT) ) dt
GROUP BY it.Substation,it.ColumnTitle,it.S6_name,dt.Pdate,dt.pTime

http://sqlfiddle.com/#!18/e1ed2d/1