mysql with rollup 多组统计 如何去掉子项汇总，保留最后条汇总？

[「已注销」]

如题，现有报表统计需求，需统计每天的工作量，但不想显示子项统计，只想保留最后一条统计数据，该如何写语句？
代码如下：

CREATE TABLE `work_log` (
  `id` int(5) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(10) DEFAULT '',
  `work_date` varchar(10) DEFAULT '',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=1;

insert into work_log(name,work_date) values 
('1000','2012-10-11'),('1000','2012-10-12'),('1000','2012-10-13'),('1000','2012-10-14'),('1001','2012-10-11'),('1001','2012-10-12'),('1001','2012-10-13')
,('1001','2012-10-14'),('1001','2012-10-16'),('1002','2012-10-11');

select name,work_date ,count(*) as counts
from work_log  
group by name,work_date
with ROLLUP;


+------+------------+--------+
| name | work_date  | counts |
+------+------------+--------+
| 1000 | 2012-10-11 |      1 |
| 1000 | 2012-10-12 |      1 |
| 1000 | 2012-10-13 |      1 |
| 1000 | 2012-10-14 |      1 |
| 1000 | NULL       |      4 |
| 1001 | 2012-10-11 |      1 |
| 1001 | 2012-10-12 |      1 |
| 1001 | 2012-10-13 |      1 |
| 1001 | 2012-10-14 |      1 |
| 1001 | 2012-10-16 |      1 |
| 1001 | NULL       |      5 |
| 1002 | 2012-10-11 |      1 |
| 1002 | NULL       |      1 |
| NULL | NULL       |     10 |
+------+------------+--------+
14 rows in set (0.03 sec)

如上，想把

1000 | NULL       |      4
1001 | NULL       |      5
1002 | NULL       |      1

这种统计行去掉不显示，只显示最下面的那条统计，改如何写语句呢？

[「已注销」]

感谢以上回答！~

[ACMAIN_CHM]

select name,work_date ,count(*) as counts
from work_log  
group by name,work_date
having isnull(name) xor isnull(work_date)=0
with ROLLUP;

[WWWWA]

select *from (select name,work_date ,count(*) as counts from work_log  group by name,work_date with ROLLUP) b where name is not null and work_date is null and counts is not null

[nicenight]

这样也行：

select name,work_date ,count(*) as counts
from work_log
group by name,work_date
union 
select null,null,count(*) as counts
from work_log

[mysdzlt2007]

后边加having条件也行

HAVING (`name` IS NOT NULL AND work_date IS NOT NULL) OR (`name` IS NULL AND work_date IS NULL)

[nicenight]

引用 5 楼 ACMAIN_CHM 的回复:

SQL code12345select name,work_date ,count(*) as countsfrom work_log  group by name,work_datehaving isnull(name) xor isnull(work_date)=0with ROLLUP;

我从来没用过 xor 这个关系符！学习学习，哈哈。

[「已注销」]

引用 5 楼 ACMAIN_CHM 的回复:

SQL code? 12345 select name,work_date ,count(*) as counts from work_log  group by name,work_date having isnull(name) xor isnull(work_date)=0 with ROLLUP;

额 俺也没整过 XOR 第一次看到这个词， 上面的语句改成

select name,work_date ,count(*) as counts 
from work_log 
group by name,work_date 
with ROLLUP 
having isnull(name) xor isnull(work_date)=0 

可以正常运行