CREATE TABLE IF NOT EXISTS `mj_knowledge` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`time` int(11) NOT NULL,
`subject` varchar(200) NOT NULL,
`uname` varchar(15) NOT NULL,
`email` varchar(20) NOT NULL,
`attached_file` varchar(120) NOT NULL,
`type` tinyint(4) NOT NULL,
`sort` int(11) NOT NULL,
`content` text NOT NULL,
`status` tinyint(4) NOT NULL DEFAULT '2',
`uid` char(20) NOT NULL,
`FAQ_inserted` int(11) NOT NULL DEFAULT '2',
`stick` tinyint(4) NOT NULL DEFAULT '1',
`priority` varchar(10) NOT NULL DEFAULT 'normal',
`charge` varchar(20) NOT NULL,
PRIMARY KEY (`id`),
FULLTEXT KEY `subject` (`subject`),
FULLTEXT KEY `content` (`content`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=22676 ;
我想要得到不同uid的stick总数并按照stick排序?要怎么写。。求教
`id` int(11) NOT NULL AUTO_INCREMENT,
`time` int(11) NOT NULL,
`subject` varchar(200) NOT NULL,
`uname` varchar(15) NOT NULL,
`email` varchar(20) NOT NULL,
`attached_file` varchar(120) NOT NULL,
`type` tinyint(4) NOT NULL,
`sort` int(11) NOT NULL,
`content` text NOT NULL,
`status` tinyint(4) NOT NULL DEFAULT '2',
`uid` char(20) NOT NULL,
`FAQ_inserted` int(11) NOT NULL DEFAULT '2',
`stick` tinyint(4) NOT NULL DEFAULT '1',
`priority` varchar(10) NOT NULL DEFAULT 'normal',
`charge` varchar(20) NOT NULL,
PRIMARY KEY (`id`),
FULLTEXT KEY `subject` (`subject`),
FULLTEXT KEY `content` (`content`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=22676 ;
我想要得到不同uid的stick总数并按照stick排序?要怎么写。。求教
select uid, sum(stick) as total
from mj_knowledge
group by uid
order by total
from mj_knowledge WHERE stick=2
group by uid
order by total
`id` int(11) NOT NULL AUTO_INCREMENT,
`time` int(11) NOT NULL,
`subject` varchar(200) NOT NULL,
`uname` varchar(15) NOT NULL,
`email` varchar(20) NOT NULL,
`attached_file` varchar(120) NOT NULL,
`type` tinyint(4) NOT NULL,
`sort` int(11) NOT NULL,
`content` text NOT NULL,
`status` tinyint(4) NOT NULL DEFAULT '2',
`uid` char(20) NOT NULL,
`FAQ_inserted` int(11) NOT NULL DEFAULT '2',
`stick` tinyint(4) NOT NULL DEFAULT '1',
`priority` varchar(10) NOT NULL DEFAULT 'normal',
`charge` varchar(20) NOT NULL,
PRIMARY KEY (`id`),
FULLTEXT KEY `subject` (`subject`),
FULLTEXT KEY `content` (`content`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=22676 ;--
-- Dumping data for table `mj_knowledge`
--INSERT INTO `mj_knowledge` (`id`, `time`, `subject`, `uname`, `email`, `attached_file`, `type`, `sort`, `content`, `status`, `uid`, `FAQ_inserted`, `stick`, `priority`, `charge`) VALUES
(22656, 2012071615, 'abc', '', '', '', 0, 7, 'sdf', 1, 'U2012061200000000185', 2, 1, 'normal', 'etadmin'),
(22640, 2343, 'sdfdfdsf', '', '', '', 8, 7, '', 2, '4535', 2, 2, 'urgent', 'CoS_shanmao'),
(22642, 1341560293, 'thin thkdf thin thkdf thin thkdf thin thkdf thin thkdf thin thkdf thin thkdf thin thkdf thin thkdf ', '', '', '', 0, 7, 'sdfsdf', 1, 'U2012061200000000185', 2, 1, 'normal', 'faker'),
(22659, 23432, 'sdfdsf', '', '', '', 2, 1, 'sdfdsf', 2, '', 2, 1, 'normal', ''),
(22645, 1341563056, 'abdefsdfdsabdefsdfdsabdefsdfdsabdefsdfdsabdefsdfds', '', '', '', 0, 7, '撒旦发大水发', 2, 'U2012061200000000185', 2, 1, 'normal', ''),
(22646, 1341563238, 'asfd dfds asfd dfds asfd dfds asfd dfds asfd dfds', '', '', '', 0, 8, '撒旦发大水发', 1, 'U2012061200000000185', 2, 2, 'normal', ''),
(22650, 1341814074, 'this', '', '', '', 0, 7, 'test', 2, 'U2012061200000000185', 2, 1, 'normal', ''),
(22651, 1341814094, 'this', '', '', '', 0, 7, 'test', 2, 'U2012061200000000185', 2, 1, 'normal', ''),
(22652, 1341814254, 'sdf ', '', '', '', 0, 7, 'sdfdsf', 2, 'U2012061200000000185', 2, 1, 'normal', ''),
(22653, 1341816025, 'dsfd', '', '', '', 0, 7, 'dsf', 2, 'U2012061200000000185', 2, 2, 'normal', ''),
(22654, 1341816084, 'sdf', '', '', '', 0, 7, 'sdfdsf', 2, 'U2012061200000000185', 2, 1, 'normal', ''),
(22657, 1341889302, 'abc', '', '', '', 0, 7, 'eee', 2, 'U2012061200000000185', 2, 1, 'normal', ''),
(22658, 1341903480, 'sdfd', '', '', '', 0, 7, 'sdfdsf', 2, 'U2012061200000000185', 2, 1, 'normal', ''),
(22672, 23432, 'sdfdsf', '', '', '', 2, 1, 'sdfdsf', 2, '1111111', 2, 2, 'normal', ''),
(22673, 23432, 'sdfdsf', '', '', '', 2, 1, 'sdfdsf', 2, '1111111', 2, 2, 'normal', ''),
(22674, 23432, 'sdfdsf', '', '', '', 2, 1, 'sdfdsf', 2, '1111111', 2, 2, 'normal', ''),
(22675, 23432, 'sdfdsf', '', '', '', 2, 1, 'sdfdsf', 2, '1111111', 2, 2, 'normal', '');给出建表语句和数据
方便各位大大测试。。
-> from mj_knowledge WHERE stick=2
-> group by uid
-> order by total;
+----------------------+-------+
| uid | total |
+----------------------+-------+
| 4535 | 2 |
| U2012061200000000185 | 4 |
| 1111111 | 8 |
+----------------------+-------+
3 rows in set (0.00 sec)
mysql> select sum(stick) from mj_knowledge;
+------------+
| sum(stick) |
+------------+
| 24 |
+------------+
1 row in set (0.00 sec)
mysql> select stick from mj_knowledge where stick=2;
+-------+
| stick |
+-------+
| 2 |
| 2 |
| 2 |
| 2 |
| 2 |
| 2 |
| 2 |
+-------+
7 rows in set (0.00 sec)
mysql> select sum(stick) from mj_knowledge where stick=2;mysql> select a.uid, a.stick from
-> (
-> select uid uid,sum(stick) stick from mj_knowledge
-> where stick=2
-> group by uid
-> ) a
-> order by a.stick;
+----------------------+-------+
| uid | stick |
+----------------------+-------+
| 4535 | 2 |
| U2012061200000000185 | 4 |
| 1111111 | 8 |
+----------------------+-------+
3 rows in set (0.00 sec)
数量都对得上啊。14
+------------+
| sum(stick) |
+------------+
| 14 |
+------------+
1 row in set (0.00 sec)
-> where stick=2
-> ;
+----------------------+-------+
| uid | stick |
+----------------------+-------+
| 4535 | 2 |
| U2012061200000000185 | 2 |
| U2012061200000000185 | 2 |
| 1111111 | 2 |
| 1111111 | 2 |
| 1111111 | 2 |
| 1111111 | 2 |
+----------------------+-------+
7 rows in set (0.00 sec)楼主 你说的是总数 所以大家用的sum()而 你的 1 2 4 是 count()