使用sql将缺少的值插入表

fae0ux8s 于 2021-07-24 发布在 Java

关注(0)|答案(2)|浏览(266)

目标：我需要通过[propertyid]分组，用每一列的值矩阵完全填充一个表。有几个[propertyid]具有每列所需的所有值（表1），但是，很多都缺少一些值（表2）。此外，并非每个[propertyid]都需要这些值，因为它们具有完全不同的区域值。因此，我需要确定哪些[propertyid]既需要填充值，又没有所有必需的值。
示例：表1。每个已标识的[propertyid]分组应具有这四列的23条不同记录[reportingvolumesettingid]、[speciesgroupinventoryid]、[cropcategoryid]、[sortorder]。

表2。以下是缺少值组合的propertyid的示例，因为它只有22条记录：

这两个示例结果都是从同一个表[reportingvolume]查询的。我甚至没有成功地确定每个[propertyid]缺少哪个记录组合。我想确定每个丢失的记录组合，然后将该记录组合插入到[reportingvolume]表中。
要解决的问题——下面的sql代码是我试图解决的问题。确定正确的值列表；2.确定哪些属性应具有匹配值；3.确定哪些属性缺少值；4.确定每个属性缺少的值。

;with CORRECT_LIST as
(
select 
SpeciesGroupInventoryName, SpeciesGroupInventoryId, CropCategoryName,CropCategoryID, UnitOfMeasure, SortOrder
--*
from [GIS].[RST].[vPropertyDefaultTimberProductAndUnitOfMeasure]
where PropertyId in (1)
)
,
property_list as
(
select distinct rvs.propertyid as Volume_Property, pd.PropertyName, pd.PropertyId from RMS.GIS.ReportingVolumeSetting rvs
right outer JOIN RMS.GIS.PropertyDetail AS pd ON rvs.PropertyId = pd.PropertyId 
left outer  JOIN RMS.GIS.SpeciesGroupInventory AS sgi ON rvs.SpeciesGroupInventoryId = sgi.SpeciesGroupInventoryId
where sgi.SpeciesGroupInventoryId in (1,2,3)
or pd.PropertyId = 171
)
, Partial_LISTS as
(
select Count(distinct ReportingVolumeSettingId) as CNT_REPORT, pd.PropertyName, pd.PropertyId
from [GIS].[ReportingVolumeSetting] rvs
right outer JOIN property_list AS pd ON rvs.PropertyId = pd.PropertyId 
group by pd.propertyId, pd.PropertyName
)
, Add_Props as
(
select propertyName, propertyId, SUM(CNT_REPORT) as CNT_RECORDS from Partial_LISTS
where CNT_REPORT < 23
group by propertyName, propertyId
)
, RVS_RECORDS_PROPS as
(
select addProps.PropertyName, rvs.* from [GIS].[ReportingVolumeSetting] rvs
join Add_Props addProps on addprops.PropertyId = rvs.PropertyID
where rvs.PropertyId in (select PropertyId from Add_Props)
)
select rp.PropertyName, cl.*, rp.SpeciesGroupInventoryId from correct_list cl
left outer join RVS_Records_Props rp 
    on rp.SpeciesGroupInventoryId = cl.SpeciesGroupInventoryId
    and rp.CropCategoryId = cl.CropCategoryID
    and rp.SortOrder = cl.SortOrder
Order by rp.PropertyName

如何修改代码或创建新的代码块来标识缺少的值并将它们插入到每个propertyid的表中？
我使用的是sqlsmssv15。
非常感谢。

sql sql-server sql-insert

来源：https://stackoverflow.com/questions/62839979/insert-missing-values-into-table-using-sql

2条答案

按热度按时间

o4hqfura1#

这应该可以识别丢失的条目。您只需在上面添加一个insert-into命令即可。请记住，由于reportingvolumesettingid是唯一且未知的，因此此处不包括它。

SELECT * FROM (SELECT DISTINCT PropertyId FROM ReportingVolume) rv
CROSS APPLY 
(
    SELECT DISTINCT SpeciesGroupInventoryId
        , CropCategoryId
        , SortOrder
    FROM ReportingVolume
) x
EXCEPT
SELECT PropertyId, SpeciesGroupInventoryId, CropCategoryId, SortOrder FROM ReportingVolume

赞(0）回复(0）举报 2021-07-24

cbjzeqam2#

我无法访问您的数据，因此无法提供特定于您的环境的示例，但我可以使用SQLServer的 EXCEPT 接线员。
在ssms中运行以下示例：

-- Create a list of required values.
DECLARE @Required TABLE ( required_id INT, required_val VARCHAR(50) );
INSERT INTO @Required ( required_id, required_val ) VALUES
    ( 1, 'Required value 1.' ), ( 2, 'Required value 2.' ), ( 3, 'Required value 3.' ), ( 4, 'Required value 4.' ), ( 5, 'Required value 5.' );

-- Create some sample data to compare against.
DECLARE @Data TABLE ( PropertyId INT, RequiredId INT );
INSERT INTO @Data ( PropertyId, RequiredId ) VALUES
    ( 1, 1 ), ( 1, 2 ), ( 1, 3 ), ( 2, 1 ), ( 2, 2 ), ( 2, 4 ), ( 2, 5 );

-- Set a property id value to query.
DECLARE @PropertyId INT = 1;

-- Preview @Data's rows for the specified @PropertyId.
SELECT * FROM @Data WHERE PropertyId = @PropertyId ORDER BY PropertyId, RequiredId;

现在，我已经创建了一个必需值列表（必需的\uid1到5）和一些虚拟数据来检查它们。此初始选择显示指定@propertyid的当前结果集：

+------------+------------+
| PropertyId | RequiredId |
+------------+------------+
|          1 |          1 |
|          1 |          2 |
|          1 |          3 |
+------------+------------+

可以看到当前属性缺少必需的\u id值4和5。接下来，我们可以将@required与@data和 INSERT 使用 EXCEPT 运算符，然后返回更正的结果集。

-- Insert any missing required values for @PropertyId.
INSERT INTO @Data ( PropertyId, RequiredId )
SELECT @PropertyId, required_id FROM @Required
EXCEPT
SELECT PropertyId, RequiredId FROM @Data WHERE PropertyId = @PropertyId;

-- Preview @Data's revised rows for @PropertyId.
SELECT * FROM @Data WHERE PropertyId = @PropertyId ORDER BY PropertyId, RequiredId;

更新后的结果集现在如下所示：

+------------+------------+
| PropertyId | RequiredId |
+------------+------------+
|          1 |          1 |
|          1 |          2 |
|          1 |          3 |
|          1 |          4 |
|          1 |          5 |
+------------+------------+

你可以用这个来对付我 @PropertyId = 2 去看一个不同的场景。
注意使用except的顺序。首先是必需的行，然后是except运算符，然后是要验证的当前行。这很重要。除了说show me@required中不在@data中的行之外——这允许在@data中插入任何缺少的required值。
我知道这个例子不能用23行的要求来表示现有的数据，但希望它能帮助您找到满足您需求的解决方案。你可以阅读更多关于 EXCEPT 我是接线员。
以下是完整的ssms示例：

-- Create a list of required values.
DECLARE @Required TABLE ( required_id INT, required_val VARCHAR(50) );
INSERT INTO @Required ( required_id, required_val ) VALUES
    ( 1, 'Required value 1.' ), ( 2, 'Required value 2.' ), ( 3, 'Required value 3.' ), ( 4, 'Required value 4.' ), ( 5, 'Required value 5.' );

-- Create some sample data to compare against.
DECLARE @Data TABLE ( PropertyId INT, RequiredId INT );
INSERT INTO @Data ( PropertyId, RequiredId ) VALUES
    ( 1, 1 ), ( 1, 2 ), ( 1, 3 ), ( 2, 1 ), ( 2, 2 ), ( 2, 4 ), ( 2, 5 );

-- Set a property id value to query.
DECLARE @PropertyId INT = 1;

-- Preview @Data's rows for the specified @PropertyId.
SELECT * FROM @Data WHERE PropertyId = @PropertyId ORDER BY PropertyId, RequiredId;

-- Insert any missing required values for @PropertyId.
INSERT INTO @Data ( PropertyId, RequiredId )
SELECT @PropertyId, required_id FROM @Required
EXCEPT
SELECT PropertyId, RequiredId FROM @Data WHERE PropertyId = @PropertyId;

-- Preview @Data's revised rows for @PropertyId.
SELECT * FROM @Data WHERE PropertyId = @PropertyId ORDER BY PropertyId, RequiredId;

赞(0）回复(0）举报 2021-07-24

我来回答

使用sql将缺少的值插入表

2条答案

相关问题

热门标签

最新问答