做两个查询到1周的结果与2列设置
问题描述:
说我有一个表,如下所示:做两个查询到1周的结果与2列设置
Person Table
ID AccountID Name
1 6 Billy
2 6 Joe
3 6 Tom
4 8 Jamie
5 8 Jake
6 8 Sam
我有两个疑问,我自己知道工作:
Select Name Group1 from person where accountid = 6
Select Name Group2 from person where accountid = 8
但我想要一个结果集看起来像这样:
Group1 Group2
Billy Jamie
Joe Jake
Tom Same
答
您可以使用row_number()
分配于各行的不同值,ANS然后用FULL OUTER JOIN
加入两个子查询:
select t1.group1,
t2.group2
from
(
select name group1,
row_number() over(order by id) rn
from yourtable
where accountid = 6
) t1
full outer join
(
select name group2,
row_number() over(order by id) rn
from yourtable
where accountid = 8
) t2
on t1.rn = t2.rn;
答
我同意你应该做这个客户端。但它可以在T/SQL来完成:
select G1.Name as Group1
, G2.Name as Group2
from (
select row_number() over (order by ID) as rn
, *
from Group
where AccountID = 6
) as G1
full outer join
(
select row_number() over (order by ID) as rn
, *
from Group
where AccountID = 8
) as G2
on G1.rn = G2.rn
order by
coalesce(G1.rn, G2.rn)
这是应该在应用程序端处理的东西。 – Kermit 2013-03-22 18:56:22
这不涉及应用程序我正在做一些数据转换 – 2013-03-22 18:57:27
Group1和Group2之间是否有任何关系? – Vinnie 2013-03-22 18:57:40