如何根据群体来统计: [英] How to get count on the basis of group :
本文介绍了如何根据群体来统计:的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
大家好,我有一个包含多条记录的数据库。请参阅下面的表结构:
id部门产品
1 电子相机&视频
2 电子相机&视频
3 电子手机&配件
4 电子手机&配件
5 电子手机&配件
6 电子计算机,平板电脑&笔记本电脑
7 电子计算机,平板电脑&笔记本电脑
8 时尚服饰&配饰
9 时尚服饰&配饰
10 时尚鞋
11 时尚鞋
10 收藏品&艺术古董
11 收藏品&艺术古董
10 收藏品&艺术古董
11 收藏品&艺术艺术
11 收藏品&艺术艺术
现在我需要一个根据部门显示产品数量的查询..见下文。
部门产品
电子 3 (表示它有 3 产品)
时尚 2 (表示它 2 产品)
Collectibles& Art 2 (表示它有 2 产品(古董,艺术))
任何人都有一个想法,请建议我。
解决方案
>
试试这个
DECLARE @ ProductDtls TABLE (id INT ,Department VARCHAR ( 30 ),Product VARCHAR ( 100 ))
INSERT INTO @ ProductDtls (ID,部门,产品)
VALUES ( 1 ,' Electronics',' Cameras&视频'),
( 2 ,' Electronics',' Cameras& Video'),
( 3 ,' Electronics', ' 手机和配件'),
( 4 ,' Electronics', ' 手机和配件'),
( 5 ,' Electronics',' 手机和配件'),
( 6 ,' Electronics',' Computers,Tablets&笔记本电脑的),
( 7 ,' Electronics',' 计算机,平板电脑和笔记本电脑的),
( 8 ,' Fashion',' 服装与配饰'),
( 9 ,' Fashion',' 服装与配饰'),
( 10 ,' Fashion',' 鞋子),
( 11 ,' < span class =code-string> Fashion',' Shoes'),
( 10 ,< span class =code-string>' Collectibles& Art',' 古董'),
( 11 ,' Collectibles& Art',' 古董'),
( 10 , ' Collectibles& Art',' 古董'),
( 11 ,' Collectibles& Art',' Art '),
( 12 ,' 收藏品和艺术品, 艺术品)
SELECT 部门,COUNT(T.Prod uct)' ProductCount'
FROM ( SELECT DISTINCT 部门,产品
FROM @ ProductDtls )T
GROUP BY 部门
尝试下面的T-Sql代码:
< pre lang =SQL> 创建 table #temp
(
id < span class =code-keyword> int identity ,
department varchar ( 50 ),
product varchar ( 50 )
)
插入 进入 #temp values (' Electronics', ' Cameras&视频'),(' Electronics',' Cameras& Video'),(' Electronics',' 手机和配件')
,(' Electronics',' < span class =code-string>计算机,平板电脑和笔记本电脑的),(' Fashion' ,' Shoes'),(' 时尚',' 服装与配饰'),(' Fashion', ' 服装&附件')
选择部门,COUNT(不同产品)
来自 #temp
group by 部门
drop table #temp
Hi All, I have a database with multiple records. See Table Structure Below :
id Department Product
1 Electronics Cameras & Video
2 Electronics Cameras & Video
3 Electronics Cell Phones & Accessories
4 Electronics Cell Phones & Accessories
5 Electronics Cell Phones & Accessories
6 Electronics Computers, Tablets & Laptops
7 Electronics Computers, Tablets & Laptops
8 Fashion Clothing & Accessories
9 Fashion Clothing & Accessories
10 Fashion Shoes
11 Fashion Shoes
10 Collectibles & Art Antiques
11 Collectibles & Art Antiques
10 Collectibles & Art Antiques
11 Collectibles & Art Art
11 Collectibles & Art Art
Now I needs a query which shows product count as per the department.. See below.
Department Product
Electronics 3 (means it has 3 product)
Fashion 2 (means it has 2 product)
Collectibles & Art 2 (means it has 2 product (Antiques,Art))
Anyone have an idea kindly suggest me.
解决方案
Hi,
Try This
DECLARE @ProductDtls TABLE(id INT, Department VARCHAR(30),Product VARCHAR(100)) INSERT INTO @ProductDtls (ID, Department, Product) VALUES(1,'Electronics', 'Cameras & Video'), (2,'Electronics','Cameras & Video'), (3,'Electronics','Cell Phones & Accessories'), (4,'Electronics','Cell Phones & Accessories'), (5,'Electronics','Cell Phones & Accessories'), (6,'Electronics','Computers, Tablets & Laptops'), (7,'Electronics','Computers, Tablets & Laptops'), (8,'Fashion','Clothing & Accessories'), (9,'Fashion','Clothing & Accessories'), (10,'Fashion','Shoes'), (11,'Fashion','Shoes'), (10,'Collectibles & Art','Antiques'), (11,'Collectibles & Art','Antiques'), (10,'Collectibles & Art','Antiques'), (11,'Collectibles & Art','Art'), (12,'Collectibles & Art','Art') SELECT Department, COUNT(T.Product) 'ProductCount' FROM (SELECT DISTINCT Department, Product FROM @ProductDtls) T GROUP BY Department
try below T-Sql Code :
create table #temp ( id int identity, department varchar(50), product varchar(50) ) insert into #temp values ('Electronics','Cameras & Video'),('Electronics','Cameras & Video'),('Electronics','Cell Phones & Accessories') ,('Electronics','Computers, Tablets & Laptops'),('Fashion','Shoes'),('Fashion','Clothing & Accessories'),('Fashion','Clothing & Accessories') select department,COUNT(distinct product) from #temp group by department drop table #temp
这篇关于如何根据群体来统计:的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文