sql查询以在简单的两个表中的let表中获取不同的行

2021-09-10 00:00:00 sql tsql sql-server 
CREATE TABLE [dbo].[tbl_Travel](
    [TE_ID] [int] IDENTITY(1,1) NOT NULL,
    [TRAVEL_TYPE] [varchar](12) NULL,
    [TRAVEL_MODE] [varchar](35) NULL,
    [TRAVEL_CLASS] [nchar](10) NULL)

SET PRIMARY KEY TO TE_ID

INSERT INTO [TAMSMVC].[dbo].[tbl_Travel] VALUES  ('Return', 'Airlines', 'Economy')
INSERT INTO [TAMSMVC].[dbo].[tbl_Travel] VALUES  ('Single', 'Airlines', 'Business')
INSERT INTO [TAMSMVC].[dbo].[tbl_Travel] VALUES  ('Return', 'Airlines', 'Business')
INSERT INTO [TAMSMVC].[dbo].[tbl_Travel] VALUES  ('Single', 'Railway', 'Second')
INSERT INTO [TAMSMVC].[dbo].[tbl_Travel] VALUES  ('Return', 'Railway', 'First')


CREATE TABLE [dbo].[tbl_Journey](
    [JOURNET_ID] [int] IDENTITY(1,1) NOT NULL,
    [TE_ID] [int] NULL,
    [JOURNEY_FROM] [varchar](30) NULL,
    [JOURNEY_TO] [varchar](30) NULL)

将主密钥设置为 [JOURNET_ID]

SET PRIMARY KEY TO [JOURNET_ID]

INSERT INTO [TAMSMVC].[dbo].[tbl_Journey] VALUES (1,'Mumbai','PUNE')
INSERT INTO [TAMSMVC].[dbo].[tbl_Journey] VALUES (1,'PUNE','Mumbai')
INSERT INTO [TAMSMVC].[dbo].[tbl_Journey] VALUES (2,'BANGALORE','GOA')
INSERT INTO [TAMSMVC].[dbo].[tbl_Journey] VALUES (3,'CHENNAI','PANAJI')
INSERT INTO [TAMSMVC].[dbo].[tbl_Journey] VALUES (3,'PANAJI','CHENNAI')
INSERT INTO [TAMSMVC].[dbo].[tbl_Journey] VALUES (4,'DELHI','KOLKATA')
INSERT INTO [TAMSMVC].[dbo].[tbl_Journey] VALUES (5,'BHOPAL','SHIMALA')
INSERT INTO [TAMSMVC].[dbo].[tbl_Journey] VALUES (5,'SHIMALA','BHOPAL')

结果应该是我想要表中不同的行

AND RESULT SHOULD BE i want distinct rows in the table 

Journey_ID  TE_ID   Journey_From    Journey_To  TRAVEL_TYPE [TRAVEL_MODE    TRAVEL_CLASS
  1          1       Mumbai          PUNE         Return      Airlines        Economy   
  3          2       BANGALORE       GOA          Single      Airlines         Business  
  4          3       CHENNAI        PANAJI        Return      Airlines         Business  
  6          4       DELHI          KOLKATA       Single      Railway          Second    
  7          5       BHOPAL         SHIMALA       Return      Railway          First

结果应该是我想要表中的不同行我想删除第二个表中的重复行，并且最小它应该显示包含 min travel_id 的行

AND RESULT SHOULD BE i want distinct rows in the table i want to remove duplicate rows in second table and min it should show min journey_id contained rows

推荐答案

Journey_ID  TE_ID   Journey_From    Journey_To  TRAVEL_TYPE [TRAVEL_MODE    TRAVEL_CLASS    CountofTEID
1   1   Mumbai  PUNE        Return  Airlines    Economy     2
3   2   BANGALORE   GOA         Single  Airlines    Business    1
4   3   CHENNAI PANAJI      Return  Airlines    Business    2
6   4   DELHI   KOLKATA     Single  Railway Second      1
7   5   BHOPAL  SHIMALA     Return  Railway First       2
null    6   null    null    Return  Airlines    Economy     0
null    7   null    null    Return  Airlines    Business    0

结果我想要 Travel table 中的所有行，所有旅程的计数和具有最小 travel_id 的单个记录

In result Iwant all rows from Travel table , count of all journeys and single record with minimum journey_id

相关文章