sql语句count怎么使用?

Question

如题所述

Answer 1

过多繁琐的sql影响代码质量，及维护成本，以下为两种小技巧处理方式，仅供参考。

第一种，用case ---when---方法

select id

,sum(case when type in (1,2) then [count] else 0 end) as sum1

,sum(case when type in (3) then [count] else 0 end) as sum2

,sum(case when type in (4,5) then [count] else 0 end) as sum3

from 表名

group by id

第二种，if 判断

SELECT   SUM( goods_amount ) AS money,   

count( * ) AS num,  

count(if(pay_status=1,true,null)) AS success,   

count(if(pay_status=2,true,null)) AS fall   

FROM `tab_order_info`     

WHERE user_id = 11 

Answer 2

下面的几条语句完成向A表插入10000条C=1,D=2,E=3的记录：
DECLARE @C INT,@D INT,@E INT,@COUNT INT
SELECT @C=1,@D=2,@E=3,@COUNT=10000
WHILE @COUNT>0
BEGIN
insert into A表(C,D,E) VALUES(@C,@D,@E)
SET @COUNT=@COUNT-1
END