从加入数据创建SQL表 [英] SQL Table Creation from Joining Data
问题描述
在我提出问题之前，以下是有关我的数据的信息：
表名：dbo.DecodedCSVMessages_Staging
栏：MMSI，Message_ID，Time，Vessel_Name，Ship_Type，IMO，Dimension_to_Bow，Dimension_to_stern，Dimension_to_port，Dimension_to_starboard，Draft，Longitude，Latitude
我需要创建一个新表。下面是我需要在表中：
我对所有这些数据感兴趣，但我只需要Message_ID是1或3. **问题是，Message_ID的1和3 **缺少：（仅适用于Message_ID的5）
Vessel_Name，Ship_Type，IMO，
Dimension_to_Bow，
Dimension_to_stern，
Dimension_to_port，
Dimension_to_starboard，
Draft
对于Message_ID的1和3，这些列标记为NULL。他们都是
经度，
纬度，
时间，
MMSI
（所有标记为NULL ，等于 Message_ID等于5 ）
MMSI是此实例中的主键。 Message_ID的1,3和5都将具有代表给定船的MMSI编号。这些MMSI是重复的，因为每艘船发出类型1,3和5的多个消息。因此，我们有一个MMSI 210293000，这个号码将与几个Message_ID的不同类型。所以我需要做的是获取所有的Message_ID是1和3，并将Message_ID的信息附加到5的1和3。所以在那里，列不再是NULL。
最后但并非最不重要的是，我只能选择Message_ID 1和3的以下内容：
其中Latitude> 55和纬度<85和经度> 50和Longitude 141;
几个列的外观示例：
MMSI / Message_ID / Time / Ship_type / Vessel_Name / Latitude / Longitude
21029300，3，2012-06-01，NULL，NULL，56.528003，85.233443
21029300，5，2012-07-01，70，RIO_CUBAL，NULL，NULL
2109300，1，2012-08-01，NULL，NULL，57.432345，131.123343
2109300，1，2012-09-01，NULL，NULL，62.432345，121.123343
2109300，1，2012-09-02，NULL，NULL，65.432345，140.123343
21029300，5，2012-08-01，70，RIO_CUBAL，NULL，NULL
最终结果如下：
21029300，3，2012-06-01，70， RIO_CUBAL，56.528003，85.233443
2109300，1，2012-08-01，70，RIO_CUBAL，57.432345，131.123343
2109300，1，2012-09-01，70， RIO_CUBAL，62.432345，121.123343
2109300，1，2012-09-02，70，RIO_CUBAL，65.432345，140.123343
谢谢！
您可以选择类型1和类型3消息，添加信息从加入到第一个相应类型5记录您的数据。 （如果没有相应的类型5记录，你将得到这些字段的空值。）尝试这样：
SELECT DISTINCT M13。 MMSI，M13.Message_ID，M13.Time，M13.Latitude，M13.Longitude，
M5.Vessel_Name，M5.Ship_Type，M5.IMO，M5.Dimension_to_Bow
M5.Dimension_to_stern，M5.Dimension_to_port，
M5.Dimension_to_starboard，M5.Draught
FROM dbo.DecodedCSVMessages_Staging M13
JOIN（
SELECT MMSI，Time，Vessel_Name，Ship_Type，IMO，Dimension_to_Bow
Dimension_to_stern，Dimension_to_port，Dimension_to_starboard ，
Draft
FROM dbo.DecodedCSVMessages_Staging
WHERE Message_ID = 5
ORDER BY时间
）M5
ON M5.MMSI = M13.MMSI
WHERE M13.Message_ID IN（1,3）
AND M13.Latitude> 55
AND M13.Latitude< 85
AND M13.Longitude> 50
AND M13.Longitude< 141
ORDER BY M13.Time
如果此操作返回所需的数据，新表格，然后使用 INSERT INTO NewTable SELECT 插入记录。
请注意，MMSI不是主键，不管是旧表还是新表。主键（PK）必须是唯一的，在这种情况下，每个MMSI都有多个条目。
创建新表时，应该添加一个整数IDENTITY字段，使其具有PK。此IDENTITY字段不包括在insert语句中，但会自动使用递增的整数填充。
EDITED 可使用 DISTINCT 对外部查询。 Before I lay out the question, here’s the information concerning my data:
Table Name: dbo.DecodedCSVMessages_Staging
Columns: MMSI, Message_ID, Time, Vessel_Name, Ship_Type, IMO, Dimension_to_Bow, Dimension_to_stern, Dimension_to_port, Dimension_to_starboard, Draught, Longitude, Latitude
I need to create a New Table. This following is what I need in the table:
I am interested in all this data, but I only need Message_ID’s that are 1 or 3. ** Problem is, Message_ID’s 1 and 3 **lack the following: (Which is only available with Message_ID's 5.)
Vessel_Name, Ship_Type, IMO,
Dimension_to_Bow,
Dimension_to_stern,
Dimension_to_port,
Dimension_to_starboard,
Draught
For Message_ID’s 1 and 3, those columns are marked NULL. All they have is
Longitude,
Latitude,
Time,
MMSI
(which are all marked NULL for Message_ID's equaling 5)
MMSI is the primary key in this instance. Message_ID’s 1, 3 and 5 will all have MMSI numbers that represent a given ship. These MMSI’s though are reoccurring as each ship sends out multiple Message’s of type 1, 3 and 5. So say we have an MMSI of 210293000, This number will be alongside several Message_ID’s different types. So what I need to do is grab all the Message_ID’s that are 1 and 3 and append the information from the Message_ID’s that are 5 to the1’s and 3’s. So in that, the columns are no longer NULL.
Last but not least, I have to select only Message_ID 1’s and 3’s that fall within the following:
Where Latitude > 55 and Latitude <85 and Longitude > 50 and Longitude < 141;
Example of how a few columns look:
MMSI/ Message_ID /Time/Ship_type/Vessel_Name/Latitude/Longitude
21029300, 3, 2012-06-01, NULL, NULL, 56.528003, 85.233443
21029300, 5, 2012-07-01, 70, RIO_CUBAL, NULL, NULL
2109300, 1, 2012-08-01, NULL, NULL, 57.432345, 131.123343
2109300, 1, 2012-09-01, NULL, NULL, 62.432345, 121.123343
2109300, 1, 2012-09-02, NULL, NULL, 65.432345, 140.123343
21029300, 5, 2012-08-01, 70, RIO_CUBAL, NULL, NULL
The end result would be as follows from this data:
21029300, 3, 2012-06-01, 70, RIO_CUBAL, 56.528003, 85.233443
2109300, 1, 2012-08-01, 70, RIO_CUBAL, 57.432345, 131.123343
2109300, 1, 2012-09-01, 70, RIO_CUBAL, 62.432345, 121.123343
2109300, 1, 2012-09-02, 70, RIO_CUBAL, 65.432345, 140.123343
Thanks!
You can select the Type 1 and Type 3 messages, with added information from a join to the first corresponding Type 5 record in your data. (If there is no corresponding Type 5 record, you will get nulls for those fields.) Try this:
SELECT DISTINCT M13.MMSI, M13.Message_ID, M13.Time, M13.Latitude, M13.Longitude,
M5.Vessel_Name, M5.Ship_Type, M5.IMO, M5.Dimension_to_Bow
M5.Dimension_to_stern, M5.Dimension_to_port,
M5.Dimension_to_starboard, M5.Draught
FROM dbo.DecodedCSVMessages_Staging M13
JOIN (
SELECT MMSI, Time, Vessel_Name, Ship_Type, IMO, Dimension_to_Bow
Dimension_to_stern, Dimension_to_port, Dimension_to_starboard,
Draught
FROM dbo.DecodedCSVMessages_Staging
WHERE Message_ID = 5
ORDER BY Time
) M5
ON M5.MMSI = M13.MMSI
WHERE M13.Message_ID IN (1, 3)
AND M13.Latitude > 55
AND M13.Latitude < 85
AND M13.Longitude > 50
AND M13.Longitude < 141
ORDER BY M13.Time
If this returns the data you want, then create a new table and insert the records using INSERT INTO NewTable SELECT.
Please be aware that MMSI is not a primary key, either in the old table or in the new table. Primary keys (PK) have to be unique, and in this case you have multiple entries for each MMSI.
When you create your new table, you should add an integer IDENTITY field so that it will have a PK. This IDENTITY field isn't included in the insert statement, but it is automatically populated with an incrementing integer. That way each record gets a unique PK, which is highly desirable.
EDITED to use a DISTINCT on the outer query.
这篇关于从加入数据创建SQL表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
