mysql 例子

最新推荐文章于 2024-07-23 18:29:36 发布

grost

最新推荐文章于 2024-07-23 18:29:36 发布

阅读量220

点赞数

文章标签： MySQL

mysql 例子

查询出行如下面结果？？？？

--用户表 
CREATE TABLE `users` ( 
  `id` varchar(4) NOT NULL, 
  `name` varchar(10) default NULL, 
  `ahid` varchar(10) default NULL, 
  PRIMARY KEY  (`id`) 
) ENGINE=MyISAM DEFAULT CHARSET=utf8; 
insert  into `users`(`id`,`name`,`ahid`) 
values ('1','tom','1,2'),('2','jerry','2,3'),('3','yoyo','1,3'), 
('4','bushi','1'),('5','ladeng','3'),('6','Jay','1,2,3'); 
# 爱好表 
CREATE TABLE `ah` ( 
  `ahid` varchar(10) NOT NULL, 
  `ahmc` varchar(10) default NULL, 
  PRIMARY KEY  (`ahid`) 
) ENGINE=MyISAM DEFAULT CHARSET=utf8; 
insert  into `ah`(`ahid`,`ahmc`) 
values ('1','football'),('2','basketball'),('3','music'); 
/*查询出ahmc*/ 
select  * from users a,ah b where a.ahid like concat(b.ahid,',%') or a.ahid like concat('%,',b.ahid) or

a.ahid like concat('%,',b.ahid,',%') or ahid=b.ahid; 
# 新建一个临时表 
create table mytest select a.id,a.name,a.ahid,b.ahmc from users a,ah b where a.ahid like concat('%',b.ahid,'%'); 
-- 将ahmc以 ,隔开显示 
select id,name,ahid,group_concat(ahmc order by ahmc separator ',') ahmc  from mytest group by id,name,ahid;