求总计sql表
aid tag tag1
1 a,b c,d
2 ,b ,d
3 a, c,d
4 a, c,d
5 ,b c,d .....id 是编号 tag 只有a和b tag1 只有c和d 中间用逗号不知道能不能用一条简单语句总计出来如上结果是
a(3)b(3) c(4)d(5)就是总计abcd个数出来
aid tag tag1
1 a,b c,d
2 ,b ,d
3 a, c,d
4 a, c,d
5 ,b c,d .....id 是编号 tag 只有a和b tag1 只有c和d 中间用逗号不知道能不能用一条简单语句总计出来如上结果是
a(3)b(3) c(4)d(5)就是总计abcd个数出来
sum(if(instr(tag,'b'),1,0) as b,
sum(if(instr(tag1,'c'),1,0) as c,
sum(if(instr(tag1,'d'),1,0) as d
from 表
sum(if(instr(tag1,'c')>0,1,0) as c, sum(if(instr(tag1,'d')>0,1,0) as d
from tt
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'as a, sum(if(instr(tag,'b')>0,1,0) as b, sum(if(instr(tag1,'c')>0,1,0) as ' at line 1表的结构 `a`
--CREATE TABLE IF NOT EXISTS `a` (
`id` int(50) NOT NULL AUTO_INCREMENT,
`tag` varchar(50) CHARACTER SET utf8 NOT NULL,
`tag1` varchar(50) CHARACTER SET utf8 NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=gbk AUTO_INCREMENT=7 ;--
-- 转存表中的数据 `a`
--INSERT INTO `a` (`id`, `tag`, `tag1`) VALUES
(1, 'a,b', 'c,d'),
(2, 'a,', 'c,d'),
(3, 'a,', ',d'),
(4, 'a,b', ',d'),
(5, 'a,b', ',d'),
(6, 'a,b', 'c,d');