我给你一个思路,select hm, count(*) from ( select hm1 as hm from table1 union select hm2 as hm from table1 union select hm3 as hm from table1 ) group by hm
这样也可以 如果数据量大,它的效率要高 select hm,sum(amoun) as 出现次数 from (select hm1 as hm,count(Hm1) as amount from 表 group by hm1 union select hm2 as hm,count(Hm2) from 表 group by hm2 union select hm3 as hm,count(Hm3) from 表 group by hm3) a group by a,hm
建一個臨時表table_l(只有一個字段hm); insert into table_l select hm1 from table insert into table_l select hm2 from table insert into table_l select hm3 from tableselect hm,count(hm) from table_l group by hm
楼上(耙子)兄的语法应该是对的 可系统回出现 invalid use of keywrod token: select line number:3 这是这么回事啊?
to: pazee(耙子) union会自动distinct的,要使用union allselect hm, count(*) from ( select hm1 as hm from table1 union all select hm2 as hm from table1 union all select hm3 as hm from table1 ) group by hm
实验过了, union all有什么用处啊 可以了,我刚刚弄完,可以出结果了,谢谢了,哈,虽然我不是楼主, 学到了点东西!! 楼住啊,可以结分了!!
union 是什么用的,能不能教教我
改进耙子的程序 原: select hm, count(*) from ( select hm1 as hm from table1 union select hm2 as hm from table1 union select hm3 as hm from table1 ) group by hm 改:select A.hm, count(A.hm) As Itm from ( select hm1 as hm from table1 union all select hm2 as hm from table1 union all select hm3 as hm from table1 ) A group by A.hm一切OK! 耙子提醒提对了.
怎么我用这段代码 select hm, count(*) from ( select hm1 as hm from table1 union all select hm2 as hm from table1 union all select hm3 as hm from table1 ) group by hm出错啊,说groupby有错啊~~~~~~~~~~~~~~~~~~~~~
哦~我懂了~ 他的原理就是把所有的数据都选出来,(形成9个数据的集合),然后在从这9个数据中 实现我们想要的咚咚(group by hm),按值分组~union的意思就是连接,及把上下两个选择结果组合在一起,如果不加all,默认是distinct的~爽啊~!
from
(
select hm1 as hm from table1
union
select hm2 as hm from table1
union
select hm3 as hm from table1
)
group by hm
如果数据量大,它的效率要高
select hm,sum(amoun) as 出现次数
from
(select hm1 as hm,count(Hm1) as amount
from 表
group by hm1
union
select hm2 as hm,count(Hm2)
from 表
group by hm2
union
select hm3 as hm,count(Hm3)
from 表
group by hm3) a
group by a,hm
insert into table_l select hm1 from table
insert into table_l select hm2 from table
insert into table_l select hm3 from tableselect hm,count(hm) from table_l group by hm
可系统回出现 invalid use of keywrod
token: select
line number:3
这是这么回事啊?
我想问下,union是不是有问题啊,当查询出结果的时候,有重复的,系统就会自动合并,所以,统计不出各个值的个数,所以,按你们查询的方法,查出的总是 1,
不知道大家还有什么方法,我也想知道!
不过就是要建个表Table_1,里面有个字段hm是不是啊?
union会自动distinct的,要使用union allselect hm, count(*)
from
(
select hm1 as hm from table1
union all
select hm2 as hm from table1
union all
select hm3 as hm from table1
)
group by hm
union all有什么用处啊
可以了,我刚刚弄完,可以出结果了,谢谢了,哈,虽然我不是楼主,
学到了点东西!!
楼住啊,可以结分了!!
原:
select hm, count(*)
from
(
select hm1 as hm from table1
union
select hm2 as hm from table1
union
select hm3 as hm from table1
)
group by hm
改:select A.hm, count(A.hm) As Itm
from
(
select hm1 as hm from table1
union all
select hm2 as hm from table1
union all
select hm3 as hm from table1
) A
group by A.hm一切OK! 耙子提醒提对了.
select hm, count(*)
from
(
select hm1 as hm from table1
union all
select hm2 as hm from table1
union all
select hm3 as hm from table1
)
group by hm出错啊,说groupby有错啊~~~~~~~~~~~~~~~~~~~~~
他的原理就是把所有的数据都选出来,(形成9个数据的集合),然后在从这9个数据中
实现我们想要的咚咚(group by hm),按值分组~union的意思就是连接,及把上下两个选择结果组合在一起,如果不加all,默认是distinct的~爽啊~!