如何在SQL中编写查询以显示列的最大数量 [英] How to write a query in SQL to display max number of column's

问题描述

我有两个表，如DataHeader和DataDetail

i想要显示DataDetail表中的备注，它们具有高DDid

，如果DataDetail中没有备注，那么我想要显示来自DataHeader表的备注



如何用SQL编写查询



DataHeader

DHid A BC备注

1 abc 123 xyz DHidR1

2 bcd 252 jhyvb DHidR2

3 efg 541 sdh DHidR3



DataDetail

DHid DDid备注

1 1工作

1 2工作

1 3工作

2 1备注1

2 2备注2

2 3备注3

2 4备注4

2 5备注5



i希望显示数据如

DHid A B C备注
1 abc 123 xyz工作

2 bcd 252 jhyvb备注5

3 efg 541 sdh DHidR3







Sry I Cant写表格式



我尝试了什么：



i试了试外连接到Dataheader，我只显示数据头注释

I have two tables like DataHeader and DataDetail
i want to display the Remarks from DataDetail table which are having high DDid
and if remarks not available in DataDetail then I want to display Remarks from DataHeader Table

how to write query in SQL

DataHeader
DHid A B C Remarks
1 abc 123 xyz DHidR1
2 bcd 252 jhyvb DHidR2
3 efg 541 sdh DHidR3

DataDetail
DHid DDid Remarks
1 1 working
1 2 working
1 3 worked
2 1 Remarks1
2 2 Remarks2
2 3 Remarks3
2 4 Remarks4
2 5 Remarks5

i want to display the data like
DHid A B C Remarks
1 abc 123 xyz worked
2 bcd 252 jhyvb Remarks5
3 efg 541 sdh DHidR3



Sry I Cant write tables format

What I have tried:

i have tried left outer join to Dataheader and i displaying dataheader remarks only

推荐答案

这样的东西：

Something like this:

SELECT
    DH.DHid,
    DH.A,
    DH.B,
    DH.C,
    IsNull(
        (
            SELECT TOP 1 DD.Remarks
            FROM DataDetail As DD
            WHERE DD.DHid = DH.DHid
            And DD.Remarks Is Not Null
            ORDER BY DD.DDid DESC
        ),
        DH.Remarks
    ) As Remarks
FROM
    DataHeader As DH
;

或：

Or:

WITH cteDetail As
(
    SELECT
        ROW_NUMBER() OVER (PARTITION BY DHid ORDER BY DDid DESC) As RN,
        DHid,
        Remarks
    FROM
        DataDetail
    WHERE
        Remarks Is Not Null
)
SELECT
    DH.DHid,
    DH.A,
    DH.B,
    DH.C,
    IsNull(DD.Remarks, DH.Remarks) As Remarks
FROM
    DataHeader As DH
    LEFT JOIN cteDetail As DD
    ON DD.DHid = DH.DHid
    And DD.RN = 1
;

这篇关于如何在SQL中编写查询以显示列的最大数量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！