SQL：查询显示有多少用户拥有每个属性（分组） [英] SQL: query to show how many users have each property (grouped)

问题描述

我是SQL新手，似乎无法提出正确的问题，因为每次我用不同的方式进行表达时，结果都是相同的结果：一个将数据按多个属性进行分组的查询（共享多个一个属性）。

我在Glassfish服务器上编写一个基于JSP的网站，并从MS Access管理数据库。

我感兴趣的是基本上由几个不同的属性组成。假设我有一个表格显示用户拥有哪些项目。就像这样：

  id |名称| item1 | item2 | item3 | item4 | 
 ---------------------------------------------- ---- 
 1 | name1 |是|是| |是| 
 ---- + ------- + --------- + -------- + -------- + ----- --- + 
 2 | name2 |是| | |是| 
 ---- + ------- + --------- + -------- + -------- + ----- --- + 
 3 | name3 | |是| |是| 
 ---- + ------- + --------- + -------- + -------- + ----- --- + 
 4 | name4 |是|是| |是| 
 ---- + ------- + --------- + -------- + -------- + ----- --- + 
 5 | name5 | | |是|是| 
。 
。 
。 
  

我需要的查询会返回以下内容：

  ItemID |具有此项目的用户数
 ---------------------------------------- -  
 item1 | 3 
 item2 | 3 
 item3 | 1 
 item4 | 5 
  

我没有看到 GROUP BY 可以在这里使用，因为我正在寻找的结果基本上要求原始表的属性，现在将在结果的每一行中显示为值 table。

什么是正确的查询，以及如何调用这样的操作（它不是每个属性的分组，这是别的... ）？

解决方案

您可以执行一个聚合查询，返回1个记录，每个Item字段下的计数。假设这些是Yes / No类型字段：

$ pre $ SELECT $（$） Sum（IIf（[Item4]，1,0））AS Count4 FROM Table1; Sum（IIf（[Item2]，1,0））AS Count2，Sum（IIf（[Item3]，1,0）

您的数据结构没有标准化。而不是4项目字段应该是具有项目代码的一个项目字段。标准化结构将允许GROUP BY或CROSSTAB查询选项。

  id |名称| item | 
 ---------------------- + 
 1 | name1 | 1 | 
 ---- + ------- + --------- + 
 2 | name1 | 2 | 
 ---- + ------- + --------- + 
 3 | name1 | 4 | 
 ---- + ------- + --------- + 
 4 | name2 | 1 | 
 ---- + ------- + --------- + 
 5 | name2 | 4 | 
  

可以使用UNION查询来模拟标准化结构。没有UNION的设计器，必须输入SQL查询构建器。

  SELECT ID AS SourceRecID，[Name] AS User，1 AS Item，Item1AS Source FROM tablename 
 UNION SELECT ID，[Name]，2，Item2FROM tablename 
 ...; 
  

现在在另一个查询中使用该查询来执行聚合GROUP BY计算。或者将其用作报告的来源。一份报告将允许显示原始详细数据以及摘要计算。

I'm new to SQL and can't seem to ask the right question, as in every time I formulate it differently I end up with the same results: A query that groups data by several properties (users that share more than one property).

I'm writing a JSP-based website on the Glassfish server, and manage the database from MS Access.

What I'm interested in, is basically grouping by several distinct properties. Say I have a table that shows which items a user has. Something like this:

id  |  name |  item1  | item2  | item3  | item4  |
--------------------------------------------------
1   | name1 |   yes   |  yes   |        | yes    |
----+-------+---------+--------+--------+--------+
2   | name2 |   yes   |        |        | yes    |
----+-------+---------+--------+--------+--------+
3   | name3 |         |  yes   |        | yes    |
----+-------+---------+--------+--------+--------+
4   | name4 |   yes   |  yes   |        | yes    |
----+-------+---------+--------+--------+--------+
5   | name5 |         |        |  yes   | yes    | 
.
.
.

The query that I need, would return the following:

ItemID  |  Number of users with this item
-----------------------------------------
item1   |  3
item2   |  3
item3   |  1
item4   |  5

I don't see how GROUP BY can be used here, as the result I'm looking for basically demands that the properties of the original table, will now appear as the values in each row of the resulting table.

What is the right query, and how such operation is called (it's not grouping by each property, it's something else...)?

解决方案

You could do an aggregate query that returns 1 record with the count under each Item field. Assuming these are Yes/No type fields:

SELECT Sum(IIf([Item1],1,0)) AS Count1, Sum(IIf([Item2],1,0)) AS Count2, Sum(IIf([Item3],1,0)) AS Count3, Sum(IIf([Item4],1,0)) AS Count4 FROM Table1;

Your data structure is not normalized. Instead of 4 Item fields should be one Item field with the item code. Normalized structure would allow option for GROUP BY or CROSSTAB queries.

id  |  name |  item   |
----------------------+
1   | name1 |   1     |
----+-------+---------+
2   | name1 |   2     |
----+-------+---------+
3   | name1 |   4     |
----+-------+---------+
4   | name2 |   1     |
----+-------+---------+
5   | name2 |   4     |

Can emulate the normalized structure with a UNION query. There is no designer for UNION, must type into SQLView of query builder.

SELECT ID AS SourceRecID, [Name] AS User, 1 AS Item, "Item1" AS Source FROM tablename
UNION SELECT ID, [Name], 2, "Item2" FROM tablename
...;

Now use that query in another query to do the aggregate GROUP BY calcs. Or use it as the source for a report. A report will allow display of raw detail data as well as summary calcs.

这篇关于SQL：查询显示有多少用户拥有每个属性（分组）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！