-
create table skills( id mediumint primary key auto_increment, username varchar(64), skill varchar(64), skill_level mediumint ); insert into skills(username,skill,skill_level) values('唐僧','紧箍咒',5) ,('唐僧','打坐',4) ,('唐僧','念经',5) ,('唐僧','变化',0) ,('猪八戒','变化',4) ,('猪八戒','腾云',3) ,('猪八戒','浮水',5) ,('猪八戒','念经',0) ,('猪八戒','紧箍咒',0) ,('孙悟空','变化',5) ,('孙悟空','腾云',5) ,('孙悟空','浮水',3) ,('孙悟空','念经',2) ,('孙悟空','请神',5) ,('孙悟空','紧箍咒',0) ,('沙僧','变化',2) ,('沙僧','腾云',2) ,('沙僧','浮水',4) ,('沙僧','念经',1) ,('沙僧','紧箍咒',0); select s1.username,s1.skill,s2.skill from skills s1 join skills s2 on s1.username=s2.username where s1.skill='变化' and s2.skill='念经' and s1.skill_level>0 and s2.skill_level>0; SELECT user_name,a.skill,b.skill FROM (SELECT * FROM `user_skills` WHERE skill='变化')a JOIN (SELECT * FROM `user_skills` WHERE skill='打怪')b ON a.user_id=b.user_id JOIN `qujing` q ON q.id=a.user_id;查看全部
-
select user_name from user1 where id in (select user_id from user_kills); -- 使用子查询可以避免由于子查询中的数据产生的重复。
select a.user_name from user1 a join user_kills b on a.id =b.user_id; -- 会产生重复记录
select distinct a.user_name from user1 a join user_kills b on a.id =b.user_id; -- 使用distinct去除重复记录
查看全部 -
测试笔记查看全部
-
@MySQL---使用关联方式实现多属性查询
Select a.user_name,b.skill,c.skill,e.skill From user1 a From user1 a Left join user1_skill b on a.id =b.user_id and b.skill='念经' and b.skill level>0 Left join user1_skill c on a.id = c.user_id and c.skill='变化' and c.skill_level>0 Left join user1_skill d on a.id = d.user_id and d.skill = '腾云' and c.skill_level>0 Left join user1_skill e on a.id = e.user_id and e.skill = '浮水' and e.skill_level>0 Where (case when b.skill is not null then 1 else 0 end) +(case when c.skill is not null then 1 else 0 end) +(case when d.skill is not null then 1 else 0 end) +(case when e.skill is not null then 1 else 0 end)>=2;
查看全部 -
过滤条件有多个,当任意两个满足就ok的SQL查询语句的思路,使用求和的方式来实现。查看全部
-
@MySQL---独有的多例过滤方式
SELECT a.user_name,b.timerstr,kills FROM user1 a JOIN user_kills b ON a.id=b.user_id WHERE(b.user_id,b.kills) IN( SELECT user_id,MAX(kills) FROM user_kills GROUP BY user_id);
查看全部 -
同一属性的多值过滤查看全部
-
g h j h h h查看全部
-
erddddff查看全部
-
自查询去重查看全部
-
自查询查看全部
-
select a.user_name from user1 a join user1_skills b on a.id=b.user_id where b.skill in ('念经','变化','腾云','浮水') and b.skill_level>0 group by a.user_name having count(*)>=2查看全部
-
子查询和join之间的区别,如果当某条记录有重复的时候, 使用子查询,在不加distinct的条件下可以直接剔除重复。但是效率方面join和子查询谁更有效率。查看全部
-
用join实现同属性多值过滤查看全部
-
mysql中独有的多列过滤查看全部
举报
0/150
提交
取消