sql查询多列最大值

Question

select count(PumpModel), count(PumpManu), count(PumpUnits),count(PumpTime),count(PumpPower) as b from FMDS_Water_IrrigaDrainageStat

如上，FMDS_Water_IrrigaDrainageStat里有count里的几个字段，我想查出来表中非空字段最多的数量，sql怎么写?

---

please verify my account

Answer 1

写个自定义函数判断吧

---

想不想时已是想，不如不想都不想。

Answer 2

怎么写？

---

please verify my account

Answer 3

一共就这几个字段，你count出来后，比较一下，哪个最大 就输出哪个。用函数完成哈。

---

family as water

Answer 4

create function dbo.MultiMax(para1 int=0, para2 int=0, para3 int=0,......para10 int=0)

returns int

as

begin

declare @result int;

if para1 > para2

set @result = para1

else

set @result = para2

if @result < para3

set @result = para3

……

return (@result)

end

go

然后

select dbo.MultiMax( count(PumpModel), count(PumpManu), count(PumpUnits),count(PumpTime),count(PumpPower)) as b from FMDS_Water_IrrigaDrainageStat

---

想不想时已是想，不如不想都不想。

Answer 5

列转成行之后用 MAX 函数就行了嘛

SELECT * FROM(
select count(PumpModel), count(PumpManu), count(PumpUnits),count(PumpTime),count(PumpPower) as b 
from FMDS_Water_IrrigaDrainageStat
) DATA( PumpModel, PumpManu, PumpUnits, PumpTime, b)
	CROSS APPLY(
		SELECT MAX(c) FROM(
			VALUES (PumpModel), (PumpManu), (PumpUnits), (PumpTime), (b)
		) C(c)
	) C(c)