加入三个表格，只有最大日期 [英] JOIN three tabels with only max date
本文介绍了加入三个表格，只有最大日期的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
你好
请帮助
我不能自己做。
我有三张桌子：
Towary，Kontrahenci和TwrIlosci
TWrIlosci表链接到第一个via id并包含不同日期的条目。
我' d只想显示最后日期的条目
我有这个查询，但它显示所有条目
很抱歉，我忘了粘贴数据
< pre>
Table Towary
twr_kod twr_nazwa twr_numerkat twr_koddostawcy twr_iloscmin
MECCZ-TK7598 / A PAL 0.0000
MECCZ-TK7605 NAPY 0.0000
MECCZ-TK7722 KOLO 1 0.0000
MECCZ -TK7723 KOLO 2 0.0000
MECCZ-TK7741 ZEST 0.0000
表Kontrahenci
knt_kod knt_kntid
！NIEOKREŚLONY！ 1
00000 1853
10-05-1039 2239
10-05-1052 2253
10-05-1090 2309
表格TwrIlosci
twi_ilosc twi_rezerwacje twi_zamowienia twi_data TwI_TwrId
0.0000 0.0000 0.0000 2011-05-05 00：00：00.000 57
1.0000 0.0000 0.0000 2011-04-30 00：00：00.000 57
0.0000 0.0000 0.0000 2011-05-05 00：00：00.000 57
1.0000 0.0000 0.0000 2011-04-30 00：00：00.000 57
0.0000 0.0000 0.0000 2017-06-20 00：00：00.000 132
我的查询的示例答案
twr_kod twr_nazwa twr_numerkat twr_koddostawcy knt_kod twr_iloscmin twi_ilosc twi_rezerwacje twi_zamowienia twi_data
MSP321528-0-00 ZTYLNIE 6-10-11-MSP 0.0000 0.0000 0.0000 0.0000 2011-05-05 00:00： 00.000
MSP321528-0-00 ZTYLNIE 6-10-11-MSP 0.0000 1.0000 0.0000 0.0000 2011-04-30 00：00：00.000
MSP321528-0-00 ZTYLNIE 6-10-11-MSP 0.0000 0.0000 0.0000 0.0000 2011-05-05 00：00：00.000
MSP3 21528-0-00 ZTYLNIE 6-10-11-MSP 0.0000 1.0000 0.0000 0.0000 2011-04-30 00：00：00.000
MSPRM100458-010 SRUBA SCREW RM100458-0-10 6-10-11-MSP 0.0000 0.0000 0.0000 0.0000 2017-06-20 00：00：00.000
MSPRM100458-010 SRUBA SCREW RM100458-0-10 6-10-11-MSP 0.0000 0.0000 0.0000 0.0000 2012-05-22 00：00：00.000
MSPRM100458-010 SRUBA SCREW RM100458-0-10 6-10-11-MSP 0.0000 10.0000 0.0000 0.0000 2012-05-17 00：00：00.000
MSPRM100458-010 SRUBA SCREW RM100458-0-10 6-10- 11-MSP 0.0000 0.0000 0.0000 0.0000 2017-06-20 00：00：00.000
MSPRM100458-010 SRUBA SCREW RM100458-0-10 6-10-11-MSP 0.0000 0.0000 0.0000 0.0000 2012-05-22 00:00 ：00.000
MSPRM100458-010 SRUBA SCREW RM100458-0-10 6-10-11-MSP 0.0000 10.0000 0.0000 0.0000 2012-05-17 00：00：00.000
MSPRM101572-012 CHWYTACZ C 6-10- 11-MSP 0.0000 0.0000 0.0000 0.0000 2015-05-07 00：00：00.000
MSPRM101572-012 CHWYTACZ C 6-10-11-MSP 0.0000 0.0000 0.0000 0.0000 2015-05-07 00：00：00.000
MSPRM202092-0-1 CHWYTACZ G 6-10-11-MSP 0.000 0 0.0000 0.0000 0.0000 2011-05-05 00：00：00.000
MSPRM202092-0-1 CHWYTACZ G 6-10-11-MSP 0.0000 1.0000 0.0000 0.0000 2011-04-30 00：00：00.000
MSPRM202092-0-1 CHWYTACZ G 6-10-11-MSP 0.0000 0.0000 0.0000 0.0000 2011-05-05 00：00：00.000
MSPRM202092-0-1 CHWYTACZ G 6-10-11-MSP 0.0000 1.0000 0.0000 0.0000 2011-04-30 00：00：00.000
MSPRM202095-013 N DOLNY 6-10-11-MSP 0.0000 0.0000 0.0000 0.0000 2016-10-17 00：00：00.000
MSPRM202095-013 N DOLNY 6 -10-11-MSP 0.0000 0.0000 0.0000 0.0000 2015-10-19 00：00：00.000
MSPRM202095-013 N DOLNY 6-10-11-MSP 0.0000 0.0000 0.0000 0.0000 2016-10-17 00：00：00.000
MSPRM202095-013 N DOLNY 6-10-11-MSP 0.0000 0.0000 0.0000 0.0000 2015-10-19 00：00：00.000
我只想：
twr_kod twr_nazwa twr_numerkat twr_koddostawcy knt_kod twr_iloscmin twi_ilosc twi_rezerwacje twi_zamowienia twi_data
MSP321528-0-00 ZTYLNIE 6-10-11- MSP 0.0000 0.0000 0.0000 0.0000 2011-05-05 00:00:00。 000
MSPRM100458-010 SRUBA SCREW RM100458-0-10 6-10-11-MSP 0.0000 0.0000 0.0000 0.0000 2017-06-20 00：00：00.000
MSPRM101572-012 CHWYTACZ C 6-10-11 -MSP 0.0000 0.0000 0.0000 0.0000 2015-05-07 00：00：00.000
MSPRM202092-0-1 CHWYTACZ G 6-10-11-MSP 0.0000 0.0000 0.0000 0.0000 2011-05-05 00：00：00.000
MSPRM202095-013 N DOLNY 6-10-11-MSP 0.0000 0.0000 0.0000 0.0000 2016-10-17 00：00：00.000
我的尝试：
SELECT
twr_kod，twr_nazwa，twr_numerkat，twr_koddostawcy，knt_kod，twr_iloscmin，twi_ilosc，twi_rezerwacje，twi_zamowienia，twi_data
FROM （（XL.Towary
INNER JOIN XL.Kontrahenci ON XL.Towary.twr_kntid = XL.Kontrahenci.knt_kntid）
INNER JOIN
XL。 TwrIlosci ON XL.TOWARY.Twr_TwrId = XL.TwrIlosci.TwI_TwrId）
解决方案
< blockquote>似乎你需要使用 MAX（）OVER（） [ ^ ]：
SELECT DISTINCT twr_kod，twr_nazwa，twr_numerkat，twr_koddostawcy，knt_kod，twr_iloscmin，twi_ilosc，twi_rezerwacje，twi_zamowienia，twi_data
FROM （
SELECT TwrI_TwrId，twi_ilosc，twi_rezerwacje，twi_zamowienia，MAX（twi_data） OVER （ PARTITION BY TwI_TwrId O RDER BY twi_data DESC ） AS twi_data
FROM XL.TwrIlosci
） AS TWI
INNER JOIN XL.TOWARY AS TWR < span class =code-keyword> ON TWR.Twr_TwrId = TWI.TwI_TwrId
INNER 加入 XL.Kontrahenci AS K ON TWR.twr_kntid = K.knt_kntid
尝试：
SELECT TOP 1 ... ORDER BY MyDateColumn DESC你的阙ry符合省略号。
Hello
Please help
I can't do it by myself.
I have three tables:
Towary, Kontrahenci and TwrIlosci
The TWrIlosci table is linked to the first via id and contains entries with different dates.
I'd like to display only the entries for the last date
I have this query but it shows all entries
Sorry for that I forgot to paste data <pre> Table Towary twr_kod twr_nazwa twr_numerkat twr_koddostawcy twr_iloscmin MECCZ-TK7598/A PAL 0.0000 MECCZ-TK7605 NAPY 0.0000 MECCZ-TK7722 KOLO 1 0.0000 MECCZ-TK7723 KOLO 2 0.0000 MECCZ-TK7741 ZEST 0.0000
Table Kontrahenci knt_kod knt_kntid !NIEOKREŚLONY! 1 00000 1853 10-05-1039 2239 10-05-1052 2253 10-05-1090 2309
Table TwrIlosci twi_ilosc twi_rezerwacje twi_zamowienia twi_data TwI_TwrId 0.0000 0.0000 0.0000 2011-05-05 00:00:00.000 57 1.0000 0.0000 0.0000 2011-04-30 00:00:00.000 57 0.0000 0.0000 0.0000 2011-05-05 00:00:00.000 57 1.0000 0.0000 0.0000 2011-04-30 00:00:00.000 57 0.0000 0.0000 0.0000 2017-06-20 00:00:00.000 132
An example answer to my query
twr_kod twr_nazwa twr_numerkat twr_koddostawcy knt_kod twr_iloscmin twi_ilosc twi_rezerwacje twi_zamowienia twi_data MSP321528-0-00 ZTYLNIE 6-10-11-MSP 0.0000 0.0000 0.0000 0.0000 2011-05-05 00:00:00.000 MSP321528-0-00 ZTYLNIE 6-10-11-MSP 0.0000 1.0000 0.0000 0.0000 2011-04-30 00:00:00.000 MSP321528-0-00 ZTYLNIE 6-10-11-MSP 0.0000 0.0000 0.0000 0.0000 2011-05-05 00:00:00.000 MSP321528-0-00 ZTYLNIE 6-10-11-MSP 0.0000 1.0000 0.0000 0.0000 2011-04-30 00:00:00.000 MSPRM100458-010 SRUBA SCREW RM100458-0-10 6-10-11-MSP 0.0000 0.0000 0.0000 0.0000 2017-06-20 00:00:00.000 MSPRM100458-010 SRUBA SCREW RM100458-0-10 6-10-11-MSP 0.0000 0.0000 0.0000 0.0000 2012-05-22 00:00:00.000 MSPRM100458-010 SRUBA SCREW RM100458-0-10 6-10-11-MSP 0.0000 10.0000 0.0000 0.0000 2012-05-17 00:00:00.000 MSPRM100458-010 SRUBA SCREW RM100458-0-10 6-10-11-MSP 0.0000 0.0000 0.0000 0.0000 2017-06-20 00:00:00.000 MSPRM100458-010 SRUBA SCREW RM100458-0-10 6-10-11-MSP 0.0000 0.0000 0.0000 0.0000 2012-05-22 00:00:00.000 MSPRM100458-010 SRUBA SCREW RM100458-0-10 6-10-11-MSP 0.0000 10.0000 0.0000 0.0000 2012-05-17 00:00:00.000 MSPRM101572-012 CHWYTACZ C 6-10-11-MSP 0.0000 0.0000 0.0000 0.0000 2015-05-07 00:00:00.000 MSPRM101572-012 CHWYTACZ C 6-10-11-MSP 0.0000 0.0000 0.0000 0.0000 2015-05-07 00:00:00.000 MSPRM202092-0-1 CHWYTACZ G 6-10-11-MSP 0.0000 0.0000 0.0000 0.0000 2011-05-05 00:00:00.000 MSPRM202092-0-1 CHWYTACZ G 6-10-11-MSP 0.0000 1.0000 0.0000 0.0000 2011-04-30 00:00:00.000 MSPRM202092-0-1 CHWYTACZ G 6-10-11-MSP 0.0000 0.0000 0.0000 0.0000 2011-05-05 00:00:00.000 MSPRM202092-0-1 CHWYTACZ G 6-10-11-MSP 0.0000 1.0000 0.0000 0.0000 2011-04-30 00:00:00.000 MSPRM202095-013 N DOLNY 6-10-11-MSP 0.0000 0.0000 0.0000 0.0000 2016-10-17 00:00:00.000 MSPRM202095-013 N DOLNY 6-10-11-MSP 0.0000 0.0000 0.0000 0.0000 2015-10-19 00:00:00.000 MSPRM202095-013 N DOLNY 6-10-11-MSP 0.0000 0.0000 0.0000 0.0000 2016-10-17 00:00:00.000 MSPRM202095-013 N DOLNY 6-10-11-MSP 0.0000 0.0000 0.0000 0.0000 2015-10-19 00:00:00.000
I would like to have only:
twr_kod twr_nazwa twr_numerkat twr_koddostawcy knt_kod twr_iloscmin twi_ilosc twi_rezerwacje twi_zamowienia twi_data MSP321528-0-00 ZTYLNIE 6-10-11-MSP 0.0000 0.0000 0.0000 0.0000 2011-05-05 00:00:00.000 MSPRM100458-010 SRUBA SCREW RM100458-0-10 6-10-11-MSP 0.0000 0.0000 0.0000 0.0000 2017-06-20 00:00:00.000 MSPRM101572-012 CHWYTACZ C 6-10-11-MSP 0.0000 0.0000 0.0000 0.0000 2015-05-07 00:00:00.000 MSPRM202092-0-1 CHWYTACZ G 6-10-11-MSP 0.0000 0.0000 0.0000 0.0000 2011-05-05 00:00:00.000 MSPRM202095-013 N DOLNY 6-10-11-MSP 0.0000 0.0000 0.0000 0.0000 2016-10-17 00:00:00.000
What I have tried:
SELECT
twr_kod, twr_nazwa, twr_numerkat, twr_koddostawcy, knt_kod, twr_iloscmin, twi_ilosc, twi_rezerwacje, twi_zamowienia, twi_data
FROM ((XL.Towary
INNER JOIN XL.Kontrahenci ON XL.Towary.twr_kntid = XL.Kontrahenci.knt_kntid)
INNER JOIN
XL.TwrIlosci ON XL.TOWARY.Twr_TwrId = XL.TwrIlosci.TwI_TwrId) 解决方案
Seems that you need to use MAX() OVER()[^]:
SELECT DISTINCT twr_kod, twr_nazwa, twr_numerkat, twr_koddostawcy, knt_kod, twr_iloscmin, twi_ilosc, twi_rezerwacje, twi_zamowienia, twi_data FROM ( SELECT TwrI_TwrId, twi_ilosc, twi_rezerwacje, twi_zamowienia, MAX(twi_data) OVER(PARTITION BY TwI_TwrId ORDER BY twi_data DESC) AS twi_data FROM XL.TwrIlosci ) AS TWI INNER JOIN XL.TOWARY AS TWR ON TWR.Twr_TwrId = TWI.TwI_TwrId INNER JOIN XL.Kontrahenci AS K ON TWR.twr_kntid = K.knt_kntid
Try:
SELECT TOP 1 ... ORDER BY MyDateColumn DESCwhere your query fits in the ellipsis.
这篇关于加入三个表格，只有最大日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
