如何在sql中使用GroupBy? [英] How to Use GroupBy in sql?
本文介绍了如何在sql中使用GroupBy?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有这样的表格
Id ProductName MainCategory AdditionalCategory size
1 美国烤咖啡 10
2 美国烤咖啡 12
3 美国烤咖啡 14
4 美国烤咖啡 16
< span class =code-digit> 5 美国烤咖啡夏威夷 10
6 美国烤咖啡夏威夷 12
7 美国烤咖啡夏威夷 14 跨度>
8 美国烤咖啡夏威夷 16
9 美国烤咖啡Kona 10
10 美国烤咖啡Kona 12
11 American Roast Coffee Kona 14
12 American Roast Coffee Kona 16
我在这个表中逐个使用
选择 Productname,Maincategory,AdditionalCategory1 来自 testproducts
group by Productname,Main category,AdditionalCategory1
我得到了这样的结果
ProductName MainCategory AdditionalCategory
美国烤咖啡
美国烤咖啡夏威夷
美国烤咖啡Kona
但是我想要结果
Id ProductName MainCategory AdditionalCategory尺寸
1美国烤咖啡10
5美国烤咖啡夏威夷10
9美国烤咖啡Kona 10
如有可能请给我代码
谢谢Advance
解决方案
如果你想要大小,然后你应该在查询中包括列:)
由于你似乎只想要最小的大小值,那么你必须使用MIN()函数。
<前l ang =sql> SELECT Productname,Maincategory,AdditionalCategory,MIN(Size) FROM testproducts
GROUP BY Productname,Maincategory,AdditionalCategory
希望这有帮助。
试试这个:
DECLARE @ tmp AS TABLE (Id INT IDENTITY ( 1 , 1 ),ProductName VARCHAR ( 30 ),MainCategory VARCHAR ( 30 ),AdditionalCategory VARCHAR ( 30 ),大小 INT )
INSERT INTO @ tmp (ProductName,MainCategory,AdditionalCategory,size)
VALUES (' American Roast',' 咖啡', NULL , 10 ),
( ' American Roast',' Coffee', NULL , 12 ),
(' American Roast',' Coffee', NULL , 14 ) ,
(' American Roast',' Coffee', NULL , 16 ),
(' American Roast',' < span class =code-string> Coffee',' Hawaiian', 10 ),
(' American Roast',' 咖啡',' Hawaiian', 12 ),
(' American Roast',' Coffee',' Hawaiian', 14 ),
(' American Roast',' Coffee',' Hawaiian', 16 ) ,
(' American Roast',' Coffee',' Kona', 10 ),
(' American Roast',' Coffee', ' Kona', 12 ),
(' American Roast',' Coffee',' Kona', 14 ),
(' American Roast',' Coffee', ' Kona', 16 )
SELECT t2.Id,t1.ProductName,t1.MainCategory,t1.AdditionalCategory,t1.size
FROM (
SELECT ProductName,MainCategory,AdditionalCategory,MIN(size) AS 大小
FROM @ tmp
GROUP BY ProductName,MainCategory,AdditionalCat egory
) AS t1 LEFT JOIN @ tmp AS t2 ON t1 .ProductName = t2.ProductName AND t1.MainCategory = t2.MainCategory
AND COALESCE (t1.AdditionalCategory,' - ')= COALESCE (t2.AdditionalCategory,' - ') AND t1.size = t2.size
结果:
1美国烤咖啡NULL 10
5美国烤咖啡夏威夷10
9美国烤咖啡Kona 10
您不能将Id
与MIN()一起添加
函数,因为它强制将Id
添加到GROUP BY
列表。实现预期输出的唯一方法是使用子查询。
为sol1添加了ID。
选择Min(id), ProductName,MainCategory,AdditionalCategory,MIN(size)
来自testproducts
按产品名称分类,MainCategory,AdditionalCategory
Hi,
I have table like this
Id ProductName MainCategory AdditionalCategory size
1 American Roast Coffee 10
2 American Roast Coffee 12
3 American Roast Coffee 14
4 American Roast Coffee 16
5 American Roast Coffee Hawaiian 10
6 American Roast Coffee Hawaiian 12
7 American Roast Coffee Hawaiian 14
8 American Roast Coffee Hawaiian 16
9 American Roast Coffee Kona 10
10 American Roast Coffee Kona 12
11 American Roast Coffee Kona 14
12 American Roast Coffee Kona 16
I have use group by class in this table
select Productname,Maincategory,AdditionalCategory1 from testproducts
group by Productname,Maincategory,AdditionalCategory1
I got Result like this
ProductName MainCategory AdditionalCategory
American Roast Coffee
American Roast Coffee Hawaiian
American Roast Coffee Kona
But I want Result
Id ProductName MainCategory AdditionalCategory Size
1 American Roast Coffee 10
5 American Roast Coffee Hawaiian 10
9 American Roast Coffee Kona 10
If any possible please give me code
Thanks Advance
解决方案
If you want the size, then you should include the column in your query :)
And since you seem to only want the minimum size value, then you have to use the MIN() function.
SELECT Productname, Maincategory, AdditionalCategory, MIN(Size) FROM testproducts GROUP BY Productname, Maincategory, AdditionalCategory
Hope this helps.
Try this:
DECLARE @tmp AS TABLE (Id INT IDENTITY(1,1), ProductName VARCHAR(30), MainCategory VARCHAR(30), AdditionalCategory VARCHAR(30), size INT) INSERT INTO @tmp (ProductName, MainCategory, AdditionalCategory, size) VALUES('American Roast', 'Coffee', NULL, 10), ('American Roast', 'Coffee', NULL, 12), ('American Roast', 'Coffee', NULL, 14), ('American Roast', 'Coffee', NULL, 16), ('American Roast', 'Coffee', 'Hawaiian', 10), ('American Roast', 'Coffee', 'Hawaiian', 12), ('American Roast', 'Coffee', 'Hawaiian', 14), ('American Roast', 'Coffee', 'Hawaiian', 16), ('American Roast', 'Coffee', 'Kona', 10), ('American Roast', 'Coffee', 'Kona', 12), ('American Roast', 'Coffee', 'Kona', 14), ('American Roast', 'Coffee', 'Kona', 16) SELECT t2.Id, t1.ProductName, t1.MainCategory, t1.AdditionalCategory, t1.size FROM ( SELECT ProductName, MainCategory, AdditionalCategory, MIN(size) AS size FROM @tmp GROUP BY ProductName, MainCategory, AdditionalCategory ) AS t1 LEFT JOIN @tmp AS t2 ON t1.ProductName = t2.ProductName AND t1.MainCategory = t2.MainCategory AND COALESCE(t1.AdditionalCategory, '-') = COALESCE(t2.AdditionalCategory,'-') AND t1.size = t2.size
Result:
1 American Roast Coffee NULL 10 5 American Roast Coffee Hawaiian 10 9 American Roast Coffee Kona 10
You can't addId
together withMIN()
function, because it enforces to addId
toGROUP BY
list. The only way to achieve expected output is to use subquery.
Added Id to sol1 .
Select Min(id), ProductName,MainCategory,AdditionalCategory,MIN(size)
from testproducts
Group by ProductName,MainCategory,AdditionalCategory
这篇关于如何在sql中使用GroupBy?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文