server 2012中的计数结果

Question

我在Server 2012中有查询来查看我公司的用户部门。

SELECT mdl_user_info_data.data
FROM 
    dbo.mdl_user, 
    dbo.mdl_course, 
    dbo.mdl_user_enrolments, 
    dbo.mdl_enrol, 
    dbo.mdl_course_categories,
    dbo.mdl_user_info_data
WHERE 
    mdl_course.id = mdl_enrol.courseid AND 
    mdl_enrol.id = mdl_user_enrolments.enrolid AND 
    mdl_user_enrolments.userid = mdl_user.id AND 
    mdl_course.category = mdl_course_categories.id AND 
    mdl_course.id = 3 and
    mdl_user_info_data.fieldid = 3 and
    mdl_user.id = mdl_user_info_data.userid

我的查询结果是：

|data|
|CIS|
|CIS|
|TOP|

但我想要这样：

|DATA| TOTAL |
|CIS | 2 |
|TOP | 1 |

有人能帮我吗？不管怎样，谢谢。

我的mdl_user_info_data属性：

[id] [bigint] IDENTITY(1,1) NOT NULL,
[userid] [bigint] NOT NULL,
[fieldid] [bigint] NOT NULL,
[data] [ntext] NOT NULL,
[dataformat] [smallint] NOT NULL,

mdl_user_info_data中的一些行：

用户id字段数据格式

3 3 3独联体0

Answer 1

试试这个-

SELECT id.data, COUNT(1)
FROM dbo.mdl_user u
JOIN dbo.mdl_user_enrolments ue ON ue.userid = u.id
JOIN dbo.mdl_enrol e ON e.id = ue.enrolid
JOIN dbo.mdl_course c ON c.id = e.courseid
--JOIN dbo.mdl_course_categories cc ON c.category = cc.id
JOIN dbo.mdl_user_info_data id ON u.id = id.userid
WHERE c.id = 3
     AND id.fieldid = 3
GROUP BY id.data

更新：

SELECT t.data, Total = COUNT(1) 
FROM (
     SELECT data = CAST(id.data AS NVARCHAR(MAX))
     FROM dbo.mdl_user u
     JOIN dbo.mdl_user_enrolments ue ON ue.userid = u.id
     JOIN dbo.mdl_enrol e ON e.id = ue.enrolid
     JOIN dbo.mdl_course c ON c.id = e.courseid
     --JOIN dbo.mdl_course_categories cc ON c.category = cc.id
     JOIN dbo.mdl_user_info_data id ON u.id = id.userid
     WHERE c.id = 3
          AND id.fieldid = 3
) t
GROUP BY t.data

Answer 2

尝试使用group by子句

SELECT mdl_user_info_data.data,count(1) as Total
FROM dbo.mdl_user, 
     dbo.mdl_course, 
     dbo.mdl_user_enrolments, 
     dbo.mdl_enrol, 
     dbo.mdl_course_categories,
     dbo.mdl_user_info_data
WHERE mdl_course.id = mdl_enrol.courseid AND 
      mdl_enrol.id = mdl_user_enrolments.enrolid AND 
      mdl_user_enrolments.userid = mdl_user.id AND 
      mdl_course.category = mdl_course_categories.id AND 
      mdl_course.id = 3 and
      mdl_user_info_data.fieldid = 3 and
      mdl_user.id = mdl_user_info_data.userid
group by 
      mdl_user_info_data.data

但请考虑下面的解决方案

SELECT muid.data,count(1) as Total
FROM dbo.mdl_user mu, 
     dbo.mdl_course mc, 
     dbo.mdl_user_enrolments mue, 
     dbo.mdl_enrol me, 
     dbo.mdl_course_categories mcc,
     dbo.mdl_user_info_data muid
WHERE mc.id = me.courseid AND 
      me.id = mue.enrolid AND 
      mue.userid = mu.id AND 
      mc.category = mcc.id AND 
      mc.id = 3 and
      muid.fieldid = 3 and
      mu.id = muid.userid
group by muid.data

在上面的示例中，我向列添加了别名。使用它们是很好的做法，因为代码更易读。