i have two tables. and i want following desired result.

我有两张桌子。我希望得到理想的结果。

Table 1

id | salary

01 | 500

04 | 300

07 | 700

Table 2

id | salary

02 | 300

05 | 800

06 | 700

I want output like

id | salary

01 | 500

02 | 300

03 | <em>null</em>

04 | 300

05 | 800

06 | 700

07 | 700

08 | <em>null</em>

09 | <em>null</em>

10 | <em>null</em>

3 个解决方案

#1

If you are using SQL Server, then Probably this solution should work for you

如果您使用的是SQL Server,那么此解决方案可能适合您

;WITH CTE
AS(
    SELECT
        ID = 1

    UNION ALL

    SELECT
        Id = Id+1
        FROM CTE
            WHERE Id <  10
),C2
AS
(
    SELECT
        ID,
        Salary
        FROM YourTable1

    UNION ALL

    SELECT
        ID,
        Salary
        FROM YourTable2
)
SELECT
    CTE.ID,
    C2.Salary
    FROM CTE
        LEFT JOIN C2
            ON CTE.ID = C2.ID

#2

Here is an alternative solution where I used a numbers table and created numbers with zero padding (better to use a SQL pad zeros function)

这是一个替代解决方案,我使用数字表并创建零填充数字(更好地使用SQL填充零点功能)

In fact your two tables where salary data is stored are same, and you only need a UNION or UNION ALL here Below select simulates a solution like a UNION case here but if there are two entries one on each table for a id number like '01' then only first table salary is used. Please consider cases like this on your case.

实际上你的工资数据存储的两个表是相同的,你只需要一个UNION或UNION ALL这里下面选择模拟一个类似UNION案例的解决方案,但是如果有两个条目,每个表上有一个像id这样的id号'然后只使用第一个表薪水。请在您的案例中考虑这样的案例。

SELECT
    RIGHT(REPLICATE('0',2) + Convert(varchar(2),Number),2) as id,
    coalesce(t1.salary,t2.salary) as salary
FROM master..spt_values num
LEFT JOIN [Table 1] as t1 on t1.id = RIGHT(REPLICATE('0',2) + Convert(varchar(2),num.Number),2)
LEFT JOIN [Table 2] as t2 on t2.id = RIGHT(REPLICATE('0',2) + Convert(varchar(2),num.Number),2)
WHERE 
    Type = 'P' and Number <= 10
ORDER BY Number

#3

For SQL Server...This should help..You can replace 20 with the max number you need to generate.

对于SQL Server ...这应该有帮助。您可以用您需要生成的最大数量替换20。

;WITH SingleDigits(Number) AS
(
    SELECT Number
    FROM (VALUES (1), (2), (3), (4), (5), (6), (7), (8),
    (9), (0)) AS X(Number)
)
,Series AS
(
    SELECT (d1.Number+1) + (10*d2.Number) + (100*d3.Number) + (1000*d4.Number) Number
    from
    SingleDigits as d1,
    SingleDigits as d2,
    SingleDigits as d3,
    SingleDigits as d4
)
,CTE1 AS ( SELECT id, salary FROM Table1 UNION ALL SELECT id, Salary from table2 )
SELECT s.Number Id, c.salary FROM Series s 
LEFT JOIN  CTE1 c ON s.Number = c.Id
WHERE Number < 20

#1