常用sql

1. 同时利用limit和union

SELECT  *
FROM    (   SELECT  * 
            FROM    Seq 
            WHERE   JULIANDAY('2012-05-25 19:02:00') <= JULIANDAY(TimeP) 
            ORDER BY TimeP
            LIMIT 50
        )
UNION
SELECT  *
FROM    (   SELECT  * 
            FROM    Seq 
            WHERE   JULIANDAY('2012-05-29 06:20:50') <= JULIANDAY(TimeI) 
            ORDER BY TimeI
            LIMIT 50
        )

 

2.  一般的sql语句

SELECT DISTINCT column, AGG_FUNC(column_or_expression), …
FROM mytable
    JOIN another_table
      ON mytable.column = another_table.column
    WHERE constraint_expression
    GROUP BY column
    HAVING constraint_expression
    ORDER BY column ASC/DESC
    LIMIT count OFFSET COUNT;

 

3. 在同一个sql语句中，统计不同条件的Count数量

select id,
count(case when 属性 in (1,2) then 1 else 0 end) as sum1,
count(case when  属性 in (3) then 1 else 0 end) as sum2,
sum(case when  属性 in (4,5) then 1 else 0 end) as sum3
from 表名
group by id

or：

 SELECT   
count( * ) AS num,  
count(if(字段=1,true,null)) AS sum1,   

count(if字段=2,true,null)) AS sum2   

FROM 表名 

WHERE  其他条件

 

 

case 语句

常用sql