sql序列与subselect [英] sql sequence with subselect

问题描述

我有一个要解决的问题，我必须通过一个sql语句显示一个部门中有多少名女性和多少名男性(显示必须像这样:"W:M").通过group by语句显示它们，如下所示:
从员工组中按性别选择性别，性别(性别)；

但是我必须使用子选择和上面提到的方式来显示它们.

那里有人可以帮助我吗?

非常感谢

Hy,

I have a problem to solve, I have to display through one single sql statement how many women and how many men work in a department (The display must be made like this: ''W:M'' )..I only know how to display them through a group by statement, like this:
select sex,count(sex) from employees group by sex;

But I have to display them using subselects and in the way I mentioned upper.

Is there anybody there who can help me?

Thanks a lot

推荐答案

您的意思是这样的吗?

Do you mean something like this?

select MenCount   =(select count(*) from employees where sex='M'),
       WomenCount =(select count(*) from employees where sex='W')

试试这个

select woman_count
      ,man_count
  from ( select count(sex) woman_count
               ,0 man_count
           from employees
          where sex='W'
         union all
         select 0 woman_count
               ,count(sex) man_count
           from employees
          where sex='M'
        )
;

不知道这是否真的是您要追求的，但希望它能为您提供一个起点. :)

Not sure if this is really what you are after, but hoperfully it gives you a starting point. :)

您需要将结果转换为字符串并将其合并以显示所需的内容

You''d need to convert the results to string and concat them to display how you want

SELECT CONVERT(VARCHAR(100), (
    SELECT count(*) FROM employees WHERE sex = 'W')) + ':'
+ CONVERT(VARCHAR(10), (SELECT COUNT(*) FROM employees WHERE sex = 'M'))

似乎没有意义！如Mark所述，格式化数据应与查询分开保存.

Seems a bit pointless though! As Mark mentioned, formatting data should be kept separate from your query.

这篇关于sql序列与subselect的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！