22,209
社区成员
发帖
与我相关
我的任务
分享mysql 日期行转列，请教各位SQL大神
SQL菜鸟，还是行转列的问题，不过是按照日期来。baidu、google了各种资料没实现，为此才发贴，请教各位 SQL 高手 ！
/************分割线*************/
/************分割线*************/
/*----用户注册表----*/
CREATE TABLE `reg_user` (
`id` DOUBLE ,
`userid` DOUBLE ,
`username` VARCHAR(20),
`ag_id` INT ,
`reg_time` VARCHAR(20)
);
INSERT INTO `reg_user` VALUES('1','1001','aaa1001','2','2012-09-01');
INSERT INTO `reg_user` VALUES('2','1002','aaa1002','3','2012-09-01');
INSERT INTO `reg_user` VALUES('3','1003','aaa1003','6','2012-09-01');
INSERT INTO `reg_user` VALUES('4','1004','aaa1004','1','2012-09-02');
INSERT INTO `reg_user` VALUES('5','1005','aaa1005','1','2012-09-02');
INSERT INTO `reg_user` VALUES('6','1006','aaa1006','3','2012-09-02');
INSERT INTO `reg_user` VALUES('7','1007','aaa1007','6','2012-09-03');
INSERT INTO `reg_user` VALUES('8','1008','aaa1008','3','2012-09-03');
INSERT INTO `reg_user` VALUES('9','1009','aaa1009','1','2012-09-03');
INSERT INTO `reg_user` VALUES('10','1010','aaa1010','3','2012-09-20');
INSERT INTO `reg_user` VALUES('11','1011','aaa1011','3','2012-09-20');
INSERT INTO `reg_user` VALUES('12','1012','aaa1012','1','2012-09-20');
INSERT INTO `reg_user` VALUES('13','1013','aaa1013','2','2012-09-21');
INSERT INTO `reg_user` VALUES('14','1014','aaa1014','2','2012-09-21');
INSERT INTO `reg_user` VALUES('15','1015','aaa1015','3','2012-09-21');
/*----广告商----*/
CREATE TABLE `advertisers` (
`ag_id` DOUBLE ,
`ag_name` VARCHAR (96)
);
INSERT INTO `advertisers` (`ag_id`, `ag_name`) VALUES('1','广告1');
INSERT INTO `advertisers` (`ag_id`, `ag_name`) VALUES('2','广告2');
INSERT INTO `advertisers` (`ag_id`, `ag_name`) VALUES('3','广告3');
INSERT INTO `advertisers` (`ag_id`, `ag_name`) VALUES('4','广告4');
INSERT INTO `advertisers` (`ag_id`, `ag_name`) VALUES('5','广告5');
INSERT INTO `advertisers` (`ag_id`, `ag_name`) VALUES('6','广告6');
/* 想查询出的结果为：根据每个广告商统计每天的注册人数(30天)，*/
广告商 2012-09-01 2012-09-02 2012-09-03 ... ... 2012-09-20 2012-09-21 ... 2012-09-30
广告1 0 2 1 ... ... 1 0 0
广告2 1 0 0 ... ... 0 2 0
广告3 1 1 1 ... ... 2 1 0
广告4 0 0 0 ... ... 0 0 0
广告5 0 0 0 ... ... 0 0 0
广告6 1 0 1 ... ... 0 0 0
...全文
请发表友善的回复…
发表回复
發糞塗牆 2012-09-22
- 打赏
- 举报
mysql的我不会，既然你发到SQLServer专区，那我给个SQLServer的写法给你自己改吧
/*----用户注册表----*/
CREATE TABLE reg_user (
id int ,
userid int ,
username VARCHAR(20),
ag_id INT ,
reg_time VARCHAR(20)
);
INSERT INTO reg_user VALUES('1','1001','aaa1001','2','2012-09-01');
INSERT INTO reg_user VALUES('2','1002','aaa1002','3','2012-09-01');
INSERT INTO reg_user VALUES('3','1003','aaa1003','6','2012-09-01');
INSERT INTO reg_user VALUES('4','1004','aaa1004','1','2012-09-02');
INSERT INTO reg_user VALUES('5','1005','aaa1005','1','2012-09-02');
INSERT INTO reg_user VALUES('6','1006','aaa1006','3','2012-09-02');
INSERT INTO reg_user VALUES('7','1007','aaa1007','6','2012-09-03');
INSERT INTO reg_user VALUES('8','1008','aaa1008','3','2012-09-03');
INSERT INTO reg_user VALUES('9','1009','aaa1009','1','2012-09-03');
INSERT INTO reg_user VALUES('10','1010','aaa1010','3','2012-09-20');
INSERT INTO reg_user VALUES('11','1011','aaa1011','3','2012-09-20');
INSERT INTO reg_user VALUES('12','1012','aaa1012','1','2012-09-20');
INSERT INTO reg_user VALUES('13','1013','aaa1013','2','2012-09-21');
INSERT INTO reg_user VALUES('14','1014','aaa1014','2','2012-09-21');
INSERT INTO reg_user VALUES('15','1015','aaa1015','3','2012-09-21');
/*----广告商----*/
CREATE TABLE advertisers (
ag_id int ,
ag_name VARCHAR (96)
);
INSERT INTO advertisers VALUES('1','广告1');
INSERT INTO advertisers VALUES('2','广告2');
INSERT INTO advertisers VALUES('3','广告3');
INSERT INTO advertisers VALUES('4','广告4');
INSERT INTO advertisers VALUES('5','广告5');
INSERT INTO advertisers VALUES('6','广告6');
SELECT * FROM #t
SELECT a.id,a.userid,a.username,a.reg_time,b.ag_name INTO t
FROM reg_user a INNER JOIN advertisers b ON a.ag_id=b.ag_id
declare @s nvarchar(4000)
set @s=''
Select @s=@s+','+quotename(reg_time)+'=count(case when [reg_time]='+quotename(reg_time,'''')+' then [ag_name] else null end)'
from t group BY ag_name,reg_time
--PRINT @s
EXEC ('select [ag_name]'+@s+',[reg_time]=count(ag_name) from t group by ag_name')
select [ag_name],[2012-09-02]=count(case when [reg_time]='2012-09-02' then [ag_name] else null end),[2012-09-03]=count(case when [reg_time]='2012-09-03' then [ag_name] else null end),[2012-09-20]=count(case when [reg_time]='2012-09-20' then [ag_name] else null end),[2012-09-01]=count(case when [reg_time]='2012-09-01' then [ag_name] else null end),[2012-09-21]=count(case when [reg_time]='2012-09-21' then [ag_name] else null end),[2012-09-01]=count(case when [reg_time]='2012-09-01' then [ag_name] else null end),[2012-09-02]=count(case when [reg_time]='2012-09-02' then [ag_name] else null end),[2012-09-03]=count(case when [reg_time]='2012-09-03' then [ag_name] else null end),[2012-09-20]=count(case when [reg_time]='2012-09-20' then [ag_name] else null end),[2012-09-21]=count(case when [reg_time]='2012-09-21' then [ag_name] else null end),[2012-09-01]=count(case when [reg_time]='2012-09-01' then [ag_name] else null end),[2012-09-03]=count(case when [reg_time]='2012-09-03' then [ag_name] else null end),[reg_time]=count(ag_name) from t group by ag_name
/*
ag_name 2012-09-02 2012-09-03 2012-09-20 2012-09-01 2012-09-21 2012-09-01 2012-09-02 2012-09-03 2012-09-20 2012-09-21 2012-09-01 2012-09-03 reg_time
------------------------------------------------------------------------------------------------ ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
广告1 2 1 1 0 0 0 2 1 1 0 0 1 4
广告2 0 0 0 1 2 1 0 0 0 2 1 0 3
广告3 1 1 2 1 1 1 1 1 2 1 1 1 6
广告6 0 1 0 1 0 1 0 1 0 0 1 1 2
警告: 聚合或其他 SET 操作消除了 Null 值。
(4 行受影响)
*/
早起晚睡 2012-09-22
- 打赏
- 举报
这个有点费劲 看看高手的吧 今天都休息呢吧
