A表:
CREATE TABLE `a` (
`aid` int(11) NOT NULL,
`astate` int(1) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;INSERT INTO `a` (`aid`, `astate`) VALUES
(1, 1),
(2, 1),
(3, 1);
B表:
CREATE TABLE `b` (
`bid` int(11) NOT NULL,
`bfield` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;INSERT INTO `b` (`bid`, `bfield`) VALUES
(1, 'f1 '),
(2, 'f2 '),
(3, 'f3 '),
(4, 'f4 ');C表:
CREATE TABLE `c` (
`cid` int(11) NOT NULL,
`aid` int(11) NOT NULL,
`bid` int(11) NOT NULL,
`cvalue` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;INSERT INTO `c` (`cid`, `aid`, `bid`, `cvalue`) VALUES
(1, 1, 1, 'v11 '),
(2, 1, 2, 'v12 '),
(3, 1, 3, 'v13 '),
(4, 2, 1, 'v31 '),
(5, 2, 2, 'v22 '),
(6, 3, 1, 'v21 ');
现有语句:
View:CREATE VIEW qd AS
(SELECT a.aid, b.bid,b.bfield,c.cvalue
FROM (a, b )
LEFT JOIN c
ON (a.aid = c.aid AND b.bid = c.bid)
ORDER BY a.aid ASC, b.bid ASC );SELECT a.* FROM qd a
LEFT JOIN
(
SELECT a.aid,a.ma,COUNT(b.aid) AS dd FROM (SELECT aid,MAX(cvalue) AS ma FROM qd GROUP BY aid) a
LEFT JOIN (SELECT aid,MAX(cvalue) AS ma FROM qd GROUP BY aid) b
ON a.ma>=b.ma
GROUP BY a.aid,a.ma
ORDER BY COUNT(b.aid)) d
ON a.aid=d.aid
ORDER BY d.dd ;希望能满足情况:按bfield = 'f1':cvalue 从大到小排
+-----+-----+--------+--------+
| aid | bid | bfield | cvalue |
+-----+-----+--------+--------+
| 2 | 1 | f1 | v31 |
| 2 | 2 | f2 | v22 |
| 2 | 3 | f3 | NULL |
| 2 | 4 | f4 | NULL |
| 3 | 1 | f1 | v21 |
| 3 | 2 | f2 | NULL |
| 3 | 3 | f3 | NULL |
| 3 | 4 | f4 | NULL |
| 1 | 1 | f1 | v11 |
| 1 | 2 | f2 | v12 |
| 1 | 3 | f3 | v13 |
| 1 | 4 | f4 | NULL |
+-----+-----+--------+--------+按bfield = 'f2':cvalue 从大到小排
+-----+-----+--------+--------+
| aid | bid | bfield | cvalue |
+-----+-----+--------+--------+
| 2 | 1 | f1 | v31 |
| 2 | 2 | f2 | v22 |
| 2 | 3 | f3 | NULL |
| 2 | 4 | f4 | NULL |
| 1 | 1 | f1 | v11 |
| 1 | 2 | f2 | v12 |
| 1 | 3 | f3 | v13 |
| 1 | 4 | f4 | NULL |
| 3 | 1 | f1 | v21 |
| 3 | 2 | f2 | NULL |
| 3 | 3 | f3 | NULL |
| 3 | 4 | f4 | NULL |
+-----+-----+--------+--------+按bfield = 'f3':cvalue 从大到小排
+-----+-----+--------+--------+
| aid | bid | bfield | cvalue |
+-----+-----+--------+--------+
| 1 | 1 | f1 | v11 |
| 1 | 2 | f2 | v12 |
| 1 | 3 | f3 | v13 |
| 1 | 4 | f4 | NULL |
| 2 | 1 | f1 | v31 |
| 2 | 2 | f2 | v22 |
| 2 | 3 | f3 | NULL |
| 2 | 4 | f4 | NULL |
| 3 | 1 | f1 | v21 |
| 3 | 2 | f2 | NULL |
| 3 | 3 | f3 | NULL |
| 3 | 4 | f4 | NULL |
+-----+-----+--------+--------+
语句里需要两个参数是灵活的(因为程序里带参数进去查询), 就是bfield的项 和从大到小或从小到大(MAX|MIN)||(DESC|ASC)
CREATE TABLE `a` (
`aid` int(11) NOT NULL,
`astate` int(1) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;INSERT INTO `a` (`aid`, `astate`) VALUES
(1, 1),
(2, 1),
(3, 1);
B表:
CREATE TABLE `b` (
`bid` int(11) NOT NULL,
`bfield` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;INSERT INTO `b` (`bid`, `bfield`) VALUES
(1, 'f1 '),
(2, 'f2 '),
(3, 'f3 '),
(4, 'f4 ');C表:
CREATE TABLE `c` (
`cid` int(11) NOT NULL,
`aid` int(11) NOT NULL,
`bid` int(11) NOT NULL,
`cvalue` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;INSERT INTO `c` (`cid`, `aid`, `bid`, `cvalue`) VALUES
(1, 1, 1, 'v11 '),
(2, 1, 2, 'v12 '),
(3, 1, 3, 'v13 '),
(4, 2, 1, 'v31 '),
(5, 2, 2, 'v22 '),
(6, 3, 1, 'v21 ');
现有语句:
View:CREATE VIEW qd AS
(SELECT a.aid, b.bid,b.bfield,c.cvalue
FROM (a, b )
LEFT JOIN c
ON (a.aid = c.aid AND b.bid = c.bid)
ORDER BY a.aid ASC, b.bid ASC );SELECT a.* FROM qd a
LEFT JOIN
(
SELECT a.aid,a.ma,COUNT(b.aid) AS dd FROM (SELECT aid,MAX(cvalue) AS ma FROM qd GROUP BY aid) a
LEFT JOIN (SELECT aid,MAX(cvalue) AS ma FROM qd GROUP BY aid) b
ON a.ma>=b.ma
GROUP BY a.aid,a.ma
ORDER BY COUNT(b.aid)) d
ON a.aid=d.aid
ORDER BY d.dd ;希望能满足情况:按bfield = 'f1':cvalue 从大到小排
+-----+-----+--------+--------+
| aid | bid | bfield | cvalue |
+-----+-----+--------+--------+
| 2 | 1 | f1 | v31 |
| 2 | 2 | f2 | v22 |
| 2 | 3 | f3 | NULL |
| 2 | 4 | f4 | NULL |
| 3 | 1 | f1 | v21 |
| 3 | 2 | f2 | NULL |
| 3 | 3 | f3 | NULL |
| 3 | 4 | f4 | NULL |
| 1 | 1 | f1 | v11 |
| 1 | 2 | f2 | v12 |
| 1 | 3 | f3 | v13 |
| 1 | 4 | f4 | NULL |
+-----+-----+--------+--------+按bfield = 'f2':cvalue 从大到小排
+-----+-----+--------+--------+
| aid | bid | bfield | cvalue |
+-----+-----+--------+--------+
| 2 | 1 | f1 | v31 |
| 2 | 2 | f2 | v22 |
| 2 | 3 | f3 | NULL |
| 2 | 4 | f4 | NULL |
| 1 | 1 | f1 | v11 |
| 1 | 2 | f2 | v12 |
| 1 | 3 | f3 | v13 |
| 1 | 4 | f4 | NULL |
| 3 | 1 | f1 | v21 |
| 3 | 2 | f2 | NULL |
| 3 | 3 | f3 | NULL |
| 3 | 4 | f4 | NULL |
+-----+-----+--------+--------+按bfield = 'f3':cvalue 从大到小排
+-----+-----+--------+--------+
| aid | bid | bfield | cvalue |
+-----+-----+--------+--------+
| 1 | 1 | f1 | v11 |
| 1 | 2 | f2 | v12 |
| 1 | 3 | f3 | v13 |
| 1 | 4 | f4 | NULL |
| 2 | 1 | f1 | v31 |
| 2 | 2 | f2 | v22 |
| 2 | 3 | f3 | NULL |
| 2 | 4 | f4 | NULL |
| 3 | 1 | f1 | v21 |
| 3 | 2 | f2 | NULL |
| 3 | 3 | f3 | NULL |
| 3 | 4 | f4 | NULL |
+-----+-----+--------+--------+
语句里需要两个参数是灵活的(因为程序里带参数进去查询), 就是bfield的项 和从大到小或从小到大(MAX|MIN)||(DESC|ASC)
解决方案 »
- 写存储过程时可否在Select 里再套Select?
- 错误[MySQL][ODBC 3.51 Driver]Lost connection to MySQL server during query
- 求个UPDATE的SQL语句,把一行信息的2个字段增加到一个字段里
- 怎么去掉MESSAGE字段里的4个字
- 将MySQL5.0下的数据导出后再导入到MySQL3.23中出错,不能导入!
- 怎样才能让mysql的id自动增量从1开始.
- 我是新手,请问我安装好mysql后,下一步改怎样操作?怎样建立数据库?以及我的默认超级用户名是什么?
- VC使用mysql时编译是出错!
- D6中如何用DBEXPRESS连接到LINUX上的MYSQL?
- 无法定位序数3283于动态数据链接库libmysql.dll上
- 'Table 'aaa.user_tmp也属于 复制表?
- mysql数据库转移服务器后乱码
SELECT a.* FROM qd a LEFT JOIN
(SELECT a.aid,a.ma,COUNT(b.aid) AS dd FROM
(SELECT aid,COALESCE(MAX(cvalue),0) AS ma FROM qd WHERE bfield='f3' GROUP BY aid) a
LEFT JOIN (SELECT aid,COALESCE(MAX(cvalue),0) AS ma FROM qd WHERE bfield='f3' GROUP BY aid) b
ON a.ma>=b.ma GROUP BY a.aid,a.ma ORDER BY COUNT(b.aid)) d ON a.aid=d.aid ORDER BY d.dd DESC;用SP传参数,将 bfield='f3'中的F3修改为F2、F1即可
用0填满字段
如V001 V002....
(不要高估你的汉语表达能力或者我的汉语理解能力)
建议你列出你的表结构,并提供测试数据以及基于这些测试数据的所对应正确结果。
参考一下这个贴子的提问方式http://topic.csdn.net/u/20091130/20/8343ee6a-417c-4c2d-9415-fa46604a00cf.html
1. 你的 create table xxx .. 语句
2. 你的 insert into xxx ... 语句
3. 结果是什么样,(并给以简单的算法描述)
4. 你用的数据库名称和版本(经常有人在MS SQL server版问 MySQL)
这样想帮你的人可以直接搭建和你相同的环境,并在给出方案前进行测试,避免文字描述理解上的误差。