SQL计数问题 [英] SQL Counting Problem
本文介绍了SQL计数问题的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
大家好。我在计算粉丝时遇到问题。
所以这就是它的方式。对于每对用户,我需要计算他们关注的用户数。所以我有表user_inf:
插入 进入 user_inf 值(' Andreas',' Martiou 25',' 1990-12-02');
插入 进入 user_inf 值 (' Aris',' Papandreou 10',' 1987-03-12');
插入 进入 user_inf 值 (' Anna',' Aiakidon 20',' 1989-07-15');
插入 进入 user_inf 值 (' Dimitris',' Dodonis 3',' 1992-09-07');
插入 进入 user_inf 值 (' Katerina',' 28 Oktobriou 4',' 1993-01-09'跨度>);
插入 进入 user_inf 值 (' Kostas',' Kasioumi 3',' 1992-12-12');
插入 进入 user_inf 值 (' Maria',' Kalari 8',' 1993-08-31');
插入 进入 user_inf 值 (' Petros',' Panepistimiou 9',' 1992-04-15');
插入 进入 user_inf 值 (' Panos',' Aneksartisias 13',' 1991-05-27');
插入 进入 user_inf 值 (' Yannis',' Ithakis 20',' 1993-07-03');
,表格如下:
插入 进入跟随值(' Andreas',' 卡特里娜'跨度>);
插入 进入跟随值 (' Aris',' 安德烈亚斯跨度>);
插入 进入跟随值 (' Aris',' 安娜'跨度>);
插入 进入跟随值 (' Aris',' 科斯塔斯跨度>);
插入 进入跟随值 (' Aris',' 帕诺斯跨度>);
插入 进入跟随值 (' Aris',' 雅尼斯跨度>);
插入 进入跟随值 (' Anna',' 阿里斯跨度>);
插入 进入跟随值 (' Anna',' Maria的跨度>);
插入 进入跟随值 (' Anna',' Panos');
插入 进入跟随值 (' Dimitris',' Maria的跨度>);
插入 进入跟随值 (' Dimitris',' 安娜'跨度>);
插入 进入跟随值 (' Kostas',' 安德烈亚斯跨度>);
插入 进入跟随值 (' Kostas',' 帕诺斯跨度>);
插入 进入跟随值 (' Kostas',' 卡特里娜'跨度>);
插入 进入跟随值 (' Maria',' 雅尼斯跨度>);
插入 进入跟随值 (' Maria',' 科斯塔斯跨度>);
插入 进入跟随值 (' Maria',' 安娜'跨度>);
插入 进入跟随值 (' Maria',' 阿里斯跨度>);
插入 进入跟随值 (' Maria',' 帕诺斯跨度>);
插入 进入跟随值 (' Panos',' 安德烈亚斯跨度>);
插入 进入跟随值 (' Panos',' 阿里斯跨度>);
插入 进入跟随值 (' Petros',' 安德烈亚斯跨度>);
插入 进入跟随值 (' Yannis',' 阿里斯跨度>);
插入 进入跟随值 (' Yannis',' Andreas');
例如第二对(Aris,Andreas) ,阿里斯跟随5个人,安德烈亚斯跟随1个学生。这两个的数量是6.就像这样。我的代码如下。
select count(us1.user1_name),count(us2.user2_name)
来自,后跟 as us1
inner join 跟随 as us2 on us1.user1_name = us2.user2_name
其中 us1 .user1_name = us2.user2_name;
解决方案
如果你想获得用户名和计算他们的学生,请使用:
SELECT u.Name,COUNT(f.name) AS CountOfPupils
FROM user_inf AS u LEFT JOIN 跟在 AS f ON u.Name = f.user_name
要获得用户名及其学生的数量,您需要加总:
SELECT COUNT(名称) AS CountOfNames,SUM(CountOfPupils) AS CountOfPupils
FROM (
SELECT u.Name,COUNT(f。 name) AS CountOfPupils
FROM user_inf AS u LEFT JOIN 跟随 AS f ON u.Name = f.user_name
) AS T
Hello everyone. I have a problem in counting followers.
So this is how it goes. For each pair of users, i need to count the users that they follow. So i have the table user_inf:
insert into user_inf values('Andreas', 'Martiou 25', '1990-12-02');
insert into user_inf values('Aris', 'Papandreou 10', '1987-03-12');
insert into user_inf values('Anna', 'Aiakidon 20', '1989-07-15');
insert into user_inf values('Dimitris', 'Dodonis 3', '1992-09-07');
insert into user_inf values('Katerina', '28 Oktobriou 4', '1993-01-09');
insert into user_inf values('Kostas','Kasioumi 3', '1992-12-12');
insert into user_inf values('Maria', 'Kalari 8', '1993-08-31');
insert into user_inf values('Petros', 'Panepistimiou 9', '1992-04-15');
insert into user_inf values('Panos', 'Aneksartisias 13', '1991-05-27');
insert into user_inf values('Yannis', 'Ithakis 20', '1993-07-03');
and the table follows:
insert into follows values('Andreas', 'Katerina');
insert into follows values('Aris', 'Andreas');
insert into follows values('Aris', 'Anna');
insert into follows values('Aris', 'Kostas');
insert into follows values('Aris', 'Panos');
insert into follows values('Aris', 'Yannis');
insert into follows values('Anna' ,'Aris');
insert into follows values('Anna', 'Maria');
insert into follows values('Anna', 'Panos' );
insert into follows values('Dimitris', 'Maria');
insert into follows values('Dimitris', 'Anna');
insert into follows values('Kostas' ,'Andreas');
insert into follows values('Kostas', 'Panos');
insert into follows values('Kostas', 'Katerina');
insert into follows values('Maria', 'Yannis');
insert into follows values('Maria', 'Kostas');
insert into follows values('Maria', 'Anna');
insert into follows values('Maria', 'Aris');
insert into follows values('Maria', 'Panos');
insert into follows values('Panos','Andreas');
insert into follows values('Panos', 'Aris');
insert into follows values('Petros', 'Andreas');
insert into follows values('Yannis', 'Aris');
insert into follows values('Yannis', 'Andreas');
For example for the second pair(Aris,Andreas), Aris follows 5 people and Andreas follows 1 pupil. The count of these two is 6. And goes like this.My code is the following.
select count(us1.user1_name) , count(us2.user2_name)
from follows as us1
inner join follows as us2 on us1.user1_name = us2.user2_name
where us1.user1_name = us2.user2_name ;
解决方案
If you want to get user names and the count their pupils, use this:
SELECT u.Name, COUNT(f.name) AS CountOfPupils FROM user_inf AS u LEFT JOIN follows AS f ON u.Name=f.user_name
To get the count of user names and their pupils, you need to sum it:
SELECT COUNT(Name) AS CountOfNames, SUM(CountOfPupils) AS CountOfPupils FROM ( SELECT u.Name, COUNT(f.name) AS CountOfPupils FROM user_inf AS u LEFT JOIN follows AS f ON u.Name=f.user_name ) AS T
这篇关于SQL计数问题的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文