求取相同日期用户名重复的且费用不为0的记录

[wuszh1688]

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

[WWWWA]

为什么没有
('b', 6, '2011-06-01'),
('b', 20, '2011-06-01'),

[wuszh1688]

哈，不好意思，我这条记录是删除了的

[WWWWA]

详细说明，
并且money不等于0的记录：是money总和还是每个money不等于0

[wuszh1688]

应该是这 样：

INSERT INTO `wsz` (`name`, `money`, `time`) VALUES ('a', 10, '2011-05-01'),
('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-01

b 20 2011-06-01
b 20 2011-06-01

[WWWWA]

SELECT * FROM wsz a WHERE NOT EXISTS(SELECT 1 FROM wsz WHERE a.`name`=`name` AND money=0) AND
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`)

[vipper23]

select a.* from wsz a ,(select * from wsz where time = '2011-06-01' and money<>0 group by name having count(name)>1) b where a.name=b.name and a.time=b.time;
+------+-------+------------+
| name | money | time       |
+------+-------+------------+
| a    |    10 | 2011-06-01 |
| a    |    10 | 2011-06-01 |
| b    |    20 | 2011-06-01 |
| b    |    20 | 2011-06-01 |
+------+-------+------------+