join 与 group by问题后续

版权声明:本文为博主原创文章,遵循 CC 4.0 by-sa 版权协议,转载请附上原文出处链接和本声明。
本文链接:https://blog.csdn.net/BloodyMandoo/article/details/61192326

昨天的问题,的确在sql server上问题明显,可是同样的数据库、同样的表、同样的查询语句,它却可以在mysql上执行,对于sql server与mysql的区别我并不清楚,

下面写一下我对于这可问题的探索过程:

因为我是在github上照着别人练习的例子练习的,既然别人写的查询语句都贴上来了,肯定是运行成功了,于是我就想在mysql上试一试,还好我之前装了mysql。


下面是两个表manu 和 pro的查询结果(因为表名太长了,改短了):





在看一下相同查询语句下的结果:



与昨天正确语句的查询结果对比一下:


这结果出入很大,问题也很多。

mysql上的查询明显有问题,缺少了很多行。


那在mysql上试一试正确的语句如何?

结果如下:




对比一下结果没有大的出入,除了那个price被我昨天改成190 以外。


那为什么相同的语句在sqlserver 和 mysql上使用得出的结果不同?

sqlserver上的报错与mysql上并不是我们所理想的结果,这是mysql的语法问题???



展开阅读全文

joingroup by 的一些非常规用法

12-27

续:rnhttp://expert.csdn.net/Expert/TopicView1.asp?id=2607573rn本来想写行合并的但一时没想好,还是说说join 吧。rn先再次声明一下,本人不是很喜欢使用join 运算符,喜欢直接用=、*=、=*表示各种join 。rnrn3、join,group by 的非常规用法rn本人认为,下列要求都可以考虑使用join的思路来解决问题rna、涉及列数变多的情况,就可以使用 join 。rn例:rn有表 emprnemp_no namern001 Tomrn002 Greenrn003 Samrn004 Sunrn005 Halern想变成两列显示rnemp_no1 name1 emp_no2 name2rn001 Tom 004 Sunrn002 Green 005 Halern003 Samrnrn大家还记得在2里我们怎么产生序列号的吗?rn于是有了rnselect e1.name as name1,e1.emp_no as emp_no1,e2.name as name2,e2.emp_no as emp_no2rnfrom rn(select emp.* ,(select count(*) from emp x where x.emp_no<=emp.emp_no) as cnt from emp) e1,rn(select emp.* ,(select count(*) from emp x where x.emp_no<=emp.emp_no) as cnt from emp) e2,rnwhere (e1.cnt-((select count(*) from emp)/2))*=e2.cntrnrn变形一下就有另一种表达方法,就不多写了。rnrnb、涉及对比可以使用joinrn对比,除了等于 还有 存在 不存在 大于等于……rn最简单的应用rn选择在表a、b 中都存在的 id是rnselect a.idrnfrom a join brnon a.id=b.idrn如果是不存在的呢?rnselect a.idrnfrom a join brnon a.id*=b.idrnwhere a.id+b.id is nullrn这样的对比自然没什么意义,但是通过对比思想来获得组合的思想有时候就很宝贵了,rn有表 Numrncntrn1rn2rn3rn4rn5rn6rn8rn问随意取出不同4个能有多少种组合?rn解法是rnselect t1.cnt as cnt1, t2.cnt as cnt2, t3.cnt as cnt3, t4.cnt as cnt4rnfrom Num t1,Num t2,Num t3,Num t4rnwhere t1.cnt>t2.cnt and t2.cnt>t3.cnt and t3.cnt>t4.cntrn或存在表 in_outrnid obj timern1 in 2003-12-27 13:30rn1 out 2003-12-27 14:29rn2 in 2003-12-27 12:30rn2 out 2003-12-27 15:30rn要求显示在1个小时内有出入的的记录rn就可以写成rnselect t1.* rnfrom in_out t1,in_out t2rnwherern t1.id=t2.idrn and (case when t1.obj='in' then 'out' else null end )=t2.objrn and convert(float,t2.time-t1.time)<=(1.0000/24/60)*60 rnrnrngroup by 一般是用来分组数据,比如说有表 emprnemp_no emp_name emp_agern001 Tom 19rn002 Sam 55rn003 Smith 33rn要求按照10岁的跨度汇总各年龄段内的人数:rnselect min(convert(varchar(3),(emp_age/10)*10)+'~'+convert(varchar(3),(emp_age/10)*10+9)) as 年龄段,count(*) as 人数rnfrom emprngroup by emp_age/10rnrn同时 需要注意 group by 子句和聚合函数的作用 如上例里再加上rnhaving count(*) >2rn则只显示在段内超过两人的项目。rnrn但是它同样可以用来过滤重复,这在2中已经有了相关表达.更重要的是它在与join 联用的时候可以提取特征数据!rn例表in_outrnid obj timern1 in 2003-12-27 13:30rn1 out 2003-12-27 14:29rn2 in 2003-12-27 12:30rn2 out 2003-12-27 15:30rn1 in 2003-12-28 13:30rn1 out 2003-12-28 14:29rn2 in 2003-12-28 12:30rn2 out 2003-12-28 15:30rn要求取出各人的停留时间。rn按照常规的做法,你可能被迫要做一个游标来遍历,以判断哪个in和哪个out配对,但有了 join 和 group by 你可以解放出来。rn根据特征 离进来最近的一笔出去的记录就是对应的出去 记录于是有:rnselect t1.id,t1.time as in_time,min(t2.time) as out_time,convert(varchar(8),t1.time-min(t2.time),108) as lengthrnfrom in_out t1,in_out t2rnwherern t1.id=t2.idrn and (case when t1.obj='in' then 'out' else null end )=t2.objrn and t1.time 论坛

行重复、行号、旋转、joingroup by

12-30

本来要写满下面几个:rn1、行重复、行号rn2、终横转换rn3、行合并rn4、join 、group by 的特殊用法rn5、oeder by 、top 的使用rn6、casern由于忽然变得很忙,就先不写了,把前面几个草草做个做整理放出,然后好好工作去:)rnrn几种常见SQL问题解决 总结rnrn1、选择重复,消除重复和选择出序列rna、有例表:emprnemp_no name agern001 Tom 17 rn002 Sun 14rn003 Tom 15rn004 Tom 16rnrn要求:rn列出所有名字重复的人的记录rnrna_1、最直观的思路:要知道所有名字有重复人资料,首先必须知道哪个名字重复了:rnrnselect name from emp rngroup by namernhaving count(*) >1rnrn于是所有名字重复人的记录是rnrnselect * from emp rnwhere rn name in (rn select name from emp rn group by namern having count(*) >1rn )rnrna_2、稍微再聪明一点,就会想到,如果对每个名字都和原表进行比较,大于2个人名字与这条记录相同的就是合格的 ,就有rnrnselect * from emprnwherern (select count(*) from emp e where e.name=emp.name)rn >1 rn --注意一下这个>1,想下如果是 =1,如果是 =2 如果是>2 如果 e 是另外一张表 而且是=0那结果 就更好玩了:)rnrn这个过程是 在判断工号为001的 人 的时候先取得 001的 名字(emp.name) 然后和原表的名字进行比较 e.namern注意e是emp的一个别名。rn再稍微想得多一点,就会想到,如果有另外一个名字相同的人工号不与她他相同那么这条记录符合要求,rnrnselect * from emprnwhere existsrn (select * from emp e where e.name=emp.name and e.emp_no<>emp.emp_no)rn这个思路的 join 写法是:rnselect emp.* rnfrom emp,emp ernwhere rn emp.name=e.name and emp.emp_no<>e.emp_norn /*rn 这个 语句较规范 的 join 写法是rn select emp.* rn from emp inner join emp ern onrn emp.name=e.name and emp.emp_no<>e.emp_norn 但个人比较倾向于前一种写法,关键是更清晰rn */rnb、有例表:emprnname agernTom 16 rnSun 14rnTom 16rnTom 16rn要求:rn过滤掉所有多余的重复记录rnb_1、我们知道distinct、group by 可以过滤重复,于是就有最直观的rnselect distinct * from emprn或rnselect name,age from emp group by name,agernrn获得需要的数据,如果可以使用临时表就有解法:rnselect distinct * into #tmp from emprndelete from emprninsert into emp select * from #tmprnb_2、但是如果不可以使用临时表,那该怎么办?rn我们观察到我们没办法区分数据(物理位置不一样,对 SQL Server来说没有任何区别),思路自然是想办法把数据区分出来了,既然现在的所有的列都没办法区分数据,唯一的办法就是再加个列让它区分出来,加什么列好?最佳选择自然是 identity 列了:rnalter table emp add chk int identity(1,1)rn表就成了:rnname age chkrnTom 16 1rnSun 14 2rnTom 16 3rnTom 16 4rn根据一的思路,重复记录可以表示为 rnselect * from emp rnwhere rn (select count(*) from emp e where e.name=emp.name)>1rn那要删除的自然是rndelete from emp rnwhere rn (select count(*) from emp e where e.name=emp.name and e.chk>=emp.chk)>1rn rn再把添加的列 喀嚓 掉就是要的结果了rnalter table emp drop column chkrnrnb_3、另一思路:rn视图rnselect min(chk) rnfrom emp rngroup by namernhaving count(*) >1 rn获得有重复的记录chk 最小的值,于是可以 rndelete rnfrom emp rnwhere rn chk not in rn (select min(chk) rn from emp rn group by namern )rn喜欢写成join 的形式自然也可以:)rnrnc、有例表:emprnemp_no name agern001 Tom 17 rn002 Sun 14rn003 Tom 15rn004 Tom 16rn要求生成序列号rnb_1、最简单的方法,根据b问题的解法:rnalter table emp add chk int identity(1,1)rn或 select *,identity(int,1,1) chk into #tmp from emp rn另一问题:如果需要控制顺序怎么办?rnselect *,identity(int,1,1) chk into #tmp from emp order by age rnb_2、但是如果 不可以更改表结构,怎么办?rn如果不可以唯一区分没条记录是没有办法的,在可以唯一区分每条记录的时候,可以使用a 中的count的思路解决这个问题 rnselect emp.*,(select count(*) from emp e where e.emp_no<=emp.emp_no)rnfrom emprnorder by (select count(*) from emp e where e.emp_no<=emp.emp_no)rnrn2、纵横转换rnrna、横到纵rn有表 unknownrnname a c b drnTom 1 2 3 4rnSun 1 2 3 4rn要求以纵向格式显示rn这个最简单一直union 下来就是 rnselect name,'a' as item,a as value from unknownrnunion allrnselect name,'b' as item,b as value from unknownrnunion allrnselect name,'c' as item,c as value from unknownrnunion allrnselect name,'d' as item,d as value from unknownrnrn结果有人说了,你这个格式不符合要求,怎么一会儿Tom,一会儿 Sum 的看得我都晕了,我要按照rntom a 1rntom c 2rntom b 3rntom d 4rnSun a 1rn这样的格式排下来,你心里可能会开始骂:真多事,给你列出来不就得了,还这么多要求rn尝试:rnselect * from rn(rnselect name,'a' as item,a as value from unknownrnunion allrnselect name,'b' as item,b as value from unknownrnunion allrnselect name,'c' as item,c as value from unknownrnunion allrnselect name,'d' as item,d as value from unknownrn) view1 rnorder by name,itemrnrn或rnrnselect name,'a' as item,a as value from unknownrnunion rnselect name,'b' as item,b as value from unknownrnunion rnselect name,'c' as item,c as value from unknownrnunion rnselect name,'d' as item,d as value from unknownrnrn结果又有人挑骨头:在我的表里c是在b前面的,你怎么跑后面去了!rn想到最后,聪明的你被逼无奈最后一定会想出了很下流的一招:系统表。rnselect view1.* from rn(rnselect name,'a' as item,a as value from unknownrnunion allrnselect name,'b' as item,b as value from unknownrnunion allrnselect name,'c' as item,c as value from unknownrnunion allrnselect name,'d' as item,d as value from unknownrn) view1 ,rnsyscolumns srnwhere view1.item *= s.name where id=object_id('unknown')rnorder by view1.name,s.colidrn--注意这个*= 不是笔误,是 left outer join 的意思rna、纵到横rn这世道总有人想不开,你刚把数据转成了 unknown2rnname item valuerntom a 1rntom c 2rntom b 3rntom d 4rnSun a 1rnSun c 2rnSun b 3rnSun d 4rn又有人要你把它转回来。rn没办法,开始分析:rn转回来的意思就是行只剩下 Tom和 Sun 两个单位了,列却要多了起来,等等,列要多起来?什么时候会多起来?自然是join 了!好:rnrnselect t0.name,t1.value as 'a',t2.value as 'c',t3.value as 'b', t4.value as 'd'rnfrom rn(select distinct name from unknown2) t0,rn(select name,item from unknown2 where item='a') t1,rn(select name,item from unknown2 where item='c') t2,rn(select name,item from unknown2 where item='b') t3,rn(select name,item from unknown2 where item='d') t4rnwhere rnt0.name*=t1.name and t0.name*=t2.name and t0.name*=t3.name and t0.name*=t4.namernrn但如果用另外一个方向想,怎么才能把行压缩成两行呢?只有group by,但怎么区分在不同列里显示的不同内容呢?最好的答案是case:rn:rnselect name,rnsum(case when item='a' then value else 0 end) as a,rnsum(case when item='c' then value else 0 end) as c,rnsum(case when item='b' then value else 0 end) as b,rnsum(case when item='d' then value else 0 end) as drnfrom unknown2rngroup by namernrn至于其他再复杂的纵横转换,也就是上面几种思路的交叉而已了。rn至于如果设计 列数待定的情况则只能用过程来解决了。rn 论坛

没有更多推荐了,返回首页