mysql下行转列

select t.add_time,
sum(case t.add_uid when 1  then t.num else 0 end) as 'admin'
from
(
select
o.add_uid,DATE_FORMAT(o.add_time, '%Y-%m-%d') as add_time,count(o.id) as num
from hotels_orders o
group by add_uid,add_time
order by add_time) t
group by add_time

 

 

----tables

-- ----------------------------
-- Table structure for hotels_orders
-- ----------------------------
CREATE TABLE `hotels_orders` (
  `id` bigint(20) NOT NULL auto_increment,
  `order_no` char(7) default NULL,
  `hotels_name` varchar(255) default NULL,
  `room_type` varchar(255) default NULL,
  `room_num` int(1) default NULL,
  `guests` int(1) default NULL,
  `guest_name` varchar(255) default NULL COMMENT '逗号隔开',
  `live_date` date default NULL,
  `leave_date` date default NULL,
  `days` int(1) default NULL,
  `sale_money` varchar(255) default NULL,
  `low_money` varchar(255) default NULL,
  `room_money` double(10,2) default NULL,
  `payment` int(1) default NULL COMMENT '1:前台现付\r\n            2:预付转账',
  `rebate` double(10,2) default NULL,
  `channel` bigint(20) default NULL COMMENT '渠道标识',
  `channel_no` varchar(255) default NULL,
  `intro` varchar(400) default NULL,
  `rel_name` varchar(50) default NULL,
  `rel_sex` varchar(1) default NULL,
  `sure_way` int(1) default NULL COMMENT '1:手机短信\r\n            2:电话确认\r\n            3:传真确认\r\n            4:邮件确认',
  `rel_mobile` varchar(50) default NULL,
  `rel_tel` varchar(50) default NULL,
  `rel_fax` varchar(50) default NULL,
  `rel_email` varchar(50) default NULL,
  `state` int(1) default NULL COMMENT '1:预订确认\r\n            2::更改 \r\n            3:在住\r\n            4:取消\r\n            5:续住\r\n            6:提前退房 \r\n            7:正常退房\r\n            8:NO SHOW',
  `operation_time` datetime default NULL,
  `operation_id` bigint(20) default NULL,
  `expected_time` varchar(50) default NULL,
  `latest_time` varchar(50) default NULL,
  `agent` varchar(50) default NULL,
  `add_time` datetime default NULL,
  `add_uid` bigint(20) default NULL,
  `results` int(1) default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;