sql query to get distinct rows in let table in sim

问题:

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)

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     

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

回答1:

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

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

回答2:

You know, rows

INSERT INTO [TAMSMVC].[dbo].[tbl_Journey] VALUES (1,'Mumbai','PUNE')

INSERT INTO [TAMSMVC].[dbo].[tbl_Journey] VALUES (1,'PUNE','Mumbai')

are not duplicate! They have different ID's (JOURNET_ID)

But if you really want it

  select a.JOURNET_ID, c.te_id, a.JOURNEY_FROM, a.JOURNEY_TO, c.TRAVEL_TYPE, c.TRAVEL_MODE, c.TRAVEL_CLASS ,
(select count(1) from tbl_Journey where te_id=c.te_id)  CountofTEID
from  tbl_Travel c left join tbl_Journey a on a.TE_ID=c.TE_ID 

where JOURNET_ID is null or JOURNET_ID = 
(
    select min(JOURNET_ID) from tbl_Journey b where 
    (a.JOURNEY_FROM=b.JOURNEY_FROM and a.JOURNEY_TO=b.JOURNEY_TO)
    or (a.JOURNEY_FROM=b.JOURNEY_TO and a.JOURNEY_TO=b.JOURNEY_FROM)
)