根据几个条件 查询表的条数
一张表: t_topoobject
字段: id,name,location,status
我现在要查询:
条件1:t_topoobject 的总数
条件2:根据status(int类型)判断来查询条数(多个条件,如:status=0,status=1,status=2)
ps:
如果我还有一张表,跟上面一样。 2张表可以一起查询不?
[解决办法]
select count(*),sum(if(status=0,1,0)), sum(if(status=1,1,0)), ... from tt
如果我还有一张表,跟上面一样。 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
[解决办法]
贴建表及插入记录的SQL,及要求结果出来看看
[解决办法]
mysql> SELECT * FROM `ti_host` ;+------+--------+----------+------------+---------+--------------+------------+-----------------+--------+--------+-----------+--------------+----------+--------------+-------------------+--------------+------------------+----------------+----------------+| MOID | HOSTSN | HOSTNAME | CPUCORENUM | CPUDESC | CPUFREQUENCY | HOSTSTATUS |MANAGEDIPADDRESS | MEMORY | OS | OSVERSION | AGENTVERSION | HOSTTYPE | TOTACAPACITY | TOTALCAPACITYUNIT | USEDCAPACITY | USEDCAPACITYUNIT | phisicalMemSiz | virtualMemSize |+------+--------+----------+------------+---------+--------------+------------+-----------------+--------+--------+-----------+--------------+----------+--------------+-------------------+--------------+------------------+----------------+----------------+| 1 | 234 | 234 | 234 | 234 | 234 | 1 |10.27.81.131 | 324 | 234234 | 1 | 234 | 234234 | 23424 | 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(hosstatus=2,1,0)) |+----------+---------------------------+---------------------------+--------------------------+| 2 | 1 | 1 | 0 |+----------+---------------------------+---------------------------+--------------------------+1 row in set (0.00 sec)mysql>
[解决办法]
mysql> select * from ti_host;+------+--------+----------+------------+---------+--------------+------------+------------------+--------+--------+-----------+--------------+----------+---------------+-------------------+--------------+------------------+-----------------+----------------+| 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)
[解决办法]