sql将查询合并为一个

bvn4nwqk 于 2021-07-26 发布在 Java

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

我有几个问题：

insert into table2 (age, name)
    select '0-19', 'abc'
    where not exists (select 1 from table2 where age is null  and name is null);

insert into table2 (age, name)
    select '20-29', 'zxy'
    where not exists (select 1 from table2 where age not in ( '0-19')  and name is null);

insert into table2 (age, name)
    select '30-39', 'egt'
    where not exists (select 1 from table2 where age not in ( '0-19', '20-29')  and name is null);

insert into table2 (age, name)
    select '40-49', 'aaa'
    where not exists (select 1 from table2 where age not in ( '0-19', '20-29', '30-39')  and name is null);

insert into table2 (age, name)
    select '50-59', 'rtg'
    where not exists (select 1 from table2 where age not in ( '0-19', '20-29', '30-39', '40-49')  and name is null);

insert into table2 (age, name)
    select '60+', 'ghg'
    where not exists (select 1 from table2 where age not in ( '0-19', '20-29', '30-39', '40-49', '50-59')  and name is null);

如果满足相关条件，我想插入数据。您可以看到它们是独立的查询。将这些查询重写为一个查询。谢谢您
我可以试着用任何人来测试，任何人都可以修改这些查询 nvarchar 或者 integer .

sql sql-server

来源：https://stackoverflow.com/questions/62415082/sql-combine-query-into-one

2条答案

按热度按时间

vd8tlhqk1#

不知道下面是否有用。

SELECT
    CASE WHEN Age IN ("00-19") 
     THEN ("0-19")
     ELSE ("NULL")
    END AS Age,
    CASE WHEN Age IN ("20-29") 
     THEN ("20-29")
     ELSE ("NULL")
    END AS Age,
    CASE WHEN Age IN ("30-39") 
     THEN ("30-39")
     ELSE ("NULL")
    END AS Age,
    CASE WHEN Age IN ("40-49") 
     THEN ("40-49")
     ELSE ("NULL")
    END AS Age,
    CASE WHEN Age IN ("50-59") 
     THEN ("50-59")
     ELSE ("NULL")
    END AS Age,
    CASE WHEN Age IN ("60-69") 
     THEN ("60-69")
     ELSE ("NULL")
    END AS Age
FROM table2

我不知道上面是否有用。会鼓励你解释一下你想做什么，这样每个人都能帮上忙。

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

rqcrx0a62#

更新的答案：创建一个表变量来存储您的范围，然后将临时变量连接到要插入缺失值的表中，例如：

-- your Table2
Declare @table2 TABLE (age varchar(10), name varchar(10))

-- a temp variable table to join in order to exclude the duplicates
Declare @data TABLE (age varchar(10), name varchar(10))

    -- add all the values you want to verify existence of
    INSERT INTO @data SELECT '0-19', 'abc'
    INSERT INTO @data SELECT '20-29', 'zxy'
    INSERT INTO @data SELECT '30-39', 'egt'
    INSERT INTO @data SELECT '40-49', 'aaa'
    INSERT INTO @data SELECT '50-59', 'rtg'
    INSERT INTO @data SELECT '60+', 'ghg'

    --  before the insert (all the missing values)
    SELECT d.age, d.[name] 
        FROM @data d 
            LEFT OUTER JOIN @table2 t ON d.age = t.age 
        WHERE t.age IS NULL

    -- do the insert
    INSERT INTO @Table2 
    SELECT d.age, d.[name] 
        FROM @data d 
            LEFT OUTER JOIN @table2 t ON d.age = t.age 
        WHERE t.age IS NULL

    -- this should now be empty
    SELECT d.age, d.[name] 
        FROM @data d 
            LEFT OUTER JOIN @table2 t ON d.age = t.age 
        WHERE t.age IS NULL

版本1：你能给我们看看表中存储的数据吗？对存储为字符串的年龄进行范围搜索可能会像您认为的那样工作。
例如：“0-19”
数据在表中存储为“5”还是“0-19”？如果它存储为“5”，那么正如其他人已经提到的，将该数据类型转换为数字数据类型（小int或int）是一个好主意。
下面是一个示例，您是否对存储为字符串（varchar）的值进行过排序，但这些值表示一个数字？例如，如果我们有这组值，我们希望排序返回为：

1,2,3,100,200,300

但是我们得到：

1,100,2,200,3,300

为了正确排序字符串，您必须向右填充到最长的长度。
在本例中为3个字符。

001 002, 003, 100, 200, 300.

在字符串中，对每个字符进行求值。
如果你看ascii表(https://www.ascii-code.com/)你知道为什么了。空在数字前面。

1 = 49 0 0
100 = 49 48 48
2 = 50 0 0
200 = 50 48 48

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

我来回答

sql将查询合并为一个

2条答案

相关问题

热门标签

最新问答