我有一张这样的桌子:
PARTNUMBER | QUANTITY | DESCRIPTION
'foo' 2 'a'
'foo' 2 'a1'
'bar' 2 'b'
'bar' 2 'b'
'bar' 2 'b1'
'bizz' 2 'c'我试图按PARTNUMBER进行分组,按数量进行聚合,并根据最常见的外观进行聚合描述。
我尝试使用子查询来聚合最频繁出现的描述,但我在正确处理它时遇到了一些困难,特别是使用GROUP BY。
以下是我所拥有的:
SELECT SUM(QUANTITY) AS QUANTITY, PARTNNUMBER,
(SELECT TOP(1) [DESCRIPTION]
FROM [PBJobDB].[dbo].[DEVICES]
/*WHERE DESCRIPTION = t1.PARTNO ?? */
GROUP BY [DESCRIPTION], PARTNNUMBER
ORDER BY COUNT([DESCRIPTION]) DESC) as [DESCRIPTION]
FROM `database.table`
GROUP BY PARTNUMBER, [DESCRIPTION]子查询不是由PARTNUMBER获取最频繁的描述,而是在整个表中给出最频繁的描述。
我希望输出结果如下:
PARTNUMBER | QUANTITY | DESCRIPTION
'foo' 4 'a'
'bar' 6 'b'
'bizz' 2 'c'发布于 2020-01-04 08:02:19
我试过了,请检查一下它是否对你有用,
SELECT PARTNUMBER,SUM(QUANTITY) AS QUANTITY,
(
SELECT TOP 1 DESCP FROM
(SELECT [DESCRIPTION]'DESCP',COUNT(*)'CNT'
FROM testtable
WHERE PARTNUMBER = t1.PARTNUMBER
GROUP BY [DESCRIPTION]) A
GROUP BY DESCP,CNT HAVING CNT=MAX(CNT)
)as [DESCRIPTION]
FROM testtable T1
GROUP BY PARTNUMBER发布于 2020-01-04 01:54:02
这就是最后为我工作的..。
select distinct t1.PARTNUMBER , sum(t1.QUANTITY) AS QUANTITY, (
select TOP(1) [DESCRIPTION]
from [PBJobDB].[dbo].[DEVICES] AS t2
where t2.PARTNUMBER = t1.PARTNUMBER
group by [DESCRIPTION]
order by count(*) desc ) as [DESCRIPTION]
from `database.table` AS t1
/* WHERE `column` IS NULL AND `other_column` = 'some_value' */
GROUP BY t1.PARTNUMBER发布于 2020-01-04 03:28:00
你在寻找模式。我将使用两个级别的聚合:
select partnumber, sum(quantity) as total_quantity,
max(case when seqnum = 1 then description end) as description
from (select partnumber, description, sum(quantity) as quantity,
row_number() over (partition by partnumber order by sum(quantity) desc, description) as seqnum
from t
group by partnumber, description
) pd
group by partnumber;https://stackoverflow.com/questions/59587299
复制相似问题