表名:tt_bad====================================================
id______name___is_good_____datatime_______descc
================================================================
1_______zhang______y_______2013-2-1
2_______wang_______n_______2013-2-2
3_______qian_______n_______2013-2-3
4_______zhang______n_______2013-2-4
5_______qian_______y_______2013-2-9
6_______wang_______n_______2013-2-12
7_______zhang______y_______2013-2-22
8_______qian_______n_______2013-2-23
9_______wang_______y_______2013-2-24
10______zhang______n_______2013-2-25
11______wang_______n_______2013-2-28
12______qian_______y_______2013-3-1
13______wang_______n_______2013-3-2
14______zhang______y_______2013-3-3
15______qian_______y_______2013-3-4
16______wang_______n_______2013-3-5
==============================================================================================================================现要写个子查询,查询之后的结果如下:
name_____y+n的总数______y的数量_______n的数量
zhang______5______________3_____________2
wang_______6______________1_____________5
qian_______5______________3_____________2
===============================================================
其实就是按name分组之后,
再统计每个人的isgood列的总数以及每个人的y和n的数量 ;
下面的子查询语句没有y和n列,
select name n , count(is_good) isgd from tt_bad group by name;
不知道怎么查询出y和n,求助
=============================================================
好友提供的方案1:
select name n , count(is_good) isgd ,
sum(case when is_good='y' then 1 else 0 end) y,
sum(case when is_good='n' then 1 else 0 end) n
from tt_bad group by name;
好友提供的方案2:
select count(id) as 总记录数,
总记录数-count( select count(is_good) from table where is_good='y') as 不好的记录数,
(总记录数-不好的记录数) as 好的记录数
from table group by name
以上2中方案都没有查询出正确的结果数据库table
id______name___is_good_____datatime_______descc
================================================================
1_______zhang______y_______2013-2-1
2_______wang_______n_______2013-2-2
3_______qian_______n_______2013-2-3
4_______zhang______n_______2013-2-4
5_______qian_______y_______2013-2-9
6_______wang_______n_______2013-2-12
7_______zhang______y_______2013-2-22
8_______qian_______n_______2013-2-23
9_______wang_______y_______2013-2-24
10______zhang______n_______2013-2-25
11______wang_______n_______2013-2-28
12______qian_______y_______2013-3-1
13______wang_______n_______2013-3-2
14______zhang______y_______2013-3-3
15______qian_______y_______2013-3-4
16______wang_______n_______2013-3-5
==============================================================================================================================现要写个子查询,查询之后的结果如下:
name_____y+n的总数______y的数量_______n的数量
zhang______5______________3_____________2
wang_______6______________1_____________5
qian_______5______________3_____________2
===============================================================
其实就是按name分组之后,
再统计每个人的isgood列的总数以及每个人的y和n的数量 ;
下面的子查询语句没有y和n列,
select name n , count(is_good) isgd from tt_bad group by name;
不知道怎么查询出y和n,求助
=============================================================
好友提供的方案1:
select name n , count(is_good) isgd ,
sum(case when is_good='y' then 1 else 0 end) y,
sum(case when is_good='n' then 1 else 0 end) n
from tt_bad group by name;
好友提供的方案2:
select count(id) as 总记录数,
总记录数-count( select count(is_good) from table where is_good='y') as 不好的记录数,
(总记录数-不好的记录数) as 好的记录数
from table group by name
以上2中方案都没有查询出正确的结果数据库table
sum(case when is_good='n' then 1 else 0 end) as n,
sum(case when is_good in ('y','n') then 1 else 0 end) as y+n
from tt_bad
group by name
# ---------------------------------------
# Host : localhost
# Port : 3399
# Database : testaa_db
# 表结构,create table/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;SET FOREIGN_KEY_CHECKS=0;DROP DATABASE IF EXISTS `testaa_db`;CREATE DATABASE `testaa_db`
CHARACTER SET 'utf8'
COLLATE 'utf8_general_ci';USE `testaa_db`;#
# Structure for the `tt_bad` table :
#DROP TABLE IF EXISTS `tt_bad`;CREATE TABLE `tt_bad` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(20) DEFAULT NULL,
`is_good` tinyint(1) DEFAULT NULL,
`datatime` datetime DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8;#
# Data for the `tt_bad` table (LIMIT 0,500)
#INSERT INTO `tt_bad` (`id`, `name`, `is_good`, `datatime`) VALUES
(1,'zhang',1,'2013-02-01'),
(2,'wang',0,'2013-02-02'),
(3,'qian',0,'2013-02-03'),
(4,'zhang',0,'2013-02-04'),
(5,'qian',1,'2013-02-09'),
(6,'wang',0,'2013-02-12'),
(7,'zhang',1,'2013-02-22'),
(8,'qian',0,'2013-02-23'),
(9,'wang',1,'2013-02-24'),
(10,'zhang',0,'2013-02-25'),
(11,'wang',0,'2013-02-28'),
(12,'qian',1,'2013-03-01'),
(13,'wang',0,'2013-03-02'),
(14,'zhang',1,'2013-03-03'),
(15,'qian',1,'2013-03-04'),
(16,'wang',0,'2013-03-05');COMMIT;/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
sum(case when is_good in (1,0) then 1 else 0 end) yn,
sum(case when is_good=1 then 1 else 0 end) y,
sum(case when is_good=0 then 1 else 0 end) n
from tt_bad
group by name