我有表: terminal_status_log(
terminal_id varchar(30);
online varchar(10);
logdate date
)
现通过sql语句查询: select terminal_id,count(terminal_id) as 'count' from terminal_status_log where online=0 and (log_date between '2009-1-7' and '2009-1-9') group by terminal_id";
此语句查询的是在2009-1-7至2009-1-9这三天online为0的terminal_id,查询后的结果部分如下:
terminal_id | count |
+--------------+-------+
| UNI000103 | 3 |
| UNI000104 | 3 |
| UNI000111 | 1 |
| UNI000112 | 3 |
| UNI000113 | 3 |
| UNI000118 | 3 |
| UNI000130 | 1 |
| UNI000138 | 1 |
| UNI000141 | 3 |
现在我想再查询此结果中 count=3的terminal_id号以及符合此条件(count=3)的terminal_id的数量,请教如何查询??
terminal_id varchar(30);
online varchar(10);
logdate date
)
现通过sql语句查询: select terminal_id,count(terminal_id) as 'count' from terminal_status_log where online=0 and (log_date between '2009-1-7' and '2009-1-9') group by terminal_id";
此语句查询的是在2009-1-7至2009-1-9这三天online为0的terminal_id,查询后的结果部分如下:
terminal_id | count |
+--------------+-------+
| UNI000103 | 3 |
| UNI000104 | 3 |
| UNI000111 | 1 |
| UNI000112 | 3 |
| UNI000113 | 3 |
| UNI000118 | 3 |
| UNI000130 | 1 |
| UNI000138 | 1 |
| UNI000141 | 3 |
现在我想再查询此结果中 count=3的terminal_id号以及符合此条件(count=3)的terminal_id的数量,请教如何查询??
select terminal_id,count(terminal_id) as 'count'
from terminal_status_log where online=0 and (log_date between '2009-1-7' and '2009-1-9')
group by terminal_id" having (count(terminal_id))=3(count=3)的terminal_id的数量:
select count(*) from
(select terminal_id,count(terminal_id) as 'count'
from terminal_status_log where online=0 and (log_date between '2009-1-7' and '2009-1-9')
group by terminal_id" having (count(terminal_id))=3) a
语句是:select count(*) from
(select terminal_id,count(terminal_id) as 'count'
from terminal_status_log where online=0 and (log_date between '2009-1-7' and '2009-1-9')
group by terminal_id having (count(terminal_id))=3);
错误信息是:ERROR 1248 (42000): Every derived table must have its own alias