我需要列名为“二月”的公式。
我需要列名为“二月”的公式。
提问于 2014-11-24 00:42:05
我有一组数据如下。
单张1
+------+-------+
| JANUARY |
+------+-------+
+----+----------+------+-------+
| ID | NAME |COUNT | PRICE |
+----+----------+------+-------+
| 1 | ALFRED | 11 | 150 |
| 2 | ARIS | 22 | 120 |
| 3 | JOHN | 33 | 170 |
| 4 | CHRIS | 22 | 190 |
| 5 | JOE | 55 | 120 |
| 6 | ACE | 11 | 200 |
+----+----------+------+-------+SHEET2
+----+----------+------+-------+
| ID | NAME |COUNT | PRICE |
+----+----------+------+-------+
| 1 | CHRIS | 13 | 123 |
| 2 | ACE | 26 | 165 |
| 3 | JOE | 39 | 178 |
| 4 | ALFRED | 21 | 198 |
| 5 | JOHN | 58 | 112 |
| 6 | ARIS | 11 | 200 |
+----+----------+------+-------+在sheet1中，结果应该如下所示：
+------+-------++------+-------+
| JANUARY | FEBRUARY |
+------+-------++------+-------+
+----+----------+------+-------++-------+-------+
| ID | NAME |COUNT | PRICE || COUNT | PRICE |
+----+----------+------+-------++-------+-------+
| 1 | ALFRED | 11 | 150 || 21 | 198 |
| 2 | ARIS | 22 | 120 || 11 | 200 |
| 3 | JOHN | 33 | 170 || 58 | 112 |
| 4 | CHRIS | 22 | 190 || 13 | 123 |
| 5 | JOE | 55 | 120 || 39 | 178 |
| 6 | ACE | 11 | 200 || 26 | 165 |
+----+----------+------+-------++-------+-------+我需要列名为“二月”的公式。此公式将在第2页中找到其匹配项。
回答 2
Stack Overflow用户
回答已采纳
发布于 2014-11-24 00:46:30
尝尝这个
SELECT T2.ID_ENTRY, T1.TAG, T1.TAG2, T2.Name, T2.LastName, T2.Age, T2.Tel
FROM Table1 T1
INNER JOIN Table2 T2
ON T1.ID = T2.ID
GROUP BY T2.ID_ENTRY, T1.TAG, T1.TAG2, T2.Name, T2.LastName, T2.Age, T2.Tel
HAVING Count(T2.ID_ENTRY) > 1Stack Overflow用户
发布于 2014-11-24 00:52:12
构建示例数据
create table table1(
id int,
id_entry varchar(10),
tag int,
tag2 int
)
create table table2(
id int,
name varchar(50),
lastname varchar(50),
age int,
tel int
)
insert into table1
select 1, 'A1', 11, 12 union all
select 2, 'C2', 22, 13 union all
select 3, 'S5', 33, 14 union all
select 4, 'C2', 22, 13 union all
select 5, 'B6', 55, 16 union all
select 6, 'A1', 11, 12
insert into table2
select 1, 'ALFRED', 'DAVE', 21, 555 union all
select 2, 'FRED', 'SMITH', 22, 666 union all
select 3, 'MANNY', 'PAC', 23, 777 union all
select 4, 'FRED', 'DAVE', 22, 666 union all
select 5, 'JOHN', 'SMITH', 25, 999 union all
select 6, 'ALFRED', 'DAVE', 21, 555溶液
;with cte as(
select
t1.id_entry,
t1.tag,
t1.tag2,
t2.name,
t2.lastname,
t2.age,
t2.tel,
cc = count(*) over(partition by t1.id_entry),
rn = row_number() over(partition by t1.id_entry order by t2.lastname desc)
from table1 t1
inner join table2 t2
on t2.id = t1.id
)
select
id_entry,
tag,
tag2,
name,
lastname,
age,
tel
from cte
where
cc > 1
and rn = 1删除样本数据
drop table table1
drop table table2页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/27096276
复制相关文章
相似问题
腾讯云开发者
