将SQL中的数据与列中的数据合并

Question

我有包含数据的临时表

    count1  serverinfo  csdversion  extend  servicepackdate piecolor
26  Microsoft Windows 8.1 Entreprise        NULL    2023-07-11  green
1   Microsoft® Windows Vista™ Enterprise        NULL    2010-04-13  black
2   Microsoft® Windows Vista™ Entreprise        NULL    2010-04-13  black
31698   Microsoft Windows 7 Enterprise  Service Pack 1  NULL    2020-04-13  green
9918    Microsoft Windows 7 Entreprise  Service Pack 1  NULL    2020-04-13  green

请查找快照

我需要合并微软windows 7企业版和微软windows 7企业版，你能告诉我怎么做吗？

​

​

主查询数据来自SCCM表

    declare @variable varchar
create table #temptable(count1 int,serverinfo varchar(max),csdversion varchar(max),extend date,servicepackdate date,piecolor varchar(max))
insert into #temptable(count1,serverinfo,csdversion,extend,servicepackdate,piecolor)
select count(distinct OS.resourceid) as ModelCount,serverinfo,dt.Servicepack,dt.extendedsupportEnddate,dt.servicepacksupportenddate,
case
when DATEDIFF(month,getdate(), dt.servicepacksupportenddate) < 0 then 'black'
when DATEDIFF(month,getdate(),dt.servicepacksupportenddate) > 0 and DATEDIFF(month, getdate(),dt.servicepacksupportenddate) < 6  then 'red'
when DATEDIFF(month,getdate(),dt.servicepacksupportenddate) > 6 and DATEDIFF(month, getdate(),dt.servicepacksupportenddate) < 12  then 'orange'
when DATEDIFF(month,getdate(),dt.servicepacksupportenddate) > 12  then 'green'
ELSE ''
End As PieColor 
 from [dbo].[v_GS_OPERATING_SYSTEM] OS
inner join datesinfo dt on  os.caption0=dt.serverinfo where os.caption0 not like '%Server%' and  os.caption0 not like '%Windows 10%'
group by dt.Servicepack,serverinfo,dt.servicepacksupportenddate,dt.extendedsupportenddate



select * from #temptable

sccm表中的数据是错误的，为了纠正，我们必须将企业和企业等同起来

datesinfo表中的数据

serverinfo  LifecyclestartDate  ExtendedSupportEnddate  servicepacksupportenddate   servicepack
Microsoft Windows Server 2008 R2 Enterprise 2009-10-22  NULL    2020-01-14  Service Pack 1
Microsoft Windows Server 2008 R2 Standard   2009-10-22  NULL    2020-01-14  Service Pack 1
Microsoft® Windows Server® 2008 Standard    2008-05-06  NULL    2011-04-09  Service Pack 1
Microsoft® Windows Server® 2008 Enterprise  2008-05-06  NULL    2020-01-14  Service Pack 2
Microsoft® Windows Server® 2008 Standard    2008-05-06  NULL    2020-01-14  Service Pack 2
Microsoft Windows Server 2012 Standard  2012-10-30  NULL    2023-10-10  Service Pack 2
Microsoft Windows Server 2012 R2 Standard   2012-10-30  NULL    2023-10-10  Service Pack 2
Microsoft Windows Server 2012 Datacenter    2012-10-30  2018-10-09  2023-10-10  
Microsoft Windows Server 2012 R2 Datacenter 2012-10-30  2018-10-09  2023-10-10  
Microsoft Windows Server 2016 Standard  2016-10-15  2022-01-11  2027-01-12  
Microsoft Windows Server 2016 Datacenter    2016-10-15  2022-01-11  2027-01-12  
Microsoft® Windows Vista™ Enterprise    2007-01-25  NULL    2010-04-13  
Microsoft Windows 7 Enterprise  2007-01-25  NULL    2020-04-13  Service Pack 1
Microsoft Windows 8.1 Entreprise    2013-11-13  NULL    2023-07-11  
Microsoft® Windows Vista™ Entreprise    2007-01-25  NULL    2010-04-13  
Microsoft Windows 7 Entreprise  2007-01-25  NULL    2020-04-13  Service Pack 1
Microsoft Windows 8.1 Entreprise    2013-11-13  NULL    2023-07-11  
Microsoft Windows 10 Enterprise 1900-01-01  1900-01-01  2019-10-08  15063
Microsoft Windows 10 Entreprise 1900-01-01  1900-01-01  2019-10-08  15063
Microsoft Windows 10 Entreprise 1900-01-01  1900-01-01  2020-04-14  16299
Microsoft Windows 10 Entreprise 1900-01-01  1900-01-01  2020-11-10  17134
Microsoft Windows 10 Enterprise 1900-01-01  1900-01-01  2020-11-10  17134
Microsoft Windows 10 Enterprise 1900-01-01  1900-01-01  2021-05-10  17763
Microsoft Windows 10 Entreprise 1900-01-01  1900-01-01  2021-05-10  17763    

Answer 1

使用您的查询和GROUP BY serverinfo。在其余的列中使用聚合函数:例如count1 (Csdversion)，如果您确定所有的the版本都是相同的，那么就使用max(csdversion)。

Answer 2

尝试一个GROUP BY查询，取计数的和，并按所有其他列进行聚合：

SELECT
    SUM(count1) AS count1,
    serverinfo,
    csdversion,
    extend,
    servicepackdate,
    piecolor
FROM #temp_table
GROUP BY
    serverinfo,
    csdversion,
    extend,
    servicepackdate,
    piecolor;