如何在SQL中显示来自两个不同表的数据？

问题描述：

我有两个不同的表，如下所示，第一个表是存储工作日，第二个表是存储假期，两个表都有公共字段branchID（int）。

===== =============================================

I have two different tables shown below , first table is to store working days and second table is to store holidays , both tables are having common field branchID(int).
==================================================

SELECT [Sno]
      ,[CompanyID]
      ,[BranchID]
      ,[Date]
      ,[Day]
      ,[WorkingDay]

  FROM [HRMS].[dbo].[tblWorkingDayList]

================ =================================

=================================================

SELECT  [Sno]
      ,[Occasion]
      ,[Day]
      ,[Date]
      ,[CompanyID]
      ,[BranchID]

  FROM [HRMS].[dbo].[tblHolidayList]

============================================ ======

从以上两个表中我想将它们组合起来d生成如下

-------------------------------------- --------------------

BranchID |日期| workingDay |场合|

---------------------------------------- ------------------

1 16/09/2014是 -

1 17/09/2014否假日

1 18/09/2014是 -

请帮帮我

谢谢Advance

==================================================

From the Above two tables i want to combine them and generate like below
----------------------------------------------------------
BranchID | Date | workingDay | Occasion|
----------------------------------------------------------
1 16/09/2014 yes --
1 17/09/2014 no Holiday
1 18/09/2014 yes --

Please help me
Thanks Advance

答

看看 TechNet上的使用内部联接 [ ^ ]。

请试试这个，

Hi, Please try this,

SELECT [Sno],[CompanyID],[BranchID],[Date],[Day],'YES' as [WorkingDay], '--' as [Occasion] FROM tblWorkingDayList
Union all
Select [Sno],[CompanyID],[BranchID],[Date],[Day],'NO' as [WorkingDay], [Occasion] FROM tblHolidayList
Order by date

和更新 - 祝你好运

and update -- Good luck

你好，

我假设

1）当有工作日时，你将它存储在表[HRMS]中的[WorkingDay]列1中。[dbo]。[tblWorkingDayList]。

2）当它出现时，你将它存储在表[HRMS]的[WorkingDay]列中为0 。[dbo]。[tblWorkingDayList]。

3）如果是每周休息一天那么表格tblWorkingDayList和tblHolidayList

Hello ,
I am assuming that
1)when there is a working Day you are storing it as 1 in column [WorkingDay] in table [HRMS].[dbo].[tblWorkingDayList].
2)When itis Occassion , you are storing it as zero in in column [WorkingDay] in table [HRMS].[dbo].[tblWorkingDayList].
3)If it is weekly off Day then Entry exists in both table tblWorkingDayList and tblHolidayList

SELECT  t.Working,
        t.BranchId,
        t.Working,
        t.Occasion
FROM    ( SELECT    wl.[Date],
                    'YES' [Working],
                    BranchId,
                    '' Occasion
          FROM      tblWorkingDayList wl
          WHERE     wl.Date NOT IN ( SELECT DATE
                                     FROM   tblHolidayList
                                     WHERE  wl.BranchId = BranchId )
          UNION ALL
          SELECT    h1.DATE,
                    'NO' [Working],
                    BranchID,
                    Occasion
          FROM      tblHolidayList h1
        ) t

希望它可以帮到你

Hope it helps you

如何在SQL中显示来自两个不同表的数据？

相关推荐