代码之家 › 专栏 › 技术社区 › Doonie Darkoo

按日期区分

sql-server

Doonie Darkoo · 技术社区 · 6 年前

我在这里有一个家伙的帮助下编写了这个表结构和查询,它工作得很好,但是我想根据日期来排序行。以下是结构和示例数据:

DROP TABLE [TransactionMaster];
DROP TABLE [VoucherType];

CREATE TABLE [VoucherType](
    [VoucherTypeCode] [tinyint] NOT NULL PRIMARY KEY,
    [FullName] [nvarchar](255) NOT NULL
);


INSERT INTO [VoucherType] VALUES (1, 'Cash Payment Voucher');
INSERT INTO [VoucherType] VALUES (2, 'Cash Receipt Voucher');
INSERT INTO [VoucherType] VALUES (3, 'Bank Payment Voucher');
INSERT INTO [VoucherType] VALUES (4, 'Bank Receipt Voucher');


CREATE TABLE [TransactionMaster](
    [ID] [bigint] NOT NULL PRIMARY KEY,
    [VoucherTypeCode] [tinyint] NOT NULL,
    [PayeeName] [varchar](255) NOT NULL,
    [TransactionDate] datetime,
    [RefNo] [nvarchar](50) NULL
    CONSTRAINT [FK_tbl_TransactionMaster_tbl_VoucherType] FOREIGN KEY([VoucherTypeCode])
    REFERENCES [VoucherType] ([VoucherTypeCode])
)



INSERT INTO [TransactionMaster] VALUES (1, 2, 'Asim', '2018-03-21', 'CRV-0001-LHR');
INSERT INTO [TransactionMaster] VALUES (2, 4, 'Ali', '2018-03-21', 'BRV-2421-KHI');
INSERT INTO [TransactionMaster] VALUES (3, 1, 'Erick', '2018-03-23', 'CPV-5435-ISL');
INSERT INTO [TransactionMaster] VALUES (4, 3, 'Asim', '2018-03-24', 'BPV-2345-CAN');
INSERT INTO [TransactionMaster] VALUES (5, 2, 'Mehboob', '2018-03-25', 'CRV-2976-PSH');
INSERT INTO [TransactionMaster] VALUES (6, 1, 'Erick', '2018-03-25', 'CPV-2323-KOH');

这是询问

SELECT tb1.Refno
  ,tb1.[FullName] AS VType
  ,tb1.PayeeName
  ,tb2.Refno
  ,tb2.[FullName] AS VType
  ,tb2.PayeeName
FROM 
  (
    (
      SELECT 
         ROW_NUMBER() OVER ( ORDER BY id ) AS rowid
        ,TransactionMaster.RefNo
        ,TransactionMaster.PayeeName
        ,[VoucherType].FullName
      FROM 
        TransactionMaster
      INNER JOIN 
        [VoucherType] 
          ON TransactionMaster.VoucherTypeCode = [VoucherType].VoucherTypeCode
      WHERE TransactionMaster.[VoucherTypeCode] IN ( 1,3 )
    ) tb1 
    FULL OUTER JOIN 
      (
        SELECT 
           ROW_NUMBER() OVER ( ORDER BY id ) AS rowid
          ,TransactionMaster.RefNo
          ,TransactionMaster.PayeeName
          ,[VoucherType].FullName
        FROM 
          TransactionMaster
        INNER JOIN 
          [VoucherType] 
            ON TransactionMaster.VoucherTypeCode = [VoucherType].VoucherTypeCode
        WHERE TransactionMaster.[VoucherTypeCode] IN ( 2,4 )
      ) tb2 ON tb1.rowid = tb2.rowid
  )

现在的输出如下:

Refno           VType                   PayeeName   TransactionDate         Refno           VType                   PayeeName   TransactionDate

CPV-5435-ISL    Cash Payment Voucher    Erick       2018-03-23 00:00:00.000 CRV-0001-LHR    Cash Receipt Voucher    Asim        2018-03-21 00:00:00.000
BPV-2345-CAN    Bank Payment Voucher    Asim        2018-03-24 00:00:00.000 BRV-2421-KHI    Bank Receipt Voucher    Ali         2018-03-21 00:00:00.000
CPV-2323-KOH    Cash Payment Voucher    Erick       2018-03-25 00:00:00.000 CRV-2976-PSH    Cash Receipt Voucher    Mehboob     2018-03-25 00:00:00.000

我想按升序显示日期,如果该日期没有凭证,则该日期应为空。我把预期产量放在下面

Refno           VType                   PayeeName   TransactionDate                 Refno           VType                   PayeeName   TransactionDate

                                                                                    CRV-0001-LHR    Cash Receipt Voucher    Asim        2018-03-21 00:00:00.000
                                                                                    BRV-2421-KHI    Bank Receipt Voucher    Ali         2018-03-21 00:00:00.000
CPV-5435-ISL    Cash Payment Voucher    Erick       2018-03-23 00:00:00.000
BPV-2345-CAN    Bank Payment Voucher    Asim        2018-03-24 00:00:00.000 
CPV-2323-KOH    Cash Payment Voucher    Erick       2018-03-25 00:00:00.000         CRV-2976-PSH    Cash Receipt Voucher    Mehboob     2018-03-25 00:00:00.000

2 回复 | 直到 6 年前

SqlKindaGuy 6 年前

with data1 as (
select a.id,a.VoucherTypeCode,PayeeName,TransactionDate,RefNo,FullName from TransactionMaster a inner join  [VoucherType] b on a.VoucherTypeCode = b.VoucherTypeCode
where a.VoucherTypeCode in (1,3)
),

data2 as (
select a.id,a.VoucherTypeCode,PayeeName,TransactionDate,RefNo,FullName from TransactionMaster a inner join  [VoucherType] b on a.VoucherTypeCode = b.VoucherTypeCode
where a.VoucherTypeCode in (2,4)
)
select *,COALESCE(a.TransactionDate,b.TransactionDate) as FullDate from data1 a full join data2 b on a.TransactionDate = b.TransactionDate
order by FullDate

Gaj 6 年前

select tb1.Refno, 
        tb1.[FullName] as VType, 
        tb1.PayeeName, 
        tb2.Refno, 
        tb2.[FullName] as VType, 
        tb2.PayeeName 
from(
        (
            select ROW_NUMBER()over (partition by TransactionDate order by id) as rowid, 
                    TransactionMaster.RefNo, 
                    TransactionMaster.PayeeName, 
                    [VoucherType].FullName,
                  TransactionMaster.TransactionDate
            from TransactionMaster 
                    inner join [VoucherType] on  TransactionMaster.VoucherTypeCode = [VoucherType].VoucherTypeCode
            where TransactionMaster.[VoucherTypeCode] in (1,3)
        )tb1
            full outer join (
                                select ROW_NUMBER()over (partition by TransactionDate order by id) as rowid, 
                                        TransactionMaster.RefNo, 
                                        TransactionMaster.PayeeName, 
                                        [VoucherType].FullName,
                                    TransactionMaster.TransactionDate
                                from TransactionMaster 
                                        inner join [VoucherType] on  TransactionMaster.VoucherTypeCode = [VoucherType].VoucherTypeCode
                                where TransactionMaster.[VoucherTypeCode] in (2,4)
                            ) tb2 on tb1.rowid = tb2.rowid and tb1.TransactionDate = tb2.TransactionDate
)