怎么查询一条销售表中, XX天没来买过东西的用户?

秀小川 2018-03-01 03:04:54

一张用户的销售详情表 sell 包含" id, userid , buydate"三个字段, 还有一个表user.包含"userId, username"

这个销售表里面, 同一个用户有多条销售记录
怎么查出来, 10天没来买过东西的用户?

...全文

887 8 打赏收藏转发到动态举报

写回复

用AI写文章

8 条回复

切换为时间正序

请发表友善的回复…

发表回复

RINK_1 2018-03-02

打赏
举报

引用 6 楼 K_Lord 的回复:

[quote=引用 5 楼 sinat_28984567 的回复:] 如果createtime 是年月日的试试这样，如果不是把createtime转换成yyyy-mm-dd这样的格式，代码都没测试，
SELECT  AVG(money) AS avgmoney ,
        createtime ,
        userid
FROM    sell
WHERE   userid IN ( SELECT  userid ,
                            MAX(buydate) AS buydate
                    FROM    sell
                    GROUP BY userid
                    HAVING  DATEDIFF(DAY, MAX(buydate), GETDATE()) > 10 )
GROUP BY createtime ,
        userid

我刚刚没表述清楚, 我这个是要查所有用户一段时间内的月均消费额. 这个用户不是固定每个月来消费, 有可能几个月消费一次, 也有可能一个月消费几次. 帮我看下我下面这个语句有没有啥问题?



SELECT
	u.userid
FROM
	user AS u
LEFT JOIN sell AS s ON u.userid = s.userid
WHERE
	s.buydate BETWEEN  '2017-03-01 00:00:00' 
AND '2018-03-01 23:59:59'
GROUP BY
	u.userid
HAVING
	SUM (s.money) / datediff(
		MONTH,
		(	CASE WHEN u.createtime > '2017-03-01 00:00:00' THEN u.createtime ELSE '2017-03-01 00:00:00' END	),
		'2018-03-01'
	) BETWEEN 0
AND 200

[/quote]


SELECT
    u.userid
FROM
    user AS u
LEFT JOIN sell AS s ON u.userid = s.userid
WHERE
    s.buydate BETWEEN  '2017-03-01 00:00:00' 
AND '2018-03-01 23:59:59'
GROUP BY
    u.userid
HAVING
 SUM (s.money)*1.0 / MAX(datediff(
        MONTH,
        (    CASE WHEN u.createtime > '2017-03-01 00:00:00' THEN u.createtime ELSE '2017-03-01 00:00:00' END    ),
        '2018-03-01'
    )) BETWEEN 0
AND 200

二月十六版主 2018-03-01

打赏
举报

引用 6 楼 K_Lord 的回复:

[quote=引用 5 楼 sinat_28984567 的回复:] 如果createtime 是年月日的试试这样，如果不是把createtime转换成yyyy-mm-dd这样的格式，代码都没测试，
SELECT  AVG(money) AS avgmoney ,
        createtime ,
        userid
FROM    sell
WHERE   userid IN ( SELECT  userid ,
                            MAX(buydate) AS buydate
                    FROM    sell
                    GROUP BY userid
                    HAVING  DATEDIFF(DAY, MAX(buydate), GETDATE()) > 10 )
GROUP BY createtime ,
        userid



SELECT
	u.userid
FROM
	user AS u
LEFT JOIN sell AS s ON u.userid = s.userid
WHERE
	s.buydate BETWEEN  '2017-03-01 00:00:00' 
AND '2018-03-01 23:59:59'
GROUP BY
	u.userid
HAVING
	SUM (s.money) / datediff(
		MONTH,
		(	CASE WHEN u.createtime > '2017-03-01 00:00:00' THEN u.createtime ELSE '2017-03-01 00:00:00' END	),
		'2018-03-01'
	) BETWEEN 0
AND 200

[/quote] 主要是业务问题，代码没问题。。。

秀小川 2018-03-01

打赏
举报

引用 5 楼 sinat_28984567 的回复:

如果createtime 是年月日的试试这样，如果不是把createtime转换成yyyy-mm-dd这样的格式，代码都没测试，

SELECT  AVG(money) AS avgmoney ,
        createtime ,
        userid
FROM    sell
WHERE   userid IN ( SELECT  userid ,
                            MAX(buydate) AS buydate
                    FROM    sell
                    GROUP BY userid
                    HAVING  DATEDIFF(DAY, MAX(buydate), GETDATE()) > 10 )
GROUP BY createtime ,
        userid



SELECT
	u.userid
FROM
	user AS u
LEFT JOIN sell AS s ON u.userid = s.userid
WHERE
	s.buydate BETWEEN  '2017-03-01 00:00:00' 
AND '2018-03-01 23:59:59'
GROUP BY
	u.userid
HAVING
	SUM (s.money) / datediff(
		MONTH,
		(	CASE WHEN u.createtime > '2017-03-01 00:00:00' THEN u.createtime ELSE '2017-03-01 00:00:00' END	),
		'2018-03-01'
	) BETWEEN 0
AND 200

二月十六版主 2018-03-01

打赏
举报

如果createtime 是年月日的试试这样，如果不是把createtime转换成yyyy-mm-dd这样的格式，代码都没测试，

SELECT  AVG(money) AS avgmoney ,
        createtime ,
        userid
FROM    sell
WHERE   userid IN ( SELECT  userid ,
                            MAX(buydate) AS buydate
                    FROM    sell
                    GROUP BY userid
                    HAVING  DATEDIFF(DAY, MAX(buydate), GETDATE()) > 10 )
GROUP BY createtime ,
        userid

Neo_whl 2018-03-01

打赏
举报


     select a.userid,username from sell as s join [user] as u
on s.userid=u.userid join(select userid,buydate from sell)t 
on t.userid=u.userid 
where datepart(dd,s.buydate)-datepart(dd,t.buydate)>10

秀小川 2018-03-01

打赏
举报

引用 1 楼 sinat_28984567 的回复:

SELECT  *
FROM    ( SELECT    userid ,
                    MAX(buydate) AS buydate
          FROM      sell
          GROUP BY  userid
        ) t
        JOIN [user] ON [user].userId = t.userid
WHERE   DATEDIFF(DAY, buydate, GETDATE()) > 10

还有一个需求是查这个用户的月均消费金额，在sell表里面有一个money字段， user表里面有一个createtime字段. 需要查询月均消费金额大于XX的用户，这种SQL怎么写。我现在是用程序控制，遍历user表，然后查出来sum(money) , 再用程序计算月均消费，再然后程序判断，最后输出。效率太低！

学海无涯-回头是岸 2018-03-01

打赏
举报

with t1 as( select a.*,row_number()over(partition by userid order by buydate asc) as rn from sell a) ,t2 as (select t1.* from t1 where t1.rn=1) select * from user where userid in( select k.userid from t1 k where k.rn=2 and not exists(select 1 from t2 where t2 userid=k.userid and k.buydate -t2.buydate <=10))

二月十六版主 2018-03-01

打赏
举报

SELECT  *
FROM    ( SELECT    userid ,
                    MAX(buydate) AS buydate
          FROM      sell
          GROUP BY  userid
        ) t
        JOIN [user] ON [user].userId = t.userid
WHERE   DATEDIFF(DAY, buydate, GETDATE()) > 10