问题描述:
数据库有10个字段,a1-a10,已知这些字段的值为1-500中数字,且每条数据无重复值
数据格式例子如下:
id a1 a2 a3 a4 a5 a6 a7 a8 a9 a10
(自动) 1 2 4 6 100 84 5 2 5 12
(自动) 12 32 44 36 101 85 6 7 8 122
(自动) ............
如何统计出a1-a9中这些数字出现频率前3的数字呢?我使用的是PHP,希望在MYSQL中能够实现上面的功能
数据库有10个字段,a1-a10,已知这些字段的值为1-500中数字,且每条数据无重复值
数据格式例子如下:
id a1 a2 a3 a4 a5 a6 a7 a8 a9 a10
(自动) 1 2 4 6 100 84 5 2 5 12
(自动) 12 32 44 36 101 85 6 7 8 122
(自动) ............
如何统计出a1-a9中这些数字出现频率前3的数字呢?我使用的是PHP,希望在MYSQL中能够实现上面的功能
只要找出a1中的数字出现的次数最多的就行把?类似
select a1,count(a1) as c from table group by a1 limit 0,3
union all
select a2,count(a2) as c from table group by a1 limit 0,3.....
我是要求1-500中间的数字在 a1-a9中 出现次数最高的3个数字(a1-a9)
from (
select id,a1
from yourTable
union
select id,a2
from yourTable
union
select id,a3
from yourTable
union
select id,a4
from yourTable
union
select id,a5
from yourTable
union
select id,a6
from yourTable
union
select id,a7
from yourTable
union
select id,a8
from yourTable
union
select id,a9
from yourTable
union
select id,a10
from yourTable
)
group by a1
order by 1 desc
limit 3[align=center]==== 思想重于技巧 ====
[/align]
#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 ')
group by no1
order by 1 desc
limit 3' at line 9
select no1,count(*)
from (
select id,no1 from cp union
select id,no2 from cp union
select id,no3 from cp union
select id,no4 from cp union
select id,no5 from cp union
select id,no6 from cp union
)
group by no1
order by 1 desc
limit 3
from (
select id,no1 as no from cp union
select id,no2 as no from cp union
select id,no3 as no from cp union
select id,no4 as no from cp union
select id,no5 as no from cp union
select id,no6 as no from cp union
)
group by no
order by 1 desc
limit 3
from (
select id,no1 from cp union
select id,no2 from cp union
select id,no3 from cp union
select id,no4 from cp union
select id,no5 from cp union
select id,no6 from cp union
) as a
group by no1
order by 1 desc
limit 3少了个别名。[align=center]==== 思想重于技巧 ====
[/align]
from (
select id,no1 from cp union
select id,no2 from cp union
select id,no3 from cp union
select id,no4 from cp union
select id,no5 from cp union
select id,no6 from cp union
select id,no7 from cp union
select id,no8 from cp union
select id,no9 from cp
) as a
group by no1
order by no1 desc
limit 3
select no,count(*)
from (
select id,no1 as no from cp union
select id,no2 as no from cp union
select id,no3 as no from cp union
select id,no4 as no from cp union
select id,no5 as no from cp union
select id,no6 as no from cp union
)
group by no
order by 1 desc
limit 3MySQL 返回: #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 ')
group by no
order by 1 desc
limit 3' at line 9 直接套用liuyann代码
select no1,count(*)
from (
select id,no1 from cp union
select id,no2 from cp union
select id,no3 from cp union
select id,no4 from cp union
select id,no5 from cp union
select id,no6 from cp union
) as a
group by no1
order by 1 desc
limit 3
#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
group by no1
order by 1 desc
limit 3' at line 9 直接套用wwwwb的代码
select no1,count(*)
from (
select id,no1 from cp union
select id,no2 from cp union
select id,no3 from cp union
select id,no4 from cp union
select id,no5 from cp union
select id,no6 from cp union
select id,no7 from cp union
select id,no8 from cp union
select id,no9 from cp
) as a
group by no1
order by no1 desc
limit 3#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
group by no1
order by 1 desc
limit 3' at line 9
[/align]
+-----------+
| version() |
+-----------+
| 4.1.20 |
+-----------+
1 row in set (0.00 sec)
[align=center]==== 思想重于技巧 ====
[/align]
-> (
-> id INT AUTO_INCREMENT PRIMARY KEY ,
-> a1 INT,
-> a2 INT,
-> a3 INT,
-> a4 INT,
-> a5 INT,
-> a6 INT,
-> a7 INT,
-> a8 INT,
-> a9 INT,
-> a10 INT
-> );
Query OK, 0 rows affected (0.03 sec)mysql> insert into cp (a1,a2,a3,a4,a5,a6,a7,a8,a9,a10)
-> values(1,2,4,6,100,84,5,2,5,12);
Query OK, 1 row affected (0.00 sec)mysql> insert into cp (a1,a2,a3,a4,a5,a6,a7,a8,a9,a10)
-> values(12,32,44,36,101,85,6,7,8,12);
Query OK, 1 row affected (0.00 sec)mysql> select a1,count(*)
-> from (
-> select id,a1
-> from cp
-> union
-> select id,a2
-> from cp
-> union
-> select id,a3
-> from cp
-> union
-> select id,a4
-> from cp
-> union
-> select id,a5
-> from cp
-> union
-> select id,a6
-> from cp
-> union
-> select id,a7
-> from cp
-> union
-> select id,a8
-> from cp
-> union
-> select id,a9
-> from cp
-> union
-> select id,a10
-> from cp
-> ) as a
-> group by a1
-> order by 2 desc
-> limit 3;
+------+----------+
| a1 | count(*) |
+------+----------+
| 12 | 2 |
| 6 | 2 |
| 84 | 1 |
+------+----------+
3 rows in set (0.00 sec)mysql>[align=center]==== 思想重于技巧 ====
[/align]
如果您问题已经得解决,请您及时结帖给分,以感谢帮助您的朋友。 结帖方法:点击版面右上方或右下方 [管理] ,进入页面后就可以输入密码,分别给分,结帖。
或参考:
http://www.csdn.net/help/over.asp
http://topic.csdn.net/u/20080110/19/7cb462f1-cac6-4c28-848e-0a879f4fd642.html
=============================================================================
问题解决,请及时结贴。
正确结贴方法:
管理帖子-->给分-->输入密码-->结贴[align=center]==== 思想重于技巧 ====
[/align]
我的MYSQL是win版的
localhost
服务器版本: 5.0.45-community-nt-log
Protocol version: 10
服务器: localhost via TCP/IP
用户: root@localhost
MySQL 字符集: UTF-8 Unicode (utf8)
[/align]
字段 类型 整理 属性 Null 默认 额外 操作
id int(11) 否 auto_increment
no1 int(11) 否
no2 int(11) 否
no3 int(11) 否
no4 int(11) 否
no5 int(11) 否
no6 int(9) 否
no7 int(11) 否
这是我的表的结构
ERROR 1064 (42000): 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
group by no1
order by 2 desc
limit 3' at line 21
[/align]
-- version 2.10.2
-- http://www.phpmyadmin.net
--
-- 主机: localhost
-- 生成日期: 2008 年 05 月 07 日 08:15
-- 服务器版本: 5.0.45
-- PHP 版本: 5.2.3SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";--
-- 数据库: `action`
-- -- ----------------------------------------------------------
-- 表的结构 `cp`
-- CREATE TABLE `cp` (
`id` int(11) NOT NULL auto_increment,
`no1` int(11) NOT NULL,
`no2` int(11) NOT NULL,
`no3` int(11) NOT NULL,
`no4` int(11) NOT NULL,
`no5` int(11) NOT NULL,
`no6` int(9) NOT NULL,
`no7` int(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=724
SQL
select no1,count(*)
from (
select id,no1 from cp union
select id,no2 from cp union
select id,no3 from cp union
select id,no4 from cp union
select id,no5 from cp union
select id,no6 from cp union
) as a
group by no1
order by id desc
limit 3
from (
select id,no1 from cp union
select id,no2 from cp union
select id,no3 from cp union
select id,no4 from cp union
select id,no5 from cp union
select id,no6 from cp
) as a
group by no1
order by id desc
limit 3
多了一个UNION
from (
select id,no1 from cp union
select id,no2 from cp union
select id,no3 from cp union
select id,no4 from cp union
select id,no5 from cp union
select id,no6 from cp
) as a
group by no1
order by id desc
limit 3
多了一个UNION
SQL:
select a1,count(*)
from (
select id,a1 from cp2 union
select id,a2 from cp2 union
select id,a3 from cp2 union
select id,a4 from cp2 union
select id,a5 from cp2 union
) as a
group by a1
order by id desc
limit 3 -- phpMyAdmin SQL Dump
-- version 2.10.2
-- http://www.phpmyadmin.net
--
-- 主机: localhost
-- 生成日期: 2008 年 05 月 07 日 08:24
-- 服务器版本: 5.0.45
-- PHP 版本: 5.2.3SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";--
-- 数据库: `action`
-- -- ----------------------------------------------------------
-- 表的结构 `cp2`
-- CREATE TABLE `cp2` (
`id` int(11) NOT NULL auto_increment,
`a1` int(11) default NULL,
`a2` int(11) default NULL,
`a3` int(11) default NULL,
`a4` int(11) default NULL,
`a5` int(11) default NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=6 ;--
-- 导出表中的数据 `cp2`
-- INSERT INTO `cp2` VALUES (1, 1, 2, 3, 4, 5);
INSERT INTO `cp2` VALUES (2, 1, 2, 35, 4, 5);
INSERT INTO `cp2` VALUES (3, 1, 22, 35, 4, 5);
INSERT INTO `cp2` VALUES (4, 1, 22, 35, 4, 55);
INSERT INTO `cp2` VALUES (5, 1, 2, 5, 4, 5);
select a1,count(*)
from (
select id,a1 from cp2 union
select id,a2 from cp2 union
select id,a3 from cp2 union
select id,a4 from cp2 union
select id,a5 from cp2
) as a
group by a1
order by id desc
limit 3
耽误了俩位大虾的时间,非常过意不去.已经得到我想要的答案了..最后非常的谢谢
另外这里如何送分呢.次帖分数不多大家却如此耐心.我想略表下心意
from (
select id,no1 from cp union
select id,no2 from cp union
select id,no3 from cp union
select id,no4 from cp union
select id,no5 from cp union
select id,no6 from cp union
select id,no7 from cp
) as a
group by no1
order by id desc
limit 3 老兄你的SQL不对,不是照我的抄的啊,你改了些东西,少了个no7
上面语句测试过OK.[align=center]==== 思想重于技巧 ====
[/align]