结合两个SQL select [英] Combine two SQL select

问题描述

您好。我有个问题。我有一个数据库，在我的数据库中有几个表。问题是我想要组合表类别和表项目。表类别有类别ID和类别名称，而表项目有很多列，但我想要选择的列是项目ID，用户名和类别ID。所以我想从类别表中选择id和类别名称，并从项目表中选择项目ID，用户名，类别ID。这意味着我想根据用户名显示每个类别名称的数量。



现在我已经获得了类别表中每个类别名称的项目总数。但现在我遇到的问题是当我添加where条件（其中username ='013'）时，它没有显示每个类别名称的所有总和。当用户名为013时，我希望它显示每个类别名称的总数。

hello. i have a problem. i have a database and in my database got several tables. the problem is i want to combine table category and table project. table category got category id and category name while table project got lots of column but column that i want to select are project id, username, and category id. so i want to select id and category name from category table and select project id, username, category id from project table. meaning that i want to display the count of each category name based on the username.

right now ive already got the total of project for each category name in category table. but the problem now that i faced is when i added where condition (where username = '013'), it didnt display all total of each category name. i want it display all total of each category name when username is 013.

this is my category table:

	id	cat_name
	1	New Project
	2	Uncategorized Project
	3	This year
	4	Next year
	5	Tender
	6	PO in
	7	Lost
	8	Follow Up Project
	9	Completed

then, here is my project table:

	id	Project_name	username	cat_id	
	1	KLCC	          013	      3			
	2	jelutong          013	      3	
	3	seri apartment	  013	      4			
	4	seri condo	      013         1	
															

so, the result that i want for username 013 are like this.

<pre>	id	cat_name	         count
	1	New Project	               2
	2	Uncategorized Project      2
	3	This year	               2
	4	Next year	               2
	5	Tender	                   1
    6   PO In                      0
    7   Lost                       0

我尝试过：



这里是我创建的sql命令

What I have tried:

here is the sql command that i created

SELECT c.id, c.cat_name, COUNT(p.id) as count
FROM asuwaris.category c
LEFT JOIN asuwaris.project p ON c.id = p.cat_id
where p.username = '013'
GROUP BY c.cat_name
ORDER BY c.id;

推荐答案

而不是使用 GROUP BY 使用SQL窗口函数。例如，像这样重写你的查询。

Instead of using GROUP BY use SQL Window functions. For example, rewrite your query like this.

SELECT DISTINCT c.id, c.cat_name, COUNT(p.id) OVER (PARTITION BY p.username, p.cat_id) as [count]
FROM category c
LEFT JOIN project p ON c.id = p.cat_id
where p.username = '013'
ORDER BY c.id;

我使用了以下示例数据

create table category
(
	id int identity(1,1),
	cat_name nvarchar(125)
)
insert into category values
('New Project'),
('Uncategorized Project'),
('This year'),
('Next year'),
('Tender')

create table project
(
	id int identity(1,1),
	cat_id int,
	projectname nvarchar(125),
	username nvarchar(5)
)
insert into project values
(1,'Project1', '013'), (1,'Project2', '013'),
(3,'Project3', '013'), (3,'Project4', '013'),
(2,'Project5', '013'), (2,'Project6', '013'),
(1,'Project7', '013'), (1,'Project8', '013'),
(4,'Project9', '013'), (5,'Project10', '013'),
(6,'Project11', '013'), (7,'Project12', '013')

我得到了这些结果

cat_id  Cat_name               Count
1	New Project	        4
2	Uncategorized Project	2
3	This year	        2
4	Next year	        1
5	Tender	                1

要克服错误，您只需在GROUP BY子句中包含类别名称：

To overcome the error you are getting just include the Category name in the GROUP BY clause:

SELECT c.id, c.cat_name, COUNT(p.id) as count
FROM category c
LEFT JOIN project p ON c.id = p.cat_id
where p.username = '013'
GROUP BY c.id, c.cat_name
ORDER BY c.id;

结果如上



[再次编辑！]

不幸的是你可以 只有用户'013'包含类别1到5（因为项目中有条目ta对于他们来说）或包括所有类别1到9（因为查询说包含项目表中计数为0的那些类别）。



只有两个表格，无法区分类别6和7，你做想要包括的零计数和类别8& 9，也是不想要包含的零计数。



解决这个问题的另一种方法是让另一个表将用户分配到一个类别 - 比如

Results as above


Unfortunately you can either have only categories 1 to 5 included for user '013' (because there are entries on the project table for them) or all categories 1 to 9 included (because the query says to include those categories with a count of 0 in the project table).

With only the two tables there is no way to distinguish between categories 6 & 7 with a zero count that you do want to include and the categories 8 & 9, also with a zero count that you do not want to include.

One way around this is to have another table that assigns a user to a category - like this

create table UserToCategory
(
	cat_id int,
	username nvarchar(5)
);
insert into UserToCategory (cat_id, username) values (1, '013');
insert into UserToCategory (cat_id, username) values (2, '013');
insert into UserToCategory (cat_id, username) values (3, '013');
insert into UserToCategory (cat_id, username) values (4, '013');
insert into UserToCategory (cat_id, username) values (5, '013');
insert into UserToCategory (cat_id, username) values (6, '013');
insert into UserToCategory (cat_id, username) values (7, '013');

然后此查询将获得用户013的类别1到7：

Then this query will get categories 1 to 7 for user 013:

SELECT c.id, c.cat_name, COUNT(p.id) as count
FROM UserToCategory cu 
LEFT JOIN category c ON c.id = cu.cat_id
LEFT JOIN project p on p.cat_id = c.id
where cu.username = '013'  
GROUP BY c.id, c.cat_name
ORDER BY c.id; 

你的第二个选择实际上是不传递SQL - 列'p.cat_id'在选择列表中无效，因为它不包含在任何一个中聚合函数或GROUP BY子句。

Your second select is actually do not pass SQL - Column 'p.cat_id' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

select p.cat_id, count(p.id) as count from asuwaris.project p

所以解决这个问题...

UNION的其他方面是：

1.相同数量的列 - 确定

2 。相同的数据类型 - 我无法为您检查...

So fix that...
The other aspects of UNION are:
1. Same number of columns - OK
2. Same data types - I can not check it for you...

这篇关于结合两个SQL select的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！