三个表的合龙
三个表的合并
table1
Id LocationCode
1 A02b
2 A02b
table2
detail_Id Tallydata
1 1
1 2
1 3
1 4
1 5
2 6
2 7
2 8
2 9
2 10
table1和table2中Id与detail_Id关联,
table3
LocationCode CodeDetail
A02b 字段1
A02b 字段2
A02b 字段3
A02b 字段4
A02b 字段5
table1和table3中LocationCode关联
我想得到如下表,怎么弄?
LocationCode 字段1 字段2 字段3 字段4 字段5
A02b 1 2 3 4 5
A02b 6 7 8 9 10
------最佳解决方案--------------------
搞个建表和查数据额语句出来。那么多数据,懒得给你整
------其他解决方案--------------------
先3表联合,然后行转列
------其他解决方案--------------------
with TB as (
select a.locationcode,c.codedetail,b.Tallydata
from table1 as a inner join table2 as b on a.id=b.detail_id inner join table3 as c on a.locationcode=c.locationcode)
select *
from TB
pivot(max(Tallydata) for codedetail in ([字段1],[字段2],[字段3],[字段4],[字段5])) as X
------其他解决方案--------------------
这个设计的不好
------其他解决方案--------------------
table1
Id LocationCode
1 A02b
2 A02b
table2
detail_Id Tallydata
1 1
1 2
1 3
1 4
1 5
2 6
2 7
2 8
2 9
2 10
table1和table2中Id与detail_Id关联,
table3
LocationCode CodeDetail
A02b 字段1
A02b 字段2
A02b 字段3
A02b 字段4
A02b 字段5
table1和table3中LocationCode关联
我想得到如下表,怎么弄?
LocationCode 字段1 字段2 字段3 字段4 字段5
A02b 1 2 3 4 5
A02b 6 7 8 9 10
------最佳解决方案--------------------
搞个建表和查数据额语句出来。那么多数据,懒得给你整
------其他解决方案--------------------
先3表联合,然后行转列
------其他解决方案--------------------
with TB as (
select a.locationcode,c.codedetail,b.Tallydata
from table1 as a inner join table2 as b on a.id=b.detail_id inner join table3 as c on a.locationcode=c.locationcode)
select *
from TB
pivot(max(Tallydata) for codedetail in ([字段1],[字段2],[字段3],[字段4],[字段5])) as X
------其他解决方案--------------------
这个设计的不好
------其他解决方案--------------------