一张表: t_topoobject
字段: id,name,location,status
我现在要查询:
条件1:t_topoobject 的总数
条件2:根据status(int类型)判断来查询条数(多个条件,如:status=0,status=1,status=2)
ps:
如果我还有一张表,跟上面一样。 2张表可以一起查询不?
字段: id,name,location,status
我现在要查询:
条件1:t_topoobject 的总数
条件2:根据status(int类型)判断来查询条数(多个条件,如:status=0,status=1,status=2)
ps:
如果我还有一张表,跟上面一样。 2张表可以一起查询不?
如果我还有一张表,跟上面一样。 2张表可以一起查询不?
详细说明,是否有关系,直接UNION ALL不行?
select count(*),sum(if(status=0,1,0)), sum(if(status=1,1,0)), ... from (select * from tt1 union all select * from tt2) a
是否可以用group by来实现? 怎么实现? 谢谢
/*
MySQL Data Transfer
Source Host: localhost
Source Database: lego
Target Host: localhost
Target Database: lego
Date: 2012/3/27 17:16:31
*/SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for ti_host
-- ----------------------------
DROP TABLE IF EXISTS `ti_host`;
CREATE TABLE `ti_host` (
`MOID` bigint(20) NOT NULL,
`HOSTSN` varchar(255) DEFAULT NULL,
`HOSTNAME` varchar(255) DEFAULT NULL,
`CPUCORENUM` int(11) DEFAULT NULL,
`CPUDESC` varchar(255) DEFAULT NULL,
`CPUFREQUENCY` varchar(255) DEFAULT NULL,
`HOSTSTATUS` int(11) DEFAULT NULL,
`MANAGEDIPADDRESS` varchar(100) DEFAULT NULL,
`MEMORY` varchar(100) DEFAULT NULL,
`OS` varchar(255) DEFAULT NULL,
`OSVERSION` varchar(255) DEFAULT NULL,
`AGENTVERSION` varchar(255) DEFAULT NULL,
`HOSTTYPE` int(11) DEFAULT NULL,
`TOTALCAPACITY` varchar(255) DEFAULT NULL,
`TOTALCAPACITYUNIT` varchar(10) DEFAULT NULL,
`USEDCAPACITY` varchar(255) DEFAULT NULL,
`USEDCAPACITYUNIT` varchar(10) DEFAULT NULL,
`phisicalMemSize` varchar(10) DEFAULT NULL,
`virtualMemSize` varchar(10) DEFAULT NULL,
PRIMARY KEY (`MOID`),
KEY `FKB0EB6132BA3A49A3` (`MOID`),
CONSTRAINT `FKB0EB6132BA3A49A3` FOREIGN KEY (`MOID`) REFERENCES `t_managedobject` (`moid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;-- ----------------------------
-- Records
-- ----------------------------
INSERT INTO `ti_host` VALUES ('1', '234', '234', '234', '234', '234', '1', '10.27.81.131', '324', '234234', '1', '234', '234234', '234234', '234', '234', '234', '234', '234');
INSERT INTO `ti_host` VALUES ('2', '234', '234', '234', '23', '423', '0', '10.27.81.131', '345', '345', '1', '345', '234', '234', '234234', '2342', '23423', '424', '234');
这是另一张表,我自己建的。 那张表没法贴出来。
1: 总数
2:hoststatus =0,hoststatus =1,hoststatus =2的时候得出条数
建议你列出你的表结构,并提供测试数据以及基于这些测试数据的所对应正确结果。
参考一下这个贴子的提问方式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)
这样想帮你的人可以直接搭建和你相同的环境,并在给出方案前进行测试,避免文字描述理解上的误差。
+------+--------+----------+------------+---------+--------------+------------+
-----------------+--------+--------+-----------+--------------+----------+-----
---------+-------------------+--------------+------------------+---------------
-+----------------+
| MOID | HOSTSN | HOSTNAME | CPUCORENUM | CPUDESC | CPUFREQUENCY | HOSTSTATUS |
MANAGEDIPADDRESS | MEMORY | OS | OSVERSION | AGENTVERSION | HOSTTYPE | TOTA
CAPACITY | TOTALCAPACITYUNIT | USEDCAPACITY | USEDCAPACITYUNIT | phisicalMemSiz
| virtualMemSize |
+------+--------+----------+------------+---------+--------------+------------+
-----------------+--------+--------+-----------+--------------+----------+-----
---------+-------------------+--------------+------------------+---------------
-+----------------+
| 1 | 234 | 234 | 234 | 234 | 234 | 1 |
10.27.81.131 | 324 | 234234 | 1 | 234 | 234234 | 2342
4 | 234 | 234 | 234 | 234
| 234 |
| 2 | 234 | 234 | 234 | 23 | 423 | 0 |
10.27.81.131 | 345 | 345 | 1 | 345 | 234 | 234
| 234234 | 2342 | 23423 | 424
| 234 |
+------+--------+----------+------------+---------+--------------+------------+
-----------------+--------+--------+-----------+--------------+----------+-----
---------+-------------------+--------------+------------------+---------------
-+----------------+
2 rows in set (0.00 sec)mysql> SELECT COUNT(*),SUM(IF(hoststatus=0,1,0)), SUM(IF(hoststatus=1,1,0)),
-> SUM(IF(hoststatus=2,1,0))
-> FROM `ti_host`;
+----------+---------------------------+---------------------------+-----------
---------------+
| COUNT(*) | SUM(IF(hoststatus=0,1,0)) | SUM(IF(hoststatus=1,1,0)) | SUM(IF(hos
status=2,1,0)) |
+----------+---------------------------+---------------------------+-----------
---------------+
| 2 | 1 | 1 |
0 |
+----------+---------------------------+---------------------------+-----------
---------------+
1 row in set (0.00 sec)mysql>
+------+--------+----------+------------+---------+--------------+------------+------------------+--------+--------+-----------+--------------+----------+---------------+-------------------+--------------+------------------+-----------------+----------------+
| MOID | HOSTSN | HOSTNAME | CPUCORENUM | CPUDESC | CPUFREQUENCY | HOSTSTATUS |MANAGEDIPADDRESS | MEMORY | OS | OSVERSION | AGENTVERSION | HOSTTYPE | TOTALCAPACITY | TOTALCAPACITYUNIT | USEDCAPACITY | USEDCAPACITYUNIT | phisicalMemSize | virtualMemSize |
+------+--------+----------+------------+---------+--------------+------------+------------------+--------+--------+-----------+--------------+----------+---------------+-------------------+--------------+------------------+-----------------+----------------+
| 1 | 234 | 234 | 234 | 234 | 234 | 1 |10.27.81.131 | 324 | 234234 | 1 | 234 | 234234 | 234234 | 234 | 234 | 234 | 234 | 234 |
| 2 | 234 | 234 | 234 | 23 | 423 | 0 |10.27.81.131 | 345 | 345 | 1 | 345 | 234 | 234 | 234234 | 2342 | 23423 | 424 | 234 |
+------+--------+----------+------------+---------+--------------+------------+------------------+--------+--------+-----------+--------------+----------+---------------+-------------------+--------------+------------------+-----------------+----------------+
2 rows in set (0.00 sec)
mysql>mysql> select count(*) from ti_host;
+----------+
| count(*) |
+----------+
| 2 |
+----------+
1 row in set (0.06 sec)mysql>
请问这个返回的是个数组? 还是list、
select count(*) from ti_host where HOSTSTATUS=1
2。select count(id),HOSTSTATUS from ti_host group by HOSTSTATUS;