常见SQL

一

Create table #tab([id] nvarchar(23),[invoiceid] nvarchar(23))
Insert #tab
select N'001',N'*' union all
select N'001',N'*' union all
select N'001',N'123' union all
select N'002',N'234' union all
select N'002',N'456' union all
select N'003',N'*' union all
select N'003',N'*' union all
select N'003',N'*'
GO




id      invoiceid
001    *
001    *
001   123
002   234
002   456
003    *
003    *
003    *





得到这样的结果
id      state  
001   部分开票
002   全部开票
003   全部未开

state代表开票状态，比如同一个id下的，inoviceid全部为星号就代表全部开票；

如果invoiceid既有星号，又有数字的，代表部分开票；

如果invocieid全部为数字的，就代表全部开票




SELECT id,
    CASE SUM(DISTINCT CASE invoiceid WHEN '*' THEN 1 ELSE 2 END)
        WHEN 1 THEN '全部未开' WHEN 2 THEN '全部开票' WHEN 3 THEN '部分开票'
    END
FROM tb
GROUP BY id








二 用户号，单号，时间，金额，求每个用户每天前50笔里，金额大于10元的数据有多少

CREATE TABLE t(用户号 int,单号 INT,时间 DATETIME, 金额 int)




INSERT INTO t VALUES (1,1,'2018-01-21 08:00',11)

INSERT INTO t VALUES (1,2,'2018-01-21 09:00',12)

INSERT INTO t VALUES (1,3,'2018-01-21 10:00',20)

INSERT INTO t VALUES (2,4,'2018-01-21 11:00',3)

INSERT INTO t VALUES (2,5,'2018-01-21 12:00',15)

INSERT INTO t VALUES (2,6,'2018-01-21 13:00',5)

INSERT INTO t VALUES (3,7,'2018-01-22 08:00',30)




先按用户号和单据日期分组，将每天的数据分组排序，最后取序号（rid）小于等于50的数据




WITH cte AS (

    SELECT ROW_NUMBER() OVER (PARTITION BY 用户号,convert(CHAR(10),时间,120) order by 时间 desc) as rid

    ,*

    FROM t

    WHERE 金额>10   

)




SELECT 

    用户号

    ,convert(CHAR(10),时间,120) as 时间

    ,COUNT(1) AS 笔数

    ,SUM(金额) AS 金额

FROM cte

WHERE rid<=50

GROUP BY 用户号,convert(CHAR(10),时间,120)