Consultation on mysql grouping query statistics
(Mysql) table: z_hzmanage:
Create a table;
Create table 'Z _ hzmanage '(
'Z _ id' int (11) not null auto_increment,
'Z _ yyh' varchar (25) default NULL,
'Z _ uname' varchar (10) not null,
'Z _ sex' int (10) unsigned not null default '0 ',
'Z _ age' varchar (10) default '0 ',
'Z _ region' int (15) unsigned not null default '0 ',
'Z _ tel 'varchar (15) default NULL,
'Z _ jzbz1 'varchar (25) default NULL,
'Z _ jzbz2 'varchar (25) default NULL,
'Z _ dzdate' varchar (30) default NULL,
'Z _ yydate' varchar (30) default NULL,
'Z _ isxf' int (5) unsigned not null default '0 ',
'Z _ source' int (10) unsigned not null default '0 ',
'Z _ isfbz' int (10) unsigned not null default '0 ',
'Z _ iszsl 'Int (10) unsigned not null default '0 ',
'Z _ jzys 'int (10) unsigned not null default '0 ',
'Z _ content' text,
'Z _ isstate' int (5) unsigned not null default '0 ',
'Z _ class' int (10) unsigned not null default '0 ',
'Z _ username' varchar (10) default NULL,
'Z _ systemdate' date not null default '2017-00-00 ',
Primary key ('Z _ id ')
) ENGINE = InnoDB AUTO_INCREMENT = 43 default charset = gbk;
Insert data as follows:
Insert into z_hzmanage VALUES ('20', 'vb004 ', 'Zhang San', '1', '59', '3', '123', '2', null, '2017-07-22 ', '2017-07-22', '1', '8', '1', '1', '4', 'Prostate hyperplasia ', '1', '43 ', 'Jia rongwarm', '2017-07-07 ');
Insert into z_hzmanage VALUES ('22', 'a120 ', 'Li Xiang', '1', '25', '27', '123', '13', null, '', '2014-07-25 ', '0', '2', '0', '0', '0', 'noisy, noisy,', '0 ', '46', 'Yu Xin', '2017-07-08 ');
Insert into z_hzmanage VALUES ('23', 'b08136 ', 'Zhang Hong', '2', '23', '3', '123', '13', null, '2014-07-22 ', '2014-07-22', '0', '2', '0', '0', '0', '0', 'malfore', '0 ', '49', 'Liu zonglil', '2017-07-08 ');
Insert into z_hzmanage VALUES ('24', '2017111109', 'Single country Ming', '1', '60', '3', '123', '13', null, '2017-07-22 ', '2017-07-22', '0', '2', '0', '0', '0', '0 ', 'urinary system infections with hematuria ', '1', '43', 'Jia rongwarm ', '2017-07-08 ');
Insert into z_hzmanage VALUES ('25', 'x08}', 'Lee Chuanshan ', '2', '36', '3', '123', '13', null, '', '2017-07-10 ', '0', '2', '0', '0', '0', 'Premature ejaculation', '0 ', '49', 'Liu zonglil', '2017-07-08 ');
Insert into z_hzmanage VALUES ('26', 'x0111110', 'Wu Xiaodong ', '1', '28', '3', '123', '13', null, '2017-07-22 ', '2017-07-22', '0', '2', '0', '0', '0', '', '0 ', '43 ', 'Jia rongwarm', '2017-07-08 ');
Insert into z_hzmanage VALUES ('27', '2017121110', 'dong Yanqiu ', '2', '29', '13', '123', '13', null, '', '2017-07-22 ', '0', '1', '0', '0', '0','', '0', '43 ', 'Jia rongnu', '2017-07-08 ');
Insert into z_hzmanage VALUES ('28', 'x08135', 'Zhang mingxin', '1', '24', '3', '123', '13', null, '', '2017-07-08 ', '0', '2', '0', '0', '0','', '0', '49 ', 'Liu zonglil', '2017-07-08 ');
Insert into z_hzmanage VALUES ('29', '123456', 'Shout', '1', '24', '3', '1234568', '1', null, '', '2017-07-08 ', '0', '2', '0', '0', '0','', '0', '49 ', 'Liu zonglil', '2017-07-08 ');
Insert into z_hzmanage VALUES ('30', '2017121112', 'zhangxu ', '1', '42', '13', '123', '131', '13', null, '', '2017-07-10 ', '0', '1', '0', '0', '0','', '0', '43 ', 'Jia rongnu', '2017-07-08 ');
Insert into z_hzmanage VALUES ('31', 'x08231 ', 'Wu Youjun', '2', '24', '29', '123', '0 ', '0', '', '2017-07-15 ', '0', '10', '0', '0', '0','', '0 ', '49', 'Liu zonglil', '2017-07-08 ');
Insert into z_hzmanage VALUES ('32', 'b012108 ', 'Zhao Yansong', '1', '40', '13', '123', '13 ', null, '', '2017-07-22 ', '0', '11', '0', '0', '0','', '0 ', '43 ', 'Jia rongwarm', '2017-07-08 ');
Insert into z_hzmanage VALUES ('33', 'q1912', 'Wei Youjun ', '1', '50', '3', '123', '13', null, '2017-07-22 ', '2017-07-23', '0', '2', '0', '0', '0', '', '0 ', '52 ', 'Yue Jingjing', '2017-07-09 ');
Insert into z_hzmanage VALUES ('34', 'q1921', 'Wang Xianming ', '1', '50', '11', '123', '13', null, '', '2017-07-22 ', '0', '2', '0', '0', '0','', '0', '52 ', 'Yue Jingjing ', '2017-07-09 ');
Insert into z_hzmanage VALUES ('35', '11', 'Li haitao ', '1', '33', '11', '123', '1', null, '','', '0', '2', '0', '0', '0', '0', '0', '52 ', 'Yue Jingjing ', '2017-07-09 ');
Insert into z_hzmanage VALUES ('36', '11', 'hao Xiaofei ', '1', '28', '0', '20170', '0', null, '','', '0', '2', '0', '0', '0', '0', '0', '52 ', 'Yue Jingjing ', '2017-07-09 ');
Insert into z_hzmanage VALUES ('37', 'q1913 ', 'fu Xiaoliang', '1', '30', '0', '123', 'advisory ', 'short penis ', '','', '1', '2', '1', '1', '1', '1', '1 ', '52 ', 'Yue Jingjing', '2017-07-09 ');
Insert into z_hzmanage VALUES ('38', 'j13101 ', 'hao Zhi', '2', '32', '29', '123', '7', null, '','', '0', '2', '0', '0', '0', '0', '0', '50 ', 'Zhao Xueguang ', '2017-07-09 ');
Insert into z_hzmanage VALUES ('39 ', 'j13106', 'Qin Dongmei ', '2', '32', '3', '123', '13', null, '','', '0', '2', '0', '0', '0', '0', '0', '50 ', 'Zhao Xueguang ', '2017-07-09 ');
Insert into z_hzmanage VALUES ('40', 'x13203 ', 'Liu zhenmale', '1', '27', '0', '123', 'male malase ', 'bloodshot ', '','', '0', '2', '0', '0', '0', '', '0 ', '50', 'Zhao Xueguang ', '2017-07-09 ');
Insert into z_hzmanage VALUES ('41', 'ytf123', 'Wang two', '1', '28', '1', '123', 'inflammatory ', 'xiaoyan ', '', '2014-07-18', '0', '3', '0', '0', '1', '1', '123 ', '0', '27', 'summer New Year', '2017-07-18 ');
Insert into z_hzmanage VALUES ('42', 'mvcphp12', 'Wang 'er Machang ', '1', '27', '2', '123', 'Review ', 'ineffective or infected after hospital operation ', '', '2017-07-19', '0', '3', '0', '0', '0', '0 ', '', '0', '52 ', 'Yue Jingjing', '2017-07-19 ');
Z_class, z_username, z_yydate, and z_dzdate)
What I want now is to query the name of each user (the name cannot be repeated) and then query statistics, number of appointments made yesterday, from yesterday to the clinic, from this month, and from this month to the clinic
For example:
User name yesterday's appointment () yesterday's visit to the clinic () this month's appointment (unit: persons) this month to the clinic (unit: persons)
Jia rongwarm (6 persons) (6 persons) (6 persons) (6 persons)
Liu Zongli (5 persons) (5 persons) (5 persons) (5 persons)
Xia Xinhui (1 person) (1 person) (1 person) (1 person)
Yu Xin (1 person) (1 person) (1 person) (1 person)
Yue Jingjing (6 persons) (6 persons) (6 persons) (6 persons)
Zhao Xuanguang (3 persons) (3 persons) (3 persons) (3 persons)
It is to calculate the number of people who have made an appointment yesterday, the number of people who have been diagnosed yesterday, the number of people who have made an appointment this month, and the number of people who have been diagnosed this month, and then click the number to see the details, number of appointments yesterday, number of visits yesterday, number of appointments this month, and number of visits this month.
I didn't get it out in the afternoon. please help me. thank you very much. Thank you !.
Reply to discussion (solution)
We recommend that you make an appointment yesterday for four statistical queries... The number of patients of each person, and then integrate them.
SELECT z_username, SUM (case when z_yydate = '2017-07-22 'THEN 1 ELSE 0 END) AS yesterday's reservation, SUM (case when z_dzdate = '2017-07-22 'THEN 1 ELSE 0 END) AS yesterday, SUM (case when z_yydate LIKE '2017-2013' THEN 1 ELSE 0 END) AS this month's appointment, SUM (case when z_dzdate LIKE '2017-2013 'THEN 1 ELSE 0 END) AS this month to visit FROM z_hzmanage group by z_class
Code implementation on the third floor
I don't even understand what you mentioned above
Master on the third floor, good to learn
It must be a master. you can see how high your family is. learning ....
... I did not even score points?
