4表联合查询多条件筛选

[chai1338]

4个表:Guid为varchar类型
Record表要满足：
1.CreatedOn大于2016-1-1
2.多个O_Guid会出现同一个U_Guid，首次下单(最早的CreatedOn)+65天内的所有订单O_Guid，在Bill表对应O_Guid满足date2-date1<1天（DATEDIFF(Second,u_b_dateTime,u_b_payTime)>3600*24）这批用户（U_Guid），
level为1：money全部提升1000,但最后的值不能超过2000
level为2：money全部提升2000,但最后的值不能超过3000

首次下单：
Record里面其实有个外键U_GUID，表结构里面没有，但是联表查询就能查出来
那么就是会出现多条相同的U_GUID，首次下单就是指多条相同的U_GUID里最早的createdOn那条数据

[spiritofdragon]

CREATE TABLE [dbo].[User](
	[id] [int] NULL,
	[U_GUID] [varchar](36) NULL,
	[money] [money] NULL,
	[level] [int] NULL
) ON [PRIMARY]

GO
CREATE TABLE [dbo].[Order](
	[id] [int] NULL,
	[O_GUID] [varchar](36) NULL,
	[U_GUID] [varchar](36) NULL
) ON [PRIMARY]

GO
CREATE TABLE [dbo].[Bill](
	[id] [int] NULL,
	[O_GUID] [varchar](36) NULL,
	[date1] [datetime] NULL,
	[date2] [datetime] NULL
) ON [PRIMARY]

GO
CREATE TABLE [dbo].[Record](
	[id] [int] NULL,
	[O_GUID] [varchar](36) NULL,
	[U_GUID] [varchar](36) NULL,--既然有这字段，就写上，可以少连一步
	[createOn] [datetime] NULL
) ON [PRIMARY]

GO
with t as (
select u.*,MIN(r.createOn) MinCreateOn
from [User] u join Record r on u.U_GUID=r.U_GUID
where r.createOn>cast('2016-01-01' as datetime)
)
,tt as (
select 
	distinct t.*,b.date1,b.date2
from t
	join Record r on t.U_GUID=r.U_GUID
		and r.createOn-t.MinCreateOn<=65
	join [Order] o on r.O_GUID=o.O_GUID
	join Bill b on b.O_GUID=o.O_GUID 
), u as (
select t.id
from t
--(你是不是要求，所有订单都是1天内付款的这种的用户？如果有一单，隔天付款，就不要这个用户了？如果是这么理解，那么用下句where。不存在>3600*24的用户)
where not exists(select 1 from tt where tt.id=t.id and DATEDIFF(Second,date1,date2)>3600*24)
)
update [User] set [money]= 
	case [level] 
		when 1 
		then case when [money]+1000>2000 then 2000 else [money]+1000 end
		when 2 
		then case when [money]+2000>3000 then 3000 else [money]+2000 end
	end
from [User] u1 join u on u1.id=u.id

[中国风]

用秒钟计算列名对不上，你自己参数改
更新前查看一下

--DATEDIFF(Second,u_b_dateTime,u_b_payTime)>3600*24,u_b_dateTime/u_b_payTime是那个表?

UPDATE d
SET [money]=CASE WHEN d.[level]=1 THEN CASE WHEN d.[money]+1000>=2000 THEN 2000 ELSE d.[money]+1000 END 
				ELSE CASE WHEN d.[money]+2000>=3000 THEN 3000 ELSE d.[money]+2000 END END 
FROM    ( SELECT    O_Guid
                   ,MIN(createOn) AS createOn
          FROM      Record
          WHERE     createOn > '2016-1-1' ) AS a
        INNER JOIN Bill AS b ON b.O_Guid = a.O_Guid
                                AND Date1 BETWEEN a.createOn AND a.createOn
                                + 65
                                AND b.Date2 < b.Date1 + 1
        INNER JOIN [ORDER] AS c ON c.O_Guid = a.O_Guid
        INNER JOIN [USER] AS d ON d.U_Guid = c.U_Guid
WHERE d.[level]=1 AND d.[money]<2000 OR d.[level]=2 AND d.[money]<3000


;

[chai1338]

引用 2 楼 roy_88 的回复:

用秒钟计算列名对不上，你自己参数改 更新前查看一下

--DATEDIFF(Second,u_b_dateTime,u_b_payTime)>3600*24,u_b_dateTime/u_b_payTime是那个表?



;

u_b_dateTime/u_b_payTime就是date1跟date2

SELECT    O_Guid
                   ,MIN(createOn) AS createOn
          FROM      Record
          WHERE     createOn > '2016-1-1' 

选择列表中的列 'Record.O_GUID' 无效，因为该列没有包含在聚合函数或 GROUP BY 子句中

[chai1338]

引用 2 楼 roy_88 的回复:

用秒钟计算列名对不上，你自己参数改 更新前查看一下

引用 1 楼 spiritofdragon 的回复:

看了两位的回答，我觉得我描述的不是很清楚，重新开个了帖子 麻烦看一下，此帖已结 http://bbs.csdn.net/topics/391938483