如何根据a列的数据将b列的sql搜索结果汇总为sum？

mqxuamgl 于 2021-06-23 发布在 Mysql

关注(0)|答案(4)|浏览(307)

我创建了一个sql公式来搜索特定机器在信号状态上花费的时间：

SELECT 'Core' as Core, Z.EventName, ISNULL(DIFF,0) AS DIFF 
FROM (select distinct MacID, EventName from dbo.tblMachine a  
join (SELECT DISTINCT EVENTNAME FROM dbo.tblEvtDur) 
b on b.EventName <> '' and MacID in ('A06','A07', 'A08', 'A09', 'B01','B05','B09', 'B10', 'B11', 'C04', 'C08', 'C09') ) 
z LEFT JOIN (SELECT A.MacID, A.EventName, SUM(DATEDIFF(SECOND, A.STARTdt, A.eNDdt)) 
as diff,round(SUM(DATEDIFF(SECOND, A.STARTdt, A.eNDdt)) / cast(aVG(Tdiff) 
as decimal(30,8)),4) * 100 AS PER FROM dbo.tblEvtDur  A 
LEFT JOIN ( SELECT macid, SUM(DATEDIFF(SECOND, STARTdt, eNDdt)) as Tdiff 
FROM  dbo.tblEvtDur  WHERE DayID between '20180401' and '20180430'  
GROUP BY MacID ) B ON  A.MacID = B.MacID 
WHERE DayID between '20180401' and '20180430'  
AND A.MacID in
 ('A06','A07', 'A08', 'A09', 'B01','B05','B09', 'B10', 'B11', 'C04', 'C08', 'C09') 
group by A.MacID, A.EventName) a 
ON A.EVENTNAME = Z.EVENTNAME  and Z.MacID  = a.MacID order by Z.MacID, Z.EventName

结果如下：

现在我想让sql显示这些特定机器的eventname blk、mdl、mtl、off、rpr和run的总和。有可能吗？
编辑：
如果我尝试用GROUPBY子句添加sum，它在当前sql上不起作用。
所以我不得不把它重建成：

select 'Core' as Core, EventName, sum(DATEDIFF(second, startdt, EndDT)) as DIFF 
from dbo.tblEvtDur

where MacID in ('A06','A07','A08','A09','B01','B05','B09','B10','B11','C04','C08','C09')

and DayID between '20180531' and '20180531'

group by eventname

但根据当天的情况，某些信号将不会出现，因此不会在结果表中列出。

我应该添加什么来告诉sql将eventname blk显示为0？
编辑2：
到目前为止，他尝试了丹使用right join的建议：

select 'Core' as [Core], a.EventName, isnull((sum(DATEDIFF(second, startdt, EndDT))),0) as DIFF 
from dbo.tblEvtDur a
right join (select distinct eventname from dbo.tblEvtDur where EventName in     
('BLK', 'MDL', 'MTL', 'OFF', 'RPR', 'RUN')) b 
on a.EventName = b.EventName
where MacID in ('A06','A07', 'A08', 'A09', 'B01','B05',
'B09', 'B10', 'B11', 'C04', 'C08', 'C09')
and DayID between '20180531' and '20180531'
group by a.eventname

同样，同样的结果，但所花的时间要长得多。

sql mysql sql-server

来源：https://stackoverflow.com/questions/50601396/how-to-summarize-sql-search-result-of-column-b-as-sum-based-on-data-in-column-a

4条答案

按热度按时间

gcuhipw91#

使用聚合函数（sum、count、avg…）和group by。

select count(*) from (output of your query) group by EventName;

赞(0）回复(0）举报 2021-06-23

gupuwyp22#

由于格式的原因，您的代码有点难读，但正如@piyush ghediya所回答的，您可以将sum与groupby子句一起使用。

SELECT 'Core' as Core, Z.EventName, Sum(ISNULL(DIFF,0)) AS DIFF 
FROM (select distinct MacID, EventName from dbo.tblMachine a  
join (SELECT DISTINCT EVENTNAME FROM dbo.tblEvtDur) 
b on b.EventName <> '' and MacID in ('A06','A07', 'A08', 'A09', 'B01','B05','B09', 'B10', 'B11', 'C04', 'C08', 'C09') ) 
z LEFT JOIN (SELECT A.MacID, A.EventName, SUM(DATEDIFF(SECOND, A.STARTdt, A.eNDdt)) 
as diff,round(SUM(DATEDIFF(SECOND, A.STARTdt, A.eNDdt)) / cast(aVG(Tdiff) 
as decimal(30,8)),4) * 100 AS PER FROM dbo.tblEvtDur  A 
LEFT JOIN ( SELECT macid, SUM(DATEDIFF(SECOND, STARTdt, eNDdt)) as Tdiff 
FROM  dbo.tblEvtDur  WHERE DayID between '20180401' and '20180430'  
GROUP BY MacID ) B ON  A.MacID = B.MacID 
WHERE DayID between '20180401' and '20180430'  
AND A.MacID in
 ('A06','A07', 'A08', 'A09', 'B01','B05','B09', 'B10', 'B11', 'C04', 'C08', 'C09') 
group by A.MacID, A.EventName) a 
ON A.EVENTNAME = Z.EVENTNAME  and Z.MacID  = a.MacID order by Z.MacID, Z.EventName
Group by Core, Eventname

赞(0）回复(0）举报 2021-06-23

kqhtkvqz3#

您需要这样的内容，因为您需要在单个查询中包含sum和condition的所有必需值。

DECLARE @tbl TABLE(name VARCHAR(50), quantity INT, saledate DATE)
INSERT INTO @tbl(name, quantity, saledate)
VALUES('A', 20, '2018-05-31'), ('B', 50, '2018-05-25'), ('c', 40, '2018-05-20'), ('a', 40, '2018-05-10'), ('d', 20, '2018-05-15'), ('c', 20, '2018-05-20')
SELECT a.name, SUM(CASE WHEN a.saledate > '2018-05-15'
  THEN a.quantity ELSE 0 END)
FROM @tbl a
WHERE(a.saledate > '2018-05-15'
  OR a.name in ('a', 'b', 'c', 'd')) GROUP BY a.name

赞(0）回复(0）举报 2021-06-23

c3frrgcw4#

终于找到了解决问题的方法，我所要做的就是从select函数中删除macid并声明eventname：

SELECT 'Core' as Core, Z.EventName, ISNULL(DIFF,0) AS DIFF FROM 
(select distinct EventName from dbo.tblMachine a 

join 
(SELECT DISTINCT EVENTNAME FROM dbo.tblEvtDur) b 
on b.EventName <> '' and eventname in ('BLK', 'MDL', 'MTL', 'OFF', 'RPR', 'RUN') 
and MacID in ('A06','A07', 'A08', 'A09', 'B01','B05','B09', 'B10', 'B11', 'C04', 'C08', 'C09')) Z 

LEFT JOIN (SELECT A.EventName, SUM(DATEDIFF(SECOND, A.STARTdt, A.eNDdt)) as diff, 
round(SUM(DATEDIFF(SECOND, A.STARTdt, A.eNDdt)) / cast(aVG(Tdiff) as decimal(30,8)),4) * 100 AS PER 
FROM dbo.tblEvtDur  A 

LEFT JOIN ( SELECT EVENTNAME, SUM(DATEDIFF(SECOND, STARTdt, eNDdt)) as Tdiff 
FROM dbo.tblEvtDur  WHERE DayID between '20180401' and '20180430' GROUP BY EVENTNAME ) B 
ON  A.EVENTNAME = B.EVENTNAME 

WHERE DayID between '20180401' and '20180430' 
AND A.MacID in ('A06','A07', 'A08', 'A09', 'B01','B05','B09', 'B10', 'B11', 'C04', 'C08', 'C09') 
and A.eventname in ('BLK', 'MDL', 'MTL', 'OFF', 'RPR', 'RUN') 
group by A.EventName) a ON A.EVENTNAME = Z.EVENTNAME and Z.EVENTNAME  = a.EVENTNAME order by  Z.EventName

赞(0）回复(0）举报 2021-06-23

我来回答

如何根据a列的数据将b列的sql搜索结果汇总为sum？

4条答案

相关问题

热门标签

最新问答