I have two tables as below
我有两张桌子如下
Table 1 ----------------------------------- UserID | UserName | Age | Salary ----------------------------------- 1 | foo | 22 | 33000 -----------------------------------
Table 2 ------------------------------------------------ UserID | Age | Salary | CreatedDate ------------------------------------------------ 1 | NULL | 35000 | 2015-01-01 ------------------------------------------------ 1 | 28 | NULL | 2015-02-01 ------------------------------------------------ 1 | NULL | 28000 | 2015-03-01 ------------------------------------------------
I need the result like this.
我需要这样的结果。
Result ----------------------------------- UserID | UserName | Age | Salary ----------------------------------- 1 | foo | 28 | 28000 -----------------------------------
This is just an example. In my real project I have around 6 columns like Age and Salary in above tables.
这只是一个例子。在我的真实项目中,我在上表中有大约6列,如Age和Salary。
In table 2 , each record will have only have one value i.e if Age has value then Salary will be NULL and viceversa.
在表2中,每条记录只有一个值,即如果Age有值,则Salary将为NULL,反之亦然。
UPDATE :
更新:
Table 2 has CreatedDate Column. So i want to get latest "NOTNULL" CELL Value instead of maximum value.
表2包含CreatedDate列。所以我想获得最新的“NOTNULL”CELL值而不是最大值。
4 个解决方案
#1
3
Note: I'm giving you the benefit of the doubt that you know what you're doing, and you just haven't told us everything about your schema.
注意:我怀疑你知道自己在做什么,而且你没有告诉我们关于你的架构的所有信息。
It looks like Table 2
is actually an "updates" table, in which each row contains a delta of changes to apply to the base entity in Table 1
. In which case you can retrieve each column's data with a correlated join (technically an outer-apply) and put the results together. Something like the following:
看起来表2实际上是一个“更新”表,其中每行包含应用于表1中基本实体的更改增量。在这种情况下,您可以使用相关联接检索每个列的数据(技术上是外部 - 申请)并将结果放在一起。类似于以下内容:
select a.UserID, a.UserName,
coalesce(aAge.Age, a.Age),
coalesce(aSalary.Salary, a.Salary)
from [Table 1] a
outer apply (
select Age
from [Table 2] x
where x.UserID = a.UserID
and x.Age is not null
and not exists (
select 1
from [Table 2] y
where x.UserID = y.UserID
and y.Id > x.Id
and y.Age is not null
)
) aAge,
outer apply (
select Salary
from [Table 2] x
where x.UserID = a.UserID
and x.Salary is not null
and not exists (
select 1
from [Table 2] y
where x.UserID = y.UserID
and y.Id > x.Id
and y.Salary is not null
)
) aSalary
Do note I am assuming you have at minimum an Id
column in Table 2
which is monotonically increasing with each insert. If you have a "change time" column, use this instead to get the latest row, as it is better.
请注意我假设您在表2中至少有一个Id列,每个插入单调增加。如果您有“更改时间”列,请使用此列来获取最新行,因为它更好。
#2
4
You can get this done using a simple MAX()
and GROUP BY
:
您可以使用简单的MAX()和GROUP BY完成此操作:
select t1.userid,t1.username, MAX(t2.Age) as Age, MAX(t2.Salary) as Salary
from table1 t1 join
table2 t2 on t1.userid=t2.userid
group by t1.userid,t1.username
Result:
结果:
userid username Age Salary
--------------------------------
1 foo 28 35000
Sample result in SQL Fiddle
SQL Fiddle中的示例结果
#3
2
To get the latest value based on CreatedDate
, you can use ROW_NUMBER
to filter for latest rows. Here the partition is based UserID
and the other columns, Age
and Salary
.
要基于CreatedDate获取最新值,可以使用ROW_NUMBER过滤最新行。这里的分区基于UserID,其他列是Age和Salary。
SQL小提琴
;WITH Cte AS(
SELECT
UserID,
Age = MAX(Age),
Salary = MAX(Salary)
FROM(
SELECT *, Rn = ROW_NUMBER() OVER(
PARTITION BY
UserID,
CASE
WHEN Age IS NOT NULL THEN 1
WHEN Salary IS NOT NULL THEN 2
END
ORDER BY CreatedDate DESC
)
FROM Table2
)t
WHERE Rn = 1
GROUP BY UserID
)
SELECT
t.UserID,
t.UserName,
Age = ISNULL(c.Age, t.Age),
Salary = ISNULL(c.Salary, t.Salary)
FROM Table1 t
LEFT JOIN Cte c
ON t.UserID = c.UserID
#4
0
following query should work(working fine in MSSQL) :
以下查询应该工作(在MSSQL中正常工作):
select a.userID,a.username,b.age,b.sal from <table1> a
inner join
(select userID,MAX(age) age,MAX(sal) sal from <table2> group by userID) b
on a.userID=b.userID
#1
3
Note: I'm giving you the benefit of the doubt that you know what you're doing, and you just haven't told us everything about your schema.
注意:我怀疑你知道自己在做什么,而且你没有告诉我们关于你的架构的所有信息。
It looks like Table 2
is actually an "updates" table, in which each row contains a delta of changes to apply to the base entity in Table 1
. In which case you can retrieve each column's data with a correlated join (technically an outer-apply) and put the results together. Something like the following:
看起来表2实际上是一个“更新”表,其中每行包含应用于表1中基本实体的更改增量。在这种情况下,您可以使用相关联接检索每个列的数据(技术上是外部 - 申请)并将结果放在一起。类似于以下内容:
select a.UserID, a.UserName,
coalesce(aAge.Age, a.Age),
coalesce(aSalary.Salary, a.Salary)
from [Table 1] a
outer apply (
select Age
from [Table 2] x
where x.UserID = a.UserID
and x.Age is not null
and not exists (
select 1
from [Table 2] y
where x.UserID = y.UserID
and y.Id > x.Id
and y.Age is not null
)
) aAge,
outer apply (
select Salary
from [Table 2] x
where x.UserID = a.UserID
and x.Salary is not null
and not exists (
select 1
from [Table 2] y
where x.UserID = y.UserID
and y.Id > x.Id
and y.Salary is not null
)
) aSalary
Do note I am assuming you have at minimum an Id
column in Table 2
which is monotonically increasing with each insert. If you have a "change time" column, use this instead to get the latest row, as it is better.
请注意我假设您在表2中至少有一个Id列,每个插入单调增加。如果您有“更改时间”列,请使用此列来获取最新行,因为它更好。
#2
4
You can get this done using a simple MAX()
and GROUP BY
:
您可以使用简单的MAX()和GROUP BY完成此操作:
select t1.userid,t1.username, MAX(t2.Age) as Age, MAX(t2.Salary) as Salary
from table1 t1 join
table2 t2 on t1.userid=t2.userid
group by t1.userid,t1.username
Result:
结果:
userid username Age Salary
--------------------------------
1 foo 28 35000
Sample result in SQL Fiddle
SQL Fiddle中的示例结果
#3
2
To get the latest value based on CreatedDate
, you can use ROW_NUMBER
to filter for latest rows. Here the partition is based UserID
and the other columns, Age
and Salary
.
要基于CreatedDate获取最新值,可以使用ROW_NUMBER过滤最新行。这里的分区基于UserID,其他列是Age和Salary。
SQL小提琴
;WITH Cte AS(
SELECT
UserID,
Age = MAX(Age),
Salary = MAX(Salary)
FROM(
SELECT *, Rn = ROW_NUMBER() OVER(
PARTITION BY
UserID,
CASE
WHEN Age IS NOT NULL THEN 1
WHEN Salary IS NOT NULL THEN 2
END
ORDER BY CreatedDate DESC
)
FROM Table2
)t
WHERE Rn = 1
GROUP BY UserID
)
SELECT
t.UserID,
t.UserName,
Age = ISNULL(c.Age, t.Age),
Salary = ISNULL(c.Salary, t.Salary)
FROM Table1 t
LEFT JOIN Cte c
ON t.UserID = c.UserID
#4
0
following query should work(working fine in MSSQL) :
以下查询应该工作(在MSSQL中正常工作):
select a.userID,a.username,b.age,b.sal from <table1> a
inner join
(select userID,MAX(age) age,MAX(sal) sal from <table2> group by userID) b
on a.userID=b.userID