CREATE TABLE `wsz` (
`name` char(2) NOT NULL,
`money` int(11) NOT NULL,
`time` date NOT NULL,
KEY `name` (`name`,`money`,`time`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;#
# 导出表中的数据 `wsz`
#INSERT INTO `wsz` (`name`, `money`, `time`) VALUES ('a', 10, '2011-05-01'),
('a', 10, '2011-06-01'),
('a', 10, '2011-06-01'),
('b', 6, '2011-06-01'),
('b', 20, '2011-06-01'),
('c', 0, '2011-06-01'),
('c', 10, '2011-06-01');--------------
取出时间为2011-06-01的name重复的,并且money不等于0的记录
结果:a 10 2011-06-01
a 10 2011-06-01
`name` char(2) NOT NULL,
`money` int(11) NOT NULL,
`time` date NOT NULL,
KEY `name` (`name`,`money`,`time`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;#
# 导出表中的数据 `wsz`
#INSERT INTO `wsz` (`name`, `money`, `time`) VALUES ('a', 10, '2011-05-01'),
('a', 10, '2011-06-01'),
('a', 10, '2011-06-01'),
('b', 6, '2011-06-01'),
('b', 20, '2011-06-01'),
('c', 0, '2011-06-01'),
('c', 10, '2011-06-01');--------------
取出时间为2011-06-01的name重复的,并且money不等于0的记录
结果:a 10 2011-06-01
a 10 2011-06-01
解决方案 »
- 用MySQL.Data.dll操作MySQL数据库时,自动添加换行符“\”的问题
- 实现下面功能的select语句(mysql),究竟该如何写呢?
- sql能否做到下面例子的link
- mysql 「Ungultige Zeigeroperation」 是什么错误?
- 请问MYSQL数据库的org.gjt.mm.mysql.driver应怎样设置才能连接得上MYSQL数据库
- 如何将autoincrease字段回复到开始0
- 请问有谁知道在WIN2K下使用免安装的MySQL怎么设置啊?
- MYSQL中BLOB字段的思路
- 求教关于MYSQL MD5函数的问题,谢谢
- 大侠救急:如何在Redhat Linux7.2下配置php4和mySQL?
- sql
- MS-SQL转MYSQL的工具
并且money不等于0的记录:是money总和还是每个money不等于0
('a', 10, '2011-06-01'),
('a', 10, '2011-06-01'),
('b', 20, '2011-06-01'),
('b', 20, '2011-06-01'),
('c', 0, '2011-06-01'),
('c', 10, '2011-06-01');取出时间为2011-06-01的name重复的,并且money不等于0的记录
结果:a 10 2011-06-01
a 10 2011-06-01b 20 2011-06-01
b 20 2011-06-01
DATE(a.TIME)='2011-06-01'
AND
10<=(SELECT SUM(money) FROM wsz WHERE a.`name`=`name`)
AND
2<=(SELECT COUNT(*) FROM wsz WHERE a.`name`=`name`)
+------+-------+------------+
| name | money | time |
+------+-------+------------+
| a | 10 | 2011-06-01 |
| a | 10 | 2011-06-01 |
| b | 20 | 2011-06-01 |
| b | 20 | 2011-06-01 |
+------+-------+------------+