根据几个条件 查询表的条数
一张表: 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,及要求结果出来看看
探讨
引用:
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)), s……
引用1: 总数
2:hoststatus =0,hoststatus =1,hoststatus =2的时候得出条数
-
SQL code
mysql> SELECT * FROM `ti_host` ; +------+--------+----------+------------+---------+--------------+------------+ -----------------+--------+--------+-----------+--------------+----------+----- ---------+-------------------+--------------+------------------+--------------- -+----------------+ | 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>
-
SQL code
mysql> select * from ti_host; +------+--------+----------+------------+---------+--------------+------------+------------------+--------+--------+-----------+--------------+----------+---------------+-------------------+--------------+------------------+-----------------+----------------+ | MOID | HOSTSN