mysql query building从单列中获取2个不同的数据

问题描述：

伙计我有一个表，其中我有性别和日期列所述。现在我想统计在特定月份登记的男性和女性，所以我怎么能让我的查询。mysql query building从单列中获取2个不同的数据

mysql> select id,gender,entrydate from patient_master; 

+----+--------+------------+ 
| id | gender | entrydate | 
+----+--------+------------+ 
| 1 | Male | 2012-07-02 | 
| 2 | Female | 2012-05-10 | 
| 3 | Male | 2012-05-25 | 
| 4 | Female | 2012-07-09 | 
| 5 | Male | 2012-07-10 | 
| 6 | Female | 2012-07-10 | 
| 7 | Male | 2012-07-10 | 
+----+--------+------------+

家伙就像从上面的例子，我想没有在may..so结果月登记的性别将显示，男性和女性在同样的方式，它会显示计数1 7月份的男性和女性的计数为2 – 2012-07-10 20:29:57

家伙我需要它为multiseries线graph..so我想我需要的数据以这样的方式，以便我可以绘制一年的所有月份。 – 2012-07-10 20:34:52

结果集应该是什么形状？多少行？ 12？ 24？多少列？ 3？ – 2012-07-10 20:36:50

答

要得到所有月份的结果：

SELECT YEAR(entrydate) AS yr, MONTH(entrydate) AS mnth, gender, COUNT(*) AS cnt 
FROM patient_master 
GROUP BY YEAR(entrydate), MONTH(entrydate), gender

如果你想为一个单一的所有月份年份：

SELECT MONTH(entrydate) AS mnth, gender, COUNT(*) AS cnt 
FROM patient_master 
WHERE YEAR(entrydate) = 2012 
GROUP BY MONTH(entrydate), gender

这件事情也为我工作..thnx伙计 – 2012-07-10 20:43:24

答

select gender, count(id) as`count` 
from patient_master 
where month(entrydate) = 1 and year(entrydate) = 2012 
group by gender

月1 =一月，2 =二月...

因此，为可能，只是改变'month（entrydate）= 1'到'month（entrydate）= 5' – Applehat 2012-07-10 20:32:56

答

SELECT 
    date_format(entrydate,'%Y-%m-%b') YearMonth, 
    gender,COUNT(1) GenderCount 
FROM 
    patient_master 
GROUP BY 
    date_format(entrydate,'%Y-%m-%b'),gender 
;

这是你的样本数据

mysql> CREATE TABLE patient_master 
    -> (
    ->  id int not null auto_increment, 
    ->  gender varchar(10), 
    ->  entrydate date, 
    ->  primary key (id) 
    ->); 
Query OK, 0 rows affected (0.06 sec) 

mysql> INSERT INTO patient_master (gender,entrydate) VALUES 
    -> ('Male' ,'2012-07-02'), 
    -> ('Female','2012-05-10'), 
    -> ('Male' ,'2012-05-25'), 
    -> ('Female','2012-07-09'), 
    -> ('Male' ,'2012-07-10'), 
    -> ('Female','2012-07-10'), 
    -> ('Male' ,'2012-07-10'); 
Query OK, 7 rows affected (0.06 sec) 
Records: 7 Duplicates: 0 Warnings: 0

这里是输出

mysql>  SELECT 
    ->   date_format(entrydate,'%Y-%m-%b') YearMonth, 
    ->   gender,COUNT(1) GenderCount 
    ->  FROM 
    ->   patient_master 
    ->  GROUP BY 
    ->   date_format(entrydate,'%Y-%m-%b'),gender 
    ->  ; 
+-------------+--------+-------------+ 
| YearMonth | gender | GenderCount | 
+-------------+--------+-------------+ 
| 2012-05-May | Female |   1 | 
| 2012-05-May | Male |   1 | 
| 2012-07-Jul | Female |   2 | 
| 2012-07-Jul | Male |   3 | 
+-------------+--------+-------------+ 
4 rows in set (0.02 sec) 

mysql>

yaa thnx伙计它为我工作... – 2012-07-10 20:42:42

答

@juergen_d是正确的。

他的查询将返回此：

+--------+--------+ 
| gender | count | 
+--------+--------+ 
| Male | 1  | 
| Female | 1  | 
+--------+--------+

mysql query building从单列中获取2个不同的数据

相关推荐