Question

SQL SERVER查询多行值,结果只显示一行!

我有两个统计语句，怎么才能让这两个语句的查询结果显示在一行上。

SELECT count (itemid) k FROM PE_CommonModel WHERE Inputer = 'admin' and NodeID = 19
SELECT count (itemid) a FROM PE_CommonModel WHERE Inputer = 'admin' AND NodeID = 20

这两个语句中间用逗号隔开不行。。

第一条语句的结果是152，第二条语句的结果是233

另外，这两条语句的结果我都赋予了别名，一个K一个是A。

怎么才能让结果显示在一行上，就是一行上显示K 152 A233

Answer 1

转换一种方式来计数即可
SELECT SUM(CASE NodeID WHEN 19 THEN 1 ELSE 0 END) AS K,
SUM(CASE NodeID WHEN 20 THEN 1 ELSE 0 END) AS A
FROM PE_CommonModel WHERE Inputer = 'admin'

Answer 2

可以这样写：
SELECT t1.K ,t2.A FROM (SELECT count (itemid) AS K FROM PE_CommonModel WHERE Inputer = 'admin' and NodeID = 19) t1,(SELECT count (itemid) AS A FROM PE_CommonModel WHERE Inputer = 'admin' AND NodeID = 20) t2
注：你的列别名写法有误，应该这样写："count (itemid) as k"

Answer 3

(SELECT count (itemid) k FROM PE_CommonModel WHERE Inputer = 'admin' and NodeID = 19) union (SELECT count (itemid) a FROM PE_CommonModel WHERE Inputer = 'admin' AND NodeID = 20)

Answer 4

两个语句间加入 join left 或者join right；